kfschaefer1
New Member
- Joined
- Feb 28, 2016
- Messages
- 9
I need help with the syntax to pass the query name and set a table's datasource from an existing list of queries within the workbook.
I tried to modify the syntax for the LoadtoWorksheetOnly code from the suggested code sample, but I am not familiar with using a workbookquery.
I am trying to pass the query name that is stored on the MainForm Cell J3 and use this as a variable within the connection string and reset the table ("InvoiceTbl") data source. Note some of this code came from recording the macro.
I tried to modify the syntax for the LoadtoWorksheetOnly code from the suggested code sample, but I am not familiar with using a workbookquery.
I am trying to pass the query name that is stored on the MainForm Cell J3 and use this as a variable within the connection string and reset the table ("InvoiceTbl") data source. Note some of this code came from recording the macro.
Code:
[COLOR=#000000][FONT=Courier]Sub LoadToWorksheetOnly(query As WorkbookQuery) ', currentSheet As Worksheet)[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
' The usual VBA code to create ListObject with a Query Table[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
' The interface is not new, but looks how simple is the conneciton string of Power Query:[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
With currentSheet.ListObjects.Add(SourceType:=0, Source:= _[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
, Destination:=Range("$A$18")).QueryTable[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.CommandType = xlCmdDefault[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.CommandText = Array("SELECT * FROM [" & query.Name & "]")[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.RowNumbers = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.FillAdjacentFormulas = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.PreserveFormatting = True[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.RefreshOnFileOpen = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.BackgroundQuery = True[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.RefreshStyle = xlInsertDeleteCells[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.SavePassword = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.SaveData = True[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.AdjustColumnWidth = True[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.RefreshPeriod = 0[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.PreserveColumnInfo = False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
.Refresh BackgroundQuery:=False[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
End With
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]End Sub[/FONT][/COLOR]
Last edited:
