Open Excel from Access

[Glenn Suggs]

I've been able to accomplish this before but now I'm having trouble figuring
out the problem. Can you help?

I have vba code in Access that updates a spreadsheet in Excel and then saves
it to a new name. After this has been done, I use the following code:
Workbooks(1).Close False, , False
xlsApp.Quit
Set xlsApp = Nothing

Then I go to a button on a form which should open the new spreadsheet with
the Shell command like this:
RetVal = Shell(strExcelPath & " """ & strFileName & """", 1)

I get the message that the code can't find the spreadsheet (whose name is
stored in strFileName). Thanks in advance for any help you might provide.
Glenn

 

[Glenn Suggs]

I forgot to mention that when I close Access and come back in, that the
spreadsheet opens up fine.
Thanks

 

[Ken Snell \(MVP\)]

It's a bit difficult with just this tiny snippet of code, but you're not
fully qualifying the Workbooks(1).Close method, so likely you're creating a
new instance of EXCEL that is not closed until you close ACCESS; and that
may mean that Windows doesn't "see" the file yet.

(I assume that you're actually saving the EXCEL file somewhere earlier in
the code? I ask because your "Close" step is not saving any changes.)

Your code step should be this:
xlsApp.Workbooks(1).Close False, , False

Likely, there are other steps in your code where you need full qualification
through the EXCEL application, too.

 

[Klatuu]

Ken is correct. Whenever you are using Automation, it is absolutely
necessary to be sure each reference to any excel object be fully qualified.
As to what happened to the file, depending on whether you opened and
existing workbook or created a new work book, you need to save it before you
close it:

With xlsApp
'For exsiting workbook
.Workbooks(1).Save
'For new workbook
.Workbooks(1).SaveAs "C:\MyDocuments\BeancounterNumbers.xls"

.Workbooks(1).Close False, , False
.Quit
End With

 

[Glenn Suggs]

Yes, it was a new workbook and I saved it previously. Also, I went back and
qualified the Excel objects and still didn't get the spreadsheet to show.

 

[RoyVidar]

Yes, it was a new workbook and I saved it previously. Also, I went back and
qualified the Excel objects and still didn't get the spreadsheet to show.

Without seeing your current code, how can we assist?

Just wondering, if you wish to view the workbook/sheet you've just
altered, why are you closing Excel and shelling? Why not just use a

xlsApp.Visible = True

and release the objects (without closing the workbook and quitting
Excel)?

 

[Glenn Suggs]

I want the users to have the option of just running the job or running the
job and then viewing the results.
Thanks for the input,

 

[Glenn Suggs]

Hello Ken,
I thought the idea about qualifying the Excel objects was working but it
still won't pull up the spreadsheet after closing the file. There were many
places to "qualify" and I THINK I got them all. At any rate at the end I did
a For Each... loop closing all workbooks in the workbook collection, then the
xlsApp.Quit but still no results. (I want the user to have the choice of
just running the job or running it and then viewing the results by click a
button. The Shell statement is behind the button.) I could post all the
code here but its kind of large so I was saving that as a last resort. Guess
I'm stumped.

 

[RoyVidar]

I want the users to have the option of just running the job or running the
job and then viewing the results.
Thanks for the input,

So - only close Excel if they don't want to view the result.

Closing and reopening Excel when you can just keep it open, keeps the
user waiting completely unnecessary.

 

[Ken Snell \(MVP\)]

You also need to qualify "embedded" references, which can be easy to
overlook (e.g., using .Cells(x,y) as an argument of another method or
propety -- you also need to qualify here, too). But, without seeing all the
code and verifying that there are no other references, then I think Roy's
idea of just making EXCEL visible is probably the easiest approach.

 

[Keith Wilby]

I want the users to have the option of just running the job or running the
job and then viewing the results.
Thanks for the input,

This code fragment may help:

'Give the user the option to view the workbook
If MsgBox("Data exported. Do you want to view the spreadsheet?", vbYesNo,
"Data exported") = vbNo Then
objXL.Quit
Else
objSht.Activate
objXL.Visible = True
End If

Regards,
Keith.
www.keithwilby.com