How to Auto Refresh an Excel linked File (1 Viewer)

[Number11]

I have a linked excel file that i need to auto refresh daily is this possible?

 

[Minty]

If data changes in the sheet and it is linked the changed data will show up in access?
I'm not sure I understand your question.

Have you tried it?

 

[Number11]

If data changes in the sheet and it is linked the changed data will show up in access?
I'm not sure I understand your question.

Have you tried it?

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

 

[Minty]

Are you saying there is external data in the spreadsheet that needs to be refreshed?
Where does that come from?

If Excel can get it Access can possibly get it directly and "miss out the middle man"?

 

[Number11]

Are you saying there is external data in the spreadsheet that needs to be refreshed?
Where does that come from?

If Excel can get it Access can possibly get it directly and "miss out the middle man"?

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?

 

[Minty]

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;

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.

 

[Number11]

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;

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 although this does work, its not allowing time for the background query to run and update