Can't Refresh Data or Edit Query of Excel File

[NMiller007]

I recently got a call to help someone with an Excel problem they were having and I can't figure it out. It is an old Excel file I believe is pulling data from an external database (ODBC) using MS Query. When I try to edit the query, it says "waiting for data to be returned by Microsoft Query", but then a get a SQL error. Our server has changed (probably multiple times) since this file was created, so I am assuming they have somehow hardcoded an old server name in there or something similar.

Is there any way to view this query without trying to make the connection? If I can view the old query, I should be able to recreate the worksheet without much difficulty.

Thanks!

 

[xlhelp]

You can right click the query and choose Edit with Notepad. You can change the server name in the Notepad and save it and try to run the query again.

Member- AAAA Association Against Acronym Abusers

 

[NMiller007]

Thanks for the quick reply.

How can I right-click on the query? I don't think there is an external file anywhere (I've made a copy of the offending Excel file to my system and I have the same problem). I am using the Import External Data menu to try and edit the query, but I can't find anything in there.

Thanks.

 

[xlhelp]

If it is running a query, it is pulling it from somewhere, right? Is there a macro that is running or calling a query?

Member- AAAA Association Against Acronym Abusers

 

[NMiller007]

It was a query that pulled data only when the data was refreshed via the external data menu.

I was able to find a solution to the problem. A colleague was able to extract the query from the worksheet (I missed the process). Then I recreated the sheet using the SQL statement and everything seemed to flow properly.

I think in the end there was some Network=DXGHG or something similar in the query definition that was causing the problem.

Thanks for your help; I'm going back to Access where it is safe.