Open and update Excel workbook from Access

[bb29829]

This is the code I am using. It opens the excel file just fine but the prompt to update still shows up and I need the code to update the file as well. Thanks in advance!

Bryan

Dim xl As Object
Dim xlWrkBk As Object
Dim xlSht As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True 'Control whether or not Excel should be visible to
'the user or not.
Set xlWrkBk = xl.Workbooks.Open("\\wg07\COMP\Comp Share Folder\DASHBOARDS\Offers_Dashboard.xlsx")
xl.Workbooks.Refresh

 

[ashleedawg]

Can you share some more details? "Update" is a pretty vague term around here. You're trying to update what from what?

Are you trying to get data from an Access table into an Excel worksheet? Is this something that needs to run automatically, or one time, or have a constant link?

 

[Cronk]

I don't think Refresh is a method of the workbooks collection but rather the workbook object.

Try
xlWrkBk.refreshAll

or you may need to activate it first with
xlWrkBk.activate

 

[bb29829]

When the excel file opens, it automatically pops up with a "do you want to refresh" prompt. I would like the code to go ahead and say yes to that prompt when opening.

 

[Cronk]

When the excel file opens, it automatically pops up with a "do you want to refresh" prompt.

That will happen as the workbook opens, before any further Access code runs. I think you'll need to disable the prompt in Excel, maybe the Open event in Excel.

 

[ashleedawg]

Which version of Access & Excel are you using? (The wording of some errors varies by version.)

When opening an Excel 2016 workbook that has cells linked to other data sources, the user is warned:

This workbook contains links to one or more external sources that could be unsafe.​

If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.​

​

If this is the prompt you're referring to, you can't bypass that from Access (or else malicious software would be able to bypass it too), although you might be able to get sneaky using SendKeys -- but that has its own issues.

Or, you can remove that warning in Options -> Advanced -> General -> Ask to Update Automatic Links . If you un-check that option, the linked data will be updated automatically every time you open the workbook, without prompting the user.​