Creating Excel file from VB

[Mansoor Azam]

My VB 6 application creates and displays an excel file with a pivot table in
it that gets data from an access database when the following routine is run.
The report is displayed correctly in an Excel workbook (Book1) the first
time this routine is run. However if I click the button again ( after
closing excel ) excel is opened with a blank workbook named Book2. No pivot
table ! Similarly closing and opening again gives another blank Book3 and so
on. To get the pivot table I have to quit my application and run it again
every time I want a report displayed. Whats the problem?

any help appreciated.
thx


Private Sub cmdShowXLReport_Click()

Dim Excelapp As Excel.Application
Dim ExcelWS As Excel.Worksheet ' This is the sheet

Set Excelapp = CreateObject("Excel.Application")

Excelapp.Workbooks.Add

Set ExcelWS = Excelapp.ActiveSheet

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=c:\Barki\SAMSOL\samsol.mdb;DefaultDir=c:\Barki\SAMSOL;DriverId=
25;FIL=MS Access;MaxBufferSize=2048;P" _
), Array("ageTimeout=5;"))

.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT rTable." & fld & ", rTable.item, rTable.colour, rTable.qty"
& Chr(13) & "" & Chr(10) & "FROM `c:\Barki\SAMSOL\samsol`.rTable rTable" _
)

.CreatePivotTable TableDestination:=Range("A3"), TableName:= _
"PivotTable2"

End With

ActiveSheet.PivotTables("PivotTable2").SmallGrid = False

With ActiveSheet.PivotTables("PivotTable2")

.PivotFields(fld).Orientation = xlRowField
.PivotFields(fld).Position = 1

.PivotFields("colour").Orientation = xlColumnField
.PivotFields("colour").Position = 1


.PivotFields("item").Orientation = xlColumnField
.PivotFields("item").Position = 1

.PivotFields("qty").Orientation = xlDataField
.PivotFields("qty").Position = 1

End With

Application.CommandBars("PivotTable").Visible = False

ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel
Selection.NumberFormat = "0.00"

With ActiveSheet.PivotTables("PivotTable2")
.MergeLabels = True
.NullString = "0"
End With

Range("A1").Select

Excelapp.Visible = True

Set Excelapp = Nothing

End Sub

 

[Jonathan West]

There are two problems with your code

1. You are declaring Excelapp and assigning it to a new instance of Excel,
and then not using it for all your code that controls Excel. The lines of
code that start with ActiveSheet, ActiveWorkBook, Application, Selection and
Range should all be qualified by the Excelapp object if you want to keep
track of what you are doing and which instance of Excel you are sending
commands to.

2. You aren't closing your instance of Excel when you have finished with it,
before dropping the reference to it. You need to use the Quit method of
Excelapp to close Excel before you assign Excelapp to Nothing.

 

[Steve Gerrard]

In addition, you will want to Save the workbook with a file name. If you
want to open it again from VB, you will need to open the saved workbook.
The line
Excelapp.Workbooks.Add
explicitly asks Excel to create a new blank workbook each time.

 

[Mansoor Azam]

I dont want to save the file. My routine should open a new workbook and
create the pivot table in that workbook every time. The user can save the
file from excel if he wants to. The problem is the routine works correctly
the first time but does not create the pivot table on further attempts but
shows a blank workbook. I have to close my vb application and run it again
to display the pivot table every time. I've removed the reference to ExcelWs
as Jonathan said but its the same as before. I dont want to use the quit or
close method before
assigning Excelapp to Nothing because I dont want my app. to close the
workbook as
soon as it is displayed but want the user to close it from Excel.

any more ideas will be appreciated.

thx

 

[Steve Gerrard]

I dont want to save the file. My routine should open a new workbook and
create the pivot table in that workbook every time. The user can save the
file from excel if he wants to. The problem is the routine works correctly
the first time but does not create the pivot table on further attempts but
shows a blank workbook. I have to close my vb application and run it again
to display the pivot table every time. I've removed the reference to ExcelWs
as Jonathan said but its the same as before. I dont want to use the quit or
close method before
assigning Excelapp to Nothing because I dont want my app. to close the
workbook as
soon as it is displayed but want the user to close it from Excel.

any more ideas will be appreciated.

thx

Okay, now I see what you want.

I think when the user exits Excel using File/Exit, it disappears, but
remains in memory. This is because it was started through automation by
your program. You could check that by looking in Task Manager after
exiting Excel - it will probably still be listed there.

As a result, Excel is still running, and still has Book1 open. Your code
then adds a new workbook (Book2), and attempts to create the same pivot
table again. It is probably being recreated in Book1 again, or can't
create another with the same name.

In my experience, starting and manipulating Excel from VB works fine,
but allowing the user to then continue using Excel without quitting and
launching it the regular way does not.

You could try an odd approach: at the beginning of your code, just after
getting an Excel object, do the Excel.Quit, and then get another Excel
object. This would shut down Excel and restart it, allowing the rest of
your code to run the same each time. Might be worth a try.