Open Excel File from Access and...

[TimT]

Hey friends!!
I got a problem here that's got me stumped.. again.
I have an Excel file with pivot tables that are linked to the database that
I'm trying to open the file from and I'm having the following problems:
1. It opens the file in a ready only way (and it's not set to be that way)
2. When I try to refresh the tables sometimes it tells me that the database
has been placed in a state by user 'Admin' on machine 'MYNAME' that prevents
it from being opened or locked.
Please help!!

Below is the code I'm currently using:

Function AutomateExcelProvision()
Dim XL As Object
Dim SourceFile As String
Set XL = CreateObject("Excel.Application")

SourceFile = "C:\Excel\Reports\Provision.xls" '

XL.Workbooks.Open SourceFile

XL.Visible = True
Set XL = Nothing

End Function

 

[Klatuu]

There are a multitude of problems in this code. For one thing, you never
Quit the XL object. I would bet that after this code has executed, if you
open Task Manager and look in the Processes tab, you will find an instances
of Excel.exe still in memory.

You have to be very precise when you are using Automation using an Excel
object in Access. All object references have to be explicitly identified.
When you create a new object reference, it is necessary to prefix it with the
object is belongs to. If you do not do this correctly, Access may not know
what instance of the Application object it belongs to, so it will create one
on its own. Even after you issue a Quit statement, Excel can be left in
memory because you Quit the instance you created, but the one Access created
is still in memory.

Another thing to consider. You are using the CreateObject without first
trying to use the GetObject. Omitting the GetObject will ususally work
provided the user does not have an Excel file open or doesn't open one while
your code is running. In addition the the problems noted above, you now run
the risk of closing a users spreadsheet she had open while she ran your code.

Since your code doesn't appear to do anything, am I to surmise it is
intended only to open the spreadsheet for the user's convenience? If that is
the case, I would recommend you use the Shell command rather than what you
are doing.

 

[TimT]

Klatuu,
Thanks for the info.
Yes this is basically for the user's convienience so that they can just
click a button and the reports will open up and refresh automatically.
It sounds like using the shell command would work.
Could you show me what the code would look like for this?

 

[Klatuu]

Call Shell("excel " & Chr$(34) & varGetFileName & Chr$(34), vbMaximizedFocus)

varGetFileName is the full path and file name of the spreadsheet.