Excel Query

[Radagast]

I hope this has not been asked before. Red flag if it has, however;

MS Query

I have been asked to move data from one area of our servers to another. Not a problem with the database. The problem arrises when I update the excel spreadsheet pointing to the old location (Multiple query's). How do I change the file location the query's point to in Excel. Or can I?

I can move/copy the spreadsheet to any location on our server and it will always pull the data from the same place. I need that location changed.

Mike

 

[MintJulep]

Using Exel's native tools I think you would need to manually rebuild the query.

Using VBA you have access to the SourceDataFile Property

Something along the lines of:

worksheets(index).QueryTables(index).SourceDataFile = new path

 

[Radagast]

I was quite sure that would be the case. I'll give VBA a shot first.

Thank you.