List/Identify all queryTable numbers and their table names in a workbook (After the tables have already been renamed)

[gavcol]

Hi,
I have a workbook with 66 tables and recently some table corruptions began to occur in the file (on four separate occasions a different table was affected).
The excel error report has "Repaired Records: External data range from /xl/queryTables/queryTable17.bin part (External data range)".
The xml report didn't have any additional info.
I had already renamed all of my tables to Tbl_AMoreDescriptiveTableName and have no idea which one is Table17.
Some of the tables are refreshed by powerquery from csv files in the same folder.
Because I've renamed the tables, the original table numbers are nowhere to be found (not that I can see in name manager etc.)

I have backups of the file but want to identify which tables are affected to try to identify causes.
I was given some VB code (excerpt below) but it only lists the table name and sheet it's located but not the original table number that the MS error message refers to.

variable tbl As ListObject

.Cells(lRow, "A") = tbl.Name
.Cells(lRow, "B") = tbl.Parent.Name (for the sheet it's located in)

Is there a way to determine the same querytable number as referenced by Excel's error handling ?

Thanks in advance.
Gav

 

[Alex Blakenburg]

I know this is not going to give you the original table name, and if its the query that is failing may be starting at the wrong end but I thought I would put this out there anyway just in case.
It shows the Power Query "query name" in the connection string text. Its called Location.
Code needs the sheet "Summary of Tables" to exist

Sub LoopThroughAllTables()

Dim tbl As ListObject
Dim sht As Worksheet
Dim shtSummary As Worksheet
Dim cnt As Long
Dim outRowStart As Long

cnt = 0
outRowStart = 7
Set shtSummary = ThisWorkbook.Worksheets("Summary of Tables")

'Loop through each sheet and table in the workbook
  For Each sht In ThisWorkbook.Worksheets
    For Each tbl In sht.ListObjects
 
        'List table details
        cnt = cnt + 1
        shtSummary.Cells(outRowStart + cnt, 1) = cnt
        shtSummary.Cells(outRowStart + cnt, 2) = tbl.Name
        shtSummary.Cells(outRowStart + cnt, 3) = tbl.Parent.Name
 
        On Error Resume Next
        shtSummary.Cells(outRowStart + cnt, 4) = tbl.QueryTable.Connection
        On Error GoTo 0

    Next tbl
  Next sht

End Sub

Sample of Output - Headings added manually - "Summary of Tables""

 

[Alex Blakenburg]

I know this is not going to give you the original table name, and if its the query that is failing may be starting at the wrong end but I thought I would put this out there anyway just in case.
It shows the Power Query "query name" in the connection string text. Its called Location.
Code needs the sheet "Summary of Tables" to exist

Sub LoopThroughAllTables()

Dim tbl As ListObject
Dim sht As Worksheet
Dim shtSummary As Worksheet
Dim cnt As Long
Dim outRowStart As Long

cnt = 0
outRowStart = 7
Set shtSummary = ThisWorkbook.Worksheets("Summary of Tables")

'Loop through each sheet and table in the workbook
  For Each sht In ThisWorkbook.Worksheets
    For Each tbl In sht.ListObjects

        'List table details
        cnt = cnt + 1
        shtSummary.Cells(outRowStart + cnt, 1) = cnt
        shtSummary.Cells(outRowStart + cnt, 2) = tbl.Name
        shtSummary.Cells(outRowStart + cnt, 3) = tbl.Parent.Name

        On Error Resume Next
        shtSummary.Cells(outRowStart + cnt, 4) = tbl.QueryTable.Connection
        On Error GoTo 0

    Next tbl
  Next sht

End Sub

Sample of Output - Headings added manually - "Summary of Tables""

View attachment 33985

Technically the location is probably the original table name. Initially the table name and power query name are the same. In item 7 I have changed the table name adding xx.

 

[gavcol]

Technically the location is probably the original table name. Initially the table name and power query name are the same. In item 7 I have changed the table name adding xx.

Hi Alex,

Thanks so much for the code above. It didn't work at first. I was getting a subscript out of range error on the set sheet but I figured out it was because I had put the code into a module in the personal.xlsb VBAProject. Once I put it in a module/project for the file I wanted a list of tables from then it ran without error.

I should also have added in the OP that I'm using Office 365 64bit Version 2008 (Build 13127.21216)

Unfortunately, as you suspected, it didn't give me the queryTable.bin table number.
The location seems to be the PowerQuery query name used to import the data from the CSV and no location info is provided for manually created tables.
The last line, on row 72, is a table I created where I haven't yet renamed the table.

Just for more info,
This is one of the table errors I received:

 

[Alex Blakenburg]

Hi Alex,

Thanks so much for the code above. It didn't work at first. I was getting a subscript out of range error on the set sheet but I figured out it was because I had put the code into a module in the personal.xlsb VBAProject. Once I put it in a module/project for the file I wanted a list of tables from then it ran without error.

I should also have added in the OP that I'm using Office 365 64bit Version 2008 (Build 13127.21216)

Unfortunately, as you suspected, it didn't give me the queryTable.bin table number.
The location seems to be the PowerQuery query name used to import the data from the CSV and no location info is provided for manually created tables.
The last line, on row 72, is a table I created where I haven't yet renamed the table.

View attachment 34032

Just for more info,
This is one of the table errors I received:

View attachment 34034

Thank you for sharing.

I googled your error message (used OneNote to convert to text) and got to this link:-
Excel when connected to external data: "We found a problеm with some content in [file]"

With 66 Tables, I am not sure that some of the options will be viable.
One of the options is below and since the last line indicates you can turn refresh back on again, that might be worth a try.
(if it doesn't work maybe even looking on the connections file may be worth doing - I used Notepad but it didn't wrap so I copied it to Word to view it)

(posted by Jon on the above page)
"As an update on the last answer so that you retain your connections:

1. Open file with 7-zip (no unpacking!)
2. Open folder 'xl'
3. Edit connections.xml
4. Change refreshOnLoad="1" to refreshOnLoad="0"
5. Close and save, update in the archive.
6. Close 7-zip

The file should now open without errors. You can turn on the refresh on load and seems to work fine."

 

[gavcol]

Thank you for sharing.

I googled your error message (used OneNote to convert to text) and got to this link:-
Excel when connected to external data: "We found a problеm with some content in [file]"

With 66 Tables, I am not sure that some of the options will be viable.
One of the options is below and since the last line indicates you can turn refresh back on again, that might be worth a try.
(if it doesn't work maybe even looking on the connections file may be worth doing - I used Notepad but it didn't wrap so I copied it to Word to view it)

(posted by Jon on the above page)
"As an update on the last answer so that you retain your connections:

1. Open file with 7-zip (no unpacking!)
2. Open folder 'xl'
3. Edit connections.xml
4. Change refreshOnLoad="1" to refreshOnLoad="0"
5. Close and save, update in the archive.
6. Close 7-zip

The file should now open without errors. You can turn on the refresh on load and seems to work fine."

Thanks Alex,
I very much appreciate the time you've put into this.

I had already come across that page but it doesn't relate to my issue. I'm able to open the file without any problems and the MS Excel repairs seemed to have been successful. I haven't had any more occurrences (sorry, I should have put that in the original post) but I just want to verify which tables were affected and see if I can identify the repairs performed or if there's any discrepancies.

It would be too time consuming to check every table. Some sheets have columns with formulas up to the GA-GV ranges and the mapping tables sheet has 24 tables in it with tentacles touching them from the formulas on all the other tables. Checking every formula and link to every mapping table would put me in something not too far from a causality loop ?
That's why I'm trying to identify the few affected tables by their queryTable number that had resulted in that error message.

If only there was a way to determine which queryTable # was related to each data table.

Thanks again,
Gav

 

[gavcol]

Even looking at the Rels xmls, they're not very informative of the relationships between query and data tables by name
i.e. apparently queryTable17.bin has a relationship with table26 but I've no idea which table is table 26

 

[Alex Blakenburg]

Even looking at the Rels xmls, they're not very informative of the relationships between query and data tables by name
i.e. apparently queryTable17.bin has a relationship with table26 but I've no idea which table is table 26

View attachment 34054

Given you have what's most likely the original table number.
Use 7-Zip to unzip the files xlsx / xlsm file
Go the folder \xl\tables
This seems to have the original Table numbers

Open Table26.xml using the Browser (I used Chrome)
You will see something like the below:-

(My Sample Table is Table7 which in Excel is called xxhours_summary)

 

[gavcol]

Given you have what's most likely the original table number.
Use 7-Zip to unzip the files xlsx / xlsm file
Go the folder \xl\tables
This seems to have the original Table numbers

Open Table26.xml using the Browser (I used Chrome)
You will see something like the below:-

(My Sample Table is Table7 which in Excel is called xxhours_summary)

View attachment 34091

@Alex Blakenburg , (sry for the delay responding, work got in the way )
Thank you that's very helpful.
I'm using xlsb files and you'll see in my previous screengrabs that the files in \xl\tables are stored as tablexx.bin files which were not readable in Notepad++ so I didn't notice that xml detail before.
Once I saved the file as xlsm I was able to view the xml in notepad++

This would still be a fairly laborious exercise for 80+ sheets.
Is there a way via VB or otherwise from within excel to interrogate these table files in xl\tables to list the content name or displayname by each filename ?

Cheers and thanks again,
Gav

 

[Alex Blakenburg]

This would still be a fairly laborious exercise for 80+ sheets.
Is there a way via VB or otherwise from within excel to interrogate these table files in xl\tables to list the content name or displayname by each filename ?

Cheers and thanks again,
Gav

I don't know of a VB way of doing it but how do you feel about using Power Query to do it ?

I used

• Data > From File > From Folder
• then went to the unzipped folder \xl\tables
• Transpose
• filtered out rels
• in the binary field clicked on the expand / merge icon
• then looked for what you see below