Forum Discussion
Excel VBA Macro inadvertantly renaming Power Query queries
Hi pelowski ,
If such error occurs in Excel or Power Query?
According to the error message, it is caused by renaming queries, so that we could not access to the data connection any more.You may click on the "Debug" button to see more details.
Best Regards,
Eyelyn Qin
What? I've been debugging for days. Of course I've clicked "Debug" in the first prompt shown and the indication is that the query that I want to refresh in the VBA subroutine has been renamed and is therefore not available via the name in which I'm calling it.
The PQ queries are getting renamed somehow in the VBA execution and I'm asking if anyone else has experienced this because figuring out where they are getting renamed in the VBA has not been successful thus far even with stepping through the code and using Debug.Asserts (https://youtu.be/CT7XkPXKVFw?t=368) everywhere to try to figure it out.
this should help
excel - How to automate a power query in VBA? - Stack Overflow
Look for "ListObjects" and "Connections" in your VBA code.
Thanks. I've reviewed that article several times. I reference Gil Raviv's book regularly.
I have over a hundred references to ListObjects (I'm creating a bunch of formatted tables in the output workbooks and need to format them accordingly) and many references to Connections as well. This was all working at one point very smoothly and when it did it looked like magic, but in the last year and a half the "query renaming" issue became a thing and I still don't know in the code where it's happening.
This is the main spot where I refresh my queries in order. These are all based upon larger parent queries that don't need to be refreshed after they've got the data for each workbook I want to create.
https://www.screencast.com/t/wrUgaXAi6ZqN
But this part all works fine... I can refresh the queries repeatedly with no problems. It's somewhere in the generation of the multiple worksheets that a query is getting clobbered by a rename operation.
After adding a ton of Debug.Print statements throughout the code I'm getting closer to understanding where this is happening although Excel still can't recognize that the query no longer exists before it tries to refresh it.
