Worksheets not visible in Excel XP

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

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


<[email protected]> wrote in message news:[email protected]...
> 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
>

Correction...

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

Jim Cone



"Jim Cone" <[email protected]> wrote in message
news:O9%[email protected]...
> 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

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

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

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

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

<[email protected]> wrote in message news:[email protected]...
> 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,

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