Moving a ".dqy" query

[YellowBelly]

Hello,

I am in a situation involving getting external data from an ODBC connection.

I have a workbook on a networked drive that imports data via a ".dqy" query file which is located on another networked drive. People have been using this sytem to build job reports for the last 6 months or so and have saved each job report as a seperate sheet in the same workbook.
Each time the workbook is opened, they choose "enable automatic refresh" to get updated data for each on-going job and just print the sheets off for analysis.

I have two questions:

1.) Is it possible to move the query file to the same directory as the workbook and somehow "re-link" to it without affecting the data?

2.) When you "save" the query defintion with the workbook via the data range properties dialogue, does it still link to the ".dqy" file or is the connection data magically embedded into the worksheet?


Thanks in advance for any help.

 

[Juan Pablo González]

If I understand this correctly, the dqy files are only used the first time the query is created. After that, Excel stores in the QueryTable object, the information on the connection and the query itself. So, if you move or do whatever with the dqy file after the queries have been created, Excel shouldn't notice.

 

[YellowBelly]

Thank-you for the quick response Juan.

I used the following VBA code and it gave me the path to my ".dqy" file on my networked drive:

For Each qy In Sheet1.QueryTables
MsgBox qy.SourceConnectionFile
Next

If I delete the ".dqy" file will this not affect my workbook?

Thanks again.

 

[Juan Pablo González]

Ah, I don't have that property in Excel 2000, but I do see it in 2003. But I tried this:

1. Create a file, and run a query with a dqy file.
2. Save and close the file.
3. Rename the dqy file
4. Open the file, refresh the query, and everything seemed to work fine.

So, I'd think you would be safe.

 

[YellowBelly]

Thank-you Juan,

I agree with you that Excel must embed the query into the sheet (via Query Table object) . So the query remains with the sheet no matter what you do to the original.

It is very odd that I cannot edit this query with MS Query though. Every time I click "edit query" MS query flahes in the bottom status bar and then dissapears. That is a pain.

Thanks again for all of your help!