What I want to do is build a module in access that would open an excel file, refresh its links with my access db, save and close immediately afterwards. Basically, all I need is excel to load an excel workbook.
In my excel workbook, one of the sheet imports data from an access query, another sheet caculates some totals with the imported values. Then the sheet with the new calculated values is a linked table in access....
Therefore, when values in the access query change, Excel when refreshing should transfer the new values from access to excel, than back to access in the linked table,...
Here is the code I have, but there is still a problem, the excel file doesnt refresh its links with my db.
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
'set references to excel objects
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("X:\Martin\AFMQ\DB\afmq.xls", 3)
'save the workbook as a new name
'close and quit out of excel
'get rid of the object references
Set xlBook = Nothing
Set xlApp = Nothing