Open Excel and have changes from query show

[PaulBricker]

A link(?) was set up between an Excel spreadsheet and an Access query by creating the spreadsheet using the Excel "Get External Data/New Database Query" wizard. The option to update information was selected and seems to work fine when opening the Excel file from the menu bar but doesn't seem to update the data when opening it from an Access VBA module. I've tried the Shell command and tried using the Excel Object and I have no problem opening the file but it doesn't show the updated data. Anyone have any thoughts on this.

Paul

 

[PaulBricker]

I worked on this Friday and couldn't come up with a solution. Today I try it using the Shell command and it seems to work fine. If anyone has suggestions, I'm happy to hear them, otherwise it seems to be working.

Paul

 

[xlbo]

This'll be to do with HOW the workbook is opened. MSQuery, refresh data on open uses the same switch as the workbook_open event. Neither of these happen when a workbook is opened using CODE. Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[combo]

If you use OLE automation to open workbook, you can use Worksheets(1).QueryTables(1).Refresh with proper reference to worksheet (name or index) and querytables collection item to refresh data.

combo

 

[PaulBricker]

Thanks for the info. I'll write the refresh into the code and see how it goes.

Paul