This is my vba code for powerquery function
ActiveWorkbook.Queries.Add Name:="Or ORder", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\DDK\Downloads\excel.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Column1"", type any}" & _
", {""CROWN"", type any}, {""March 15th, 2021"", type text}, {""Column4"", type any}, {""Column5"", type any}})," & Chr(13) & "" & Chr(10) & " #""Removed Top Rows"" = Table.Skip(#""Changed Type"",2)," & Chr(13) & "" & Chr(10) & " #""Renamed Columns"" = Table.RenameColumns(#""Removed Top Rows"",{{""Column1"", ""QTY""}, {""CROWN"", ""ITEM""}, {""March 15th, 2021"", ""Part""}, {""Column5"", ""Price""}})," & Chr(13) & "" & Chr(10) & " #""Filter" & _
"ed Rows"" = Table.SelectRows(#""Renamed Columns"", each [QTY] <> null and [QTY] <> """")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Filtered Rows"""
ActiveWorkbook.Worksheets.Add.Name = "Our Order"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Or ORder"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Or ORder]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Or_ORder"
.Refresh BackgroundQuery:=False
End With
When the file directory for the source is changed to a variable like this
Dim fileOrder as String
fileOrder = "C:\Users\Ddk\Downloads\excel.xlsx"
....
Source = Excel.Workbook(File.Contents(fileOrder), null, true)," & Chr(13) & "" & Chr(10) ""....
It throws an error when it reaches .Refresh BackgroundQuery:=False. Why is it? Even when it is the exact same string it gives an error [Expression.Error] The import Order matches no exports. Did you miss a module reference?
The thing is I want user to select the excel file for power query through VBA that's why I need to have a variable on file directory.
Source = Excel.Workbook(File.Contents(fileOrder)...withSource = Excel.Workbook(File.Contents(""" & fileOrder & """)...You must incapsulate the necessary variabile between double quotes." & fileOrder &"then I realised my mistake that I should have given one more double quotes. it still didn't work. Actual statement has to have 3 double quotes; one which denotes the string."""works