Change connection of query table

[jaryszek]

Hi Guys,

i am using this code to create listobject with oledb connection from external web source.
It is working nice.

Sub test()

QName = "HanaTable"

MFormula = ActiveSheet.[E1].Value
ThisWorkbook.Queries.Add Name:="PQ" & QName, Formula:=MFormula

          With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & "PQ" & QName & ";Extended Properties=""""" _
                , Destination:=Range("$A$1")).QueryTable
                .CommandType = xlCmdSql
                .CommandText = Array("SELECT * FROM " & "PQ" & QName)
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = QName
                .Refresh BackgroundQuery:=False
            End With
          
              With ActiveSheet.ListObjects(1).QueryTable
'                .WorkbookConnection.Name = "PQ" & QName
                .WorkbookConnection.RefreshWithRefreshAll = False
                .WorkbookConnection.OLEDBConnection.BackgroundQuery = False
            End With
          
          
End Sub

This is result.

The issue is that i had to change all the names from camel cases to upper cases for power queries names, so in this case it is "HanaTable" and should be replaced to "HANATable".
After replacing i am getting error:

I renamed this manually (i suppose in VBa it is not possible).
But why it is no more connected with workbook?

steps to reproduce:
1) renamte "hanaTable" to "HANAtable".
2) Try to refresh power query.
3) See the error.

I need code to do this or workaround...
Please help,

link to example workbook:

ExampleConnectionsv5.xlsm

drive.google.com

Best,
Jacek

 

[RoryA]

You just need to change the connection string to use Location=PQHANATable

 

[jaryszek]

thank you so much Rory,

i will try,

one more question, the only way to rename power query is manual renaming?

Best,
Jacek

 

[RoryA]

No, you can do it in code - for example:

activeworkbook.queries(1).Name = "NEWname"

 

[jaryszek]

thank you!

How can i take from query table the name of the workbook query?

ThisWorkbook.Sheets("HANAtable").ListObjects(1).QueryTable.name = "NewName" is not working.

Best,
Jacek

 

[RoryA]

You can only directly get the connection name - eg ThisWorkbook.Sheets("HANAtable").ListObjects(1).QueryTable.WorkbookConnection.Name but from that you can generally extract the query name.

 

[jaryszek]

Thank you,

ok i have checked and unfortunately it is not working,

so code:

Sub CheckQT()

Dim qt As WorkbookQuery
Dim qtT As QueryTable

ActiveWorkbook.Queries(1).Name = "PQHANATable"

Set qtT = ThisWorkbook.Sheets("HANAtable").ListObjects(1).QueryTable
qtT.EnableRefresh = True
qtT.Connection = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=PQHANATable;Extended Properties="
End Sub

It is changing name perfectly but setting connection is not working.
Why?

How to set up this to make this working ?

after this step i am getting error from above (download failed):

ActiveWorkbook.Queries(1).Name = "PQHANATable"

i tried also to reverse the order - first changing connection and after query name but it is the same.

Best,
Jacek

 

[RoryA]

I'll have to dig into it. A macro recorded while successfully changing the connection then doesn't work if you rename the query and replay the macro. The connection string doesn't actually change.

 

[jaryszek]

Ok thank you for digging in into. Let me know please,

i am struggling with issue 2 days

Best,
Jacek

 

[jaryszek]

Hi Rory,

i think i have found another bug.
So if you are changing cases and string is the same --> you will have this error. But if tableName1 <> tableNameToReplace --> code will work. Without changing connection even jsut changing power query name.

What do you think?
I am in constant contanct with Excel developemt Team manager so i will ask him about fixing it.

Best,
Jacek