Forum Discussion
Berger1012
Apr 20, 2020Copper Contributor
VBA Error when creating power query with variables
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!
- JKPieterseSilver ContributorDoes the code work if the query of that name is not in the workbook?
- Berger1012Copper Contributor
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.- JKPieterseSilver ContributorA listobject's name cannot contain spaces, that is the source of your error.