Error opening spreadsheet through VB

[mlopes1]

Using the code from a previous post I was able to open an excel spreadsheet through VB on a control button.

However, when the spreadsheet opens, it's external data ranges (which use Microsoft Query to get data from the DB) do not refresh. Their properties are set to refresh on file open and they do refresh properly IF the spreadsheet is opened through windows explorer or Excel itself. Why won't they refresh when opened using Access?

Dim XL As Object
updateTable
Set XL = CreateObject("Excel.Application")
tempAppPath = "K:\Export\CustDocs\COA\Formatted\" & Me.Formula & ".xls"
With XL.Application
.Visible = True
.workbooks.Open tempAppPath
End With

Set XL = Nothing

Any ideas??

 

[The_Doc_Man]

The first place I would look is in EXCEL help, topic such as Automation, subtopic perhaps the Open Method.

You might have to look in the section on VBA under EXCEL instead. I don't recall offhand where to find it.

The idea is that in EXCEL's help, find the .Open method that is used to open a workbook. It should list optional parameters, one of which might control whether you do that update or not.

 

[mlopes1]

Doc Man,

Thanks for the reply. The reason I posted here was that, in the past, I have used the Call(Shell, ...) method of VBA to succesfully open the same worksheets.

Recently, some of our users have upgraded to XP while others are still on 97. Using the Shell method requires stating the path from which to launch i.e. (Office97/Office/Excel.exe)

Now that users have varying excel.exe paths I decided to just launch the file, and let windows open it in whatever the appropriate application is.

And in fact the files do open without errors no matter whose system is used. That much I got to work!

However, the updating in the worksheets stopped happening automatically. Since it worked fine before and no changes were made to the worksheets themselves I was thinking maybe someone knew of what Access might be doing that blocks the UpdateOnFileOpen from taking place.

Is it just an inherent difference between Call(Shell) and xl.application ? Or is there maybe something I can add to allow the worksheets to update their data from Access?

Again, the worksheets themselves are identical to when it was working fine with Call(Shell).

I hope this is a little more clear and thanks again for your post.

Marco