Opening an Excel File from Acess and Updating the Links in the Excel File

[ojasg]

I am having a very peculiar problem.
I am using the following code to open an Excel File from Access -->

// Code Starts //
DoCmd.OutputTo acOutputQuery, "qryOutputIncommingVsMeasuredToExcel", "*.xlsx", "Q:\Some Path\MeasuredVsIncommingRawData.xlsx"

Dim AppExcel As Object

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Workbooks.Open "Q:\Some Path\MeasuredVsIncomingAnalysis.xlsx"

AppExcel.Visible = True

AppExcel.ActiveWorkbook.Refreshall
\\Code Ends\
The file that opens (Analysis) has some links to the file that is outputted by the query(Raw); and is getting it's data from the query output.
The files opens, but none of the data is updated. If I close the file and open it again from it's location, it prompts me to update the external links and everything is updated correctly.

How do I get the file to show the refreshed data when it opened from Acess?

Thank You!

 

[lameid]

On the surface the Excel portion looks like it should work to me... I mean that refreshall is the method I get when I go to data and hit refresh all...

I'd suggest you ask in forum707

That is a generic VBA forum where Excel experts are more likely to notice.... you might try searching that forum too.

 

[ojasg]

Thanks..
Will try that forum.. If I find the solution.. Will post it here.

 

[ojasg]

Ok.. So after playing around for a while, I think I have a found a solution.
Everything with code is ok. What needs to be altered is the Excel file settings.

Open your Excel File.
Click on "Data" in the Ribbon
Click on "Edit Links"
Ensure Update is Automatic
Click on "Startup"
Ensure the "Don't display alert and update links" option is choosen.
Click OK and SAVE!

This did it for me

Hope it helps someone else too

 

[lameid]

I still would think that the code would refesh, unless, there is some other way to update manually if that option is not set...