Excel VBA Macro inadvertantly renaming Power Query queries

[pelowski]

I've been trying to troubleshoot this for awhile now but am coming up blank.  For the past couple of months a VBA subroutine I have has been renaming my M queries after the first run-through to QUERYNAME (2).  Subsequently the second time through the loop (with different parameters specified) the queries fail to refresh because they no longer have the same names.  The closest thing I could find to this online is this article from back in 2015. 

Here are some cavaets to what I'm doing...

1. All queries are refreshing in Background Mode of False because I need to refresh them in order.
2. I've tried them in "Load to Table" only, "Only Create Connection" and "Load to Data Model" and various variations thereof.  Some must be loaded to worksheets as I'm copying over the data into new workbooks as part of the output process.
3. It's not at all clear when stepping through the VBA code when the queries get renamed.  I've even tried some VBA watches on "Break when True" but they don't seem to fire in this regard.
4. As I said this was working several months ago but so many changes have happened since then (most of them good) that I don't know which change broke it.
5. In order to properly troubleshoot this and improve the performance, I've separated the former "Get and transform all the data" steps and the "Generate all the workbooks" steps into separate processes.
6. Despite #4 above, there are still there are still several (a dozen or so) queries in the "Generate all the workbooks" workbook some of which are interconnected and I think I've already paired it down to a bare minimum.  (The previous "Do all the stuff" workbook had some 40+ queries, many of which were custom M functions, in it.)

Any thoughts?

[pelowski]

This is for anyone that might care about this solution.  After a lot of debugging and refactoring of this code I think I'm on the right path.  I'm changing any time I copy a sheet that is based on query results from VBA code that looks like this...

ForReview.Copy After:=wbCreated.Sheets(2)

To looking something like this instead...

wbCreated.Sheets.Add.Name = "For Electronic Review"
Set rngToCopy = wbMain.Sheets("For Review").Range("A1").CurrentRegion
Set rngToPaste = wbCreated.Sheets("For Electronic Review").Range(rngToCopy.Address)
rngToCopy.Copy
rngToPaste.PasteSpecial Paste:=xlPasteValues
rngToPaste.PasteSpecial Paste:=xlPasteFormats
Call FormatForReview(wbCreated.Sheets("For Electronic Review"))

(There are several output worksheets in various output workbooks that have very specific formatting that I need to recreate in areas.)

The downsides of this approach are...

1. There are more steps to the copy and despite everything being already formatted in the source worksheet not all of it (like images that have been added) get carried over like before so sometimes I need additional formatting steps after the PasteValues/PasteFormats.

2. I have to recreate any table declarations and reformat them where necessary because the PasteFormats doesn't get me that either.

The upsides are...

1. My query names aren't getting clobbered during the workbook creation

2. I don't have to loop through the Destination workbook afterwards and remove queries and named ranges that inadvertently got copied over during the previous method of worksheet copy.

Overall, I still don't have it done, but I'm at least closer to solving this problem.

[pelowski]

I thought this was on the right path the solving the issue, but unfortunately I'm running into other issues now including queries not existing the next time I try to do a refresh.  I'm really loathing VBA and query refreshing right now.

[Anonymous]

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

[pelowski]

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.

[lbendlin]

this should help

excel - How to automate a power query in VBA? - Stack Overflow

Look for "ListObjects"  and "Connections"  in your VBA code.

[pelowski]

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.

[pelowski]

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.