Sorry, so i have an excel spreadsheet that is linked to my database, but i need to have the database open/refresh the excel spreadsheet , as currently i have to open the excel spreadsheet so it refreshes and the save and close then have to go to table manager and refresh the linked excel all manually
Ok, no thats not possible and the excel is pulling data from many other sources, and it works fine all i need to do is add a button that opens the excel (so the auto refresh can haven) save the file so new changes remain and then refresh the link in access?
So what access sees as the excel table, the fields change each time ? That is what requires the link to be refreshed as well?
Code to open a spreadsheet;
Code:
Dim xlApp as Object
Dim xlWB as Object
Dim strFile as String
strFile = "C:\YourFilePath\YourFile.xlsx"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(strFile)
'Then depending on what you want to refresh
'recalculate all open workbooks
xlApp.Calculate
'recalculate a specific worksheet
xlWB.Worksheets(1).Calculate
' recalculate a specific range
xlWB.Worksheets(1).Columns(1).Calculate
xlWB.Save
xlWB.Close
SET xlApp = Nothing
If the fields don't change then you shouldn't need to refresh the Excel link.
So what access sees as the excel table, the fields change each time ? That is what requires the link to be refreshed as well?
Code to open a spreadsheet;
Code:
Dim xlApp as Object
Dim xlWB as Object
Dim strFile as String
strFile = "C:\YourFilePath\YourFile.xlsx"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(strFile)
'Then depending on what you want to refresh
'recalculate all open workbooks
xlApp.Calculate
'recalculate a specific worksheet
xlWB.Worksheets(1).Calculate
' recalculate a specific range
xlWB.Worksheets(1).Columns(1).Calculate
xlWB.Save
xlWB.Close
SET xlApp = Nothing
If the fields don't change then you shouldn't need to refresh the Excel link.