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]

I am in constant contanct with Excel developemt Team manager so i will ask him about fixing it.

Good luck!

 

[jaryszek]

Ok but can you please confirm ?

If this is a bug or i am missing something?

Best,
Jacek

 

[RoryA]

Yes, it seems to work fine with a completely different name.

 

[jaryszek]

thank you so much! Ah

 

[jaryszek]

Ok Rory,

after few more tests seems that problem is not that general.

Tried with code:

Sub CheckQT()

Dim qt As WorkbookQuery
Dim qtT As QueryTable
Set qtT = ThisWorkbook.Sheets("HANAtable").ListObjects(1).QueryTable
qtT.EnableRefresh = True
qtT.Connection = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=PQnewName;Extended Properties="
qtT.CommandText = "SELECT * FROM [PQnewName]"

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

End Sub

and error occured once again. But connection has changed and sql command also.
Connection was lost...

Can you please check maybe you will find why?
Or maybe another forum guys have idea how to fix it?

Best,
Jacek

 

[RoryA]

You should change the query name before you change anything that refers to it.

 

[jaryszek]

ech i also tried to change query name as first (still error occurs) and when you are doing this manually you do not have to even change any connection.

In vba after renaming you are getting our error.
So i suppose it is a bug.

Best,
Jacek