Hello,
I am running power queries from VBA and am having issues reusing my old connections. I am working in a system where we need to delete and recreate the output sheet each time so I can't just refresh the old connections. Even after deleting the sheet, I can see the old connections in the ThisWorkbook.Connections collection, so instead of deleting the connections and recreating them, is it possible to use an old connection to output to a new sheet without creating a new connection?
This is the code I use to make the original connections:
Sub loadToWorksheet(query As WorkbookQuery, workingSheet As Excel.Worksheet, outputTableName As String)
With workingSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
, Destination:=workingSheet.Range("A1"))
.Name = outputTableName
With .QueryTable
.CommandType = xlCmdDefault
.CommandText = Array("SELECT * FROM [" & query.Name & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.PreserveColumnInfo = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.Refresh BackgroundQuery:=False
End With
End With
End Sub
I have tried adding the old connection to the ListObjects of the new sheet, but the add function can't take in connections.
It feels like what I am trying to do is not possible since connections seem to be tied to a sheet, but I wanted to see if I was missing anything.
Thank you!
