Worksheets not visible in Excel XP

[jwiles]

I wrote the following code in Access to create an Excel spreadsheet
output. In Excel 2000 it works fine. When I put it on Excel XP, the
spreadsheet is not visible and I can't seem to find a way to make it
visible. What am I missing?

' Setup Excel Spreadsheet
Dim xlApp As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Const XL_NOTRUNNING As Long = 429
Set xlApp = GetObject(, "excel.application")
Excel.Application.SheetsInNewWorkbook = 1
Set wkb = Workbooks.Add
Set wks = Worksheets(1)

... bunch of cell writes ...

' Save the spreadsheet
sFileName = Me!tFileName
wkb.SaveAs sFileName

Exit_WriteToExcel:
wkb.Close
xlApp.Quit
Set wks = Nothing
Set wkb = Nothing
Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing
Exit Sub

Thanks,
John

 

[Jim Cone]

John,

First of all if there is no existing Excel application running then
your code will fail at the GetObject line.
One way to get around that is to create a new instance of the App...
Set xlApp = New.Excel.Application
The new app will not be visible, but if you need to see it then..
xlApp.Visible = True

However, to answer you questions try...

Set wkb = xlApp.Workbooks.Add
Set wks = wkb.Worksheets(1)

I don't believe you need to specify the number of sheets in the
new app, as Excel will not create a workbook without at least one
sheet in it.

Also, I would set the worksheet and workbook objects to nothing
before quitting the application.

Regards,
Jim Cone
San Francisco, USA

 

[Jim Cone]

Correction...

Set xlApp = New.Excel.Application should be
Set xlApp = New Excel.Application ' (no dot)

Jim Cone

 

[jwiles]

Thanks for the reply. I did not copy my error routine that starts
Excel if it is not running, but thanks for info.

If I understand you, the issue may be related to the fact that I
reference the worksheet without context to the workbook? It's really
weird when I open the spreadsheet, the heading is there, just not the
grid. If you use the arrow keys, the data values show up in the
formula bar.

I need to connect to the XP installation to try your fix. I'll let you
know.

John

 

[jwiles]

Well, now I really don't get it. It seems that if you open Excel
spreadsheet while the Access application is still running, the
workbook/worksheet is invisible (you see the desktop between the
formula line and the status bar). If you close the Access application
and then open the Excel spreadsheet, it displays just fine. This only
happens in Office XP, not in 2003 or 2000.

I think your syntax (set wks = wkb.worksheets(1)) was better, so I did
make that change, but it had no effect on the above condition.

John

 

[jwiles]

Well, now I really don't get it. It seems that if you open Excel
spreadsheet while the Access application is still running, the
workbook/worksheet is invisible (you see the desktop between the
formula line and the status bar). If you close the Access application
and then open the Excel spreadsheet, it displays just fine. This only
happens in Office XP, not in 2003 or 2000.

I think your syntax (set wks = wkb.worksheets(1)) was better, so I did
make that change, but it had no effect on the above condition.

John

 

[Jim Cone]

John,

Just got back from having 3 teeth extracted.
I don't recommend it.

Did you also use...
Set wkb = xlApp.Workbooks.Add '?

Regards,
Jim Cone
San Francisco, USA

 

[jwiles]

Jim,

I didn't put the xlApp. in front of the workbooks.add. When I can get
back to that client, I'll give it a try and see if that solves the
issue.

Thanks,
John