Updating spreadsheet links from VBA
 

I'm not sure if this is a Excel question or an Access question.

I have a spreadsheet (call it "A") that links to data from several
different spreadsheets. I have an Access database links to worksheets in
"A", and some queries run from VBA that use linked tables.

So when I run the VBA, it runs queries that refer to the linked tables,
which in turn refers to the spreadsheet "A" which has links to other
spreadsheets.

The problem is getting the spreadsheet "A" to update its links to the other
spreadsheets. Currently, I manually open the spreadsheet, it prompts me if
I want to update the links, I answer "Yes", the links get updated, and then
I close "A" and run the macro.

What I would like to do is from Access VBA, open "A", cause all the links
to be updated and then close the spreadsheet. How do I do that?

amorphous

Updating spreadsheet links from VBA
 

Sub OPenExcel()

Set obj = GetObject("C:\temp\book1.xls")
obj.Application.Visible = True

obj.Close
End Su

"amorphous999" wrote:

I'm not sure if this is a Excel question or an Access question.

I have a spreadsheet (call it "A") that links to data from several
different spreadsheets. I have an Access database links to worksheets in
"A", and some queries run from VBA that use linked tables.

So when I run the VBA, it runs queries that refer to the linked tables,
which in turn refers to the spreadsheet "A" which has links to other
spreadsheets.

The problem is getting the spreadsheet "A" to update its links to the other
spreadsheets. Currently, I manually open the spreadsheet, it prompts me if
I want to update the links, I answer "Yes", the links get updated, and then
I close "A" and run the macro.

What I would like to do is from Access VBA, open "A", cause all the links
to be updated and then close the spreadsheet. How do I do that?

amorphous