Use VBA with Power Query to import CSV files

[JeffKotnik]

Folks, I am posting for the first time here and please forgive me if I don't do it exactly right.

I am working on a VBA program that will import CSV files to Excel using Power Query. I am quite familiar with VBA but have never used Power Query.

So I recorded a macro to get the code. It works perfectly.

But the problem is, since I recorded the macro, the file location and name are absolute references (Source = Csv.Document(File.Contents(""C:\aaaTest\test 01.csv"")). I need to replace these with variables. But the code as written won't accept a variable. How would I structure this code so as to be able use variables?

wbControl.Queries.Add Name:="Query1", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\aaaTest\test 01.csv""), [Delimiter=""="", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

I appreciate any help, including just clues, that will point me in the right direction.

Thanks,
Jeff

 

[GooberTron]

I'm guessing a bit, but it looks like the VBA code is just passing in a massive string to the Formula:= argument. This appears simply to be the m-code that you usually see in the Power Query advanced editor. So just carefully build up that string first using variables and then pass it in? You just need to be careful with the thousands of quotation marks around all the different bits!

 

[JeffKotnik]

I'm guessing a bit, but it looks like the VBA code is just passing in a massive string to the Formula:= argument. This appears simply to be the m-code that you usually see in the Power Query advanced editor. So just carefully build up that string first using variables and then pass it in? You just need to be careful with the thousands of quotation marks around all the different bits!

Hi GooberTron, thank you for your comment! You are absolutely right that it is just a big long string. The problem is that after the first bit of code in that line (wbControl.Queries.Add Name:="Query1",) there doesn't seem to be a way to pass a variable into any part of the rest of it. I have tried recording a macro with the legacy Text Import Wizard, as opposed to importing with Power Query, but the result is the same. The file source is a string and the code won't accept a variable.

When I posted this I was thinking there was probably a simple solution to the problem. But now I realize that my VBA code probably needs an entirely different structure than that obtained by simply recording a macro.

 

[GooberTron]

Hi Jeff,

How's your VBA?

I think your "wbControl" is a workbook variable? In my code I've just used the default ActiveWorkbook.

But you just need to set up a string and replace it after the Formula:= part.

Have a look at this to see what I mean (I've shown the original string as a comparator too). Sorry if I've totally misunderstood the problem! Seems to work OK here.

Sub PowerQuery()

    Dim strFilepath As String
    Dim strFormulaOriginal As String
    Dim strFormulaNew As String
    
    strFilepath = "C:\aaaTest\test 01.csv"
    strFormulaOriginal = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\aaaTest\test 01.csv""), [Delimiter=""="", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    strFormulaNew = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & strFilepath & """), [Delimiter=""="", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    
    ActiveWorkbook.Queries.Add Name:="Query1", Formula:=strFormulaNew

End Sub

 

[JeffKotnik]

Hi Jeff,

How's your VBA?

I think your "wbControl" is a workbook variable? In my code I've just used the default ActiveWorkbook.

But you just need to set up a string and replace it after the Formula:= part.

Have a look at this to see what I mean (I've shown the original string as a comparator too). Sorry if I've totally misunderstood the problem! Seems to work OK here.

Sub PowerQuery()

    Dim strFilepath As String
    Dim strFormulaOriginal As String
    Dim strFormulaNew As String
   
    strFilepath = "C:\aaaTest\test 01.csv"
    strFormulaOriginal = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\aaaTest\test 01.csv""), [Delimiter=""="", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    strFormulaNew = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & strFilepath & """), [Delimiter=""="", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
   
    ActiveWorkbook.Queries.Add Name:="Query1", Formula:=strFormulaNew

End Sub

Thank you so much GooberTron. It works perfectly.

So your solution was to turn the big long "formula" string into a variable. I have taken note!

You added a pair of pair of quotes which I do not understand. I believe the result of the concatenation below would be this: ("C:\aaaTest\test 01.csv"). But in the original string (from recording the macro, the file reference was like this with two quotes: (""C:\aaaTest\test 01.csv"").

Csv.Document(File.Contents(""" & strFilepath & """)

In any event thank you so much. I was thoroughly perplexed.

Jeff

 

[GooberTron]

Hi Jeff,

All that long code mess is just ways of dealing with strings.
It's worth printing those strings to the immediate window in the VBA editor, or keeping an eye on them in the Locals window to see what they really look like as you step through the code in debug mode. All those quotation marks littered through the string are there to either break out and add in Chr(13) and Chr(10) which represent linefeeds or carriage returns. Also - your main string body needs to be enclosed by " at the beginning and end - but what do you do if you really need a quotation mark INSIDE the string - that's what the double quotation mark is "". It's signifying that you don't really want to end the string but want to insert quotation marks inside. All I did was add a third quotation mark either side to signify breaking out of the string and inserting the variable inside (i.e. joining it in using the & symbol)

 

[JeffKotnik]

Hi Jeff,

All that long code mess is just ways of dealing with strings.
It's worth printing those strings to the immediate window in the VBA editor, or keeping an eye on them in the Locals window to see what they really look like as you step through the code in debug mode. All those quotation marks littered through the string are there to either break out and add in Chr(13) and Chr(10) which represent linefeeds or carriage returns. Also - your main string body needs to be enclosed by " at the beginning and end - but what do you do if you really need a quotation mark INSIDE the string - that's what the double quotation mark is "". It's signifying that you don't really want to end the string but want to insert quotation marks inside. All I did was add a third quotation mark either side to signify breaking out of the string and inserting the variable inside (i.e. joining it in using the & s

Hi Jeff,

All that long code mess is just ways of dealing with strings.
It's worth printing those strings to the immediate window in the VBA editor, or keeping an eye on them in the Locals window to see what they really look like as you step through the code in debug mode. All those quotation marks littered through the string are there to either break out and add in Chr(13) and Chr(10) which represent linefeeds or carriage returns. Also - your main string body needs to be enclosed by " at the beginning and end - but what do you do if you really need a quotation mark INSIDE the string - that's what the double quotation mark is "". It's signifying that you don't really want to end the string but want to insert quotation marks inside. All I did was add a third quotation mark either side to signify breaking out of the string and inserting the variable inside (i.e. joining it in using the & symbol)

Thank you Goobertron. My code works and I have learned a lot : )