Linked data in a Master workbook is not updating when one of its source files are open.
I have a Master workbook that has links to about two-dozen source workbooks.
In my work place, we have Data Analyst/Data Integration people who constantly import Excel files into SQL for queries.
The spreadsheet has some Macros in it which auto-update parts of the spreadsheet internally.There are several methods of doing this and one of them is to use a linked server which we will cover in this tip.. From Management Studio-- Name the Linked Server "QUERYEXCEL" as shown below. In the data source field, enter the full path of the My file you just created. To do a quick test drive, I have attached My file and also and which you can download here.To make it little interesting, has two sheets, with sales header data in Sheet1 and sales detail in sheet2.I tried to automate updating all fields in a document from Word with VBA, but that has the same problem, it also brings up the Macros dialog constantly for half a minute. Here's my code for that: I also tried to update the Word documents directly from the spreadsheet, but that does not work either, because when Excel tries to open a Word document via VBA the program stops executing and trows this error: "Excel is waiting for another application to complete an OLE action." Clicking ok and waiting does not help because the error message reappears after a few seconds, and the only way to stop it is to manually kill the Excel process.
Then again, I have very little experience with VBA, so I might be completely missing the point and there is a super simple solution I am just not aware of. I think I see the error, which is a silent failure, becuase the document contains links, there is an open dialog waiting for you to say "yes" or "no" to update the links. Update Links At Open 'capture the original value Word Application. Update Links At Open = False 'temporarily disable Set Word Doc = Word Application.