VBA throws an error when a string is changed to a variable with exact string

Question

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.

Please, try changing Source = Excel.Workbook(File.Contents(fileOrder)... with Source = Excel.Workbook(File.Contents(""" & fileOrder & """)... You must incapsulate the necessary variabile between double quotes. — FaneDuru
– FaneDuru, Commented May 6, 2021 at 19:15
This works!! I should have checked. first I tried with " & 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 — Derik72
– Derik72, Commented May 6, 2021 at 20:27

horseyride · Accepted Answer · 2021-05-06 20:17:59Z

0

Why not use VBA to write the path to a named cell range, and have powequery read it in?

let Source = Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]{0}[Column1]),

or

let  Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]{0}[Column1]), null, true),

answered May 6, 2021 at 20:17

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Derik72 Over a year ago

(""" & fileOrder & """) using 3 doubes worked for me.

Collectives™ on Stack Overflow

VBA throws an error when a string is changed to a variable with exact string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related