VBA Error when creating power query with variables

[Berger1012]

I have a problem with VBA. I want to create a power query and I get this error: run-time error 1004, application or object-defined error. I really don't know how to fix it and would really hope that someone can help me.

This is my code:

Sub Makro1()

Dim varName As Variant
varName = Range("A1").Value

Dim varSource As Variant
varSource = Range("A2").Value

ActiveWorkbook.Queries.Add Name:=varName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(File.Contents(""" & varSource & """),[Delimiter="","", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"" = Table.TransformColumns(#""Höher gestufte Header"",{{""<OPEN>"", Json.Docum" & _
        "ent}, {""<HIGH>"", Json.Document}, {""<LOW>"", Json.Document}, {""<CLOSE>"", Json.Document}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"""

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & varName & """;Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM varName")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.Name = varName
.Refresh BackgroundQuery:=False

End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Range("L15").Select

End Sub

In this code I have two variables. Both are in the Excel sheet. Variable varName is the name of the query and varSource is the source of the Windows Explorer link.

These are the current values in the Excel sheet:

1.) varName: appf us

2.) varSource: C:\Users\Thoma\Downloads\Neuer Ordner\appf.us.txt

I think the error must be somewhere in the variable varName. Because when I insert the values of the variables in the code it works. But when I run the makro it creates the power query connection but does not load the data. When I then go to "edit power query" and look at the source I see that the source is correct. So the error hast to be with varName.

Also when I debug the code it tells me that the error occurs in this line: .Refresh BackgroundQuery:=False

I also don't have some other querys ore querys with the same name in the Excel workbook.

And this is the code of creating a power query that I recorded with the makrorecorder and without booth variables:

Sub Makro1()

ActiveWorkbook.Queries.Add Name:="appf us", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Csv.Document(File.Contents(""C:\Users\Thoma\Downloads\Neuer Ordner\appf.us.txt""),[Delimiter="","", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Analysierte JSON"" = Table.TransformColumns(#""Höher gestufte Header"",{{""<OPEN>"", Json.Docum" & _
"ent}, {""<HIGH>"", Json.Document}, {""<LOW>"", Json.Document}, {""<CLOSE>"", Json.Document}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Analysierte JSON"""

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""appf us"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [appf us]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "appf_us"
.Refresh BackgroundQuery:=False
End With Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False Range("L15").Select
End Sub

I could not add a textfile so I created this Google drive link so you can download a sample textfile if you want to: https://drive.google.com/file/d/1Krv2JRV88dAhTTfp4xB1nwqs1u9_q7m3/view?usp=sharing

Any help will be appreciated! I really hope that someone has a solution for this problem.

Thank you so much!

[#2]

Does the code work if the query of that name is not in the workbook?

[#3]

Thank you so much for your answer!

I just changed the variables to this:

Dim varName As Variant
varName = "appf us"

Dim varSource As Variant
varSource = "C:\Users\Thoma\Downloads\Neuer Ordner\appf.us.txt"

No I get the same error again. The debugger tells me, that the error occours in this line of the complete code:

.ListObject.DisplayName = varName

So I still think that the error has to be in the variable "varName" but I can't find a solution to fix it.

[#4]

A listobject's name cannot contain spaces, that is the source of your error.

[#5]

Thank you for your answer!

I just changed the varibale varName to this:

varName = "appf"

The space is now deleted but it still does not work. The error is also still the same but now the debugger tells me that the error occours in this line of the complete code:

.Refresh BackgroundQuery:=False

[#6]

Now you've gotten to this stage you can go into the query and see which step causes an error. Stop the VBA macro first of course.

[#7]

I really appreciate your help!

The data is loaded in the power query editor like it should be. It looks exactly the same as it looked when I created the power query with the editor. Take a look:

But in the Excel sheet there is no power query table. It just says "ExternData_1: receiving Data...". Here is a Screenshot:

When I press the data update button in Excel I get this error:

"The import varName does not match an export. This is maybe caused by an missing module reference"

So the error has to be at the variable varName but I am still not able to find a reason for this. 

I am sorry that this problem is so complicated but I really want to thank you for your time!

[#8]

What happens if you click the update button within PowerQuery? And are you sure you are looking at the right Query?