Apr 20 2020 12:21 AM - edited Apr 20 2020 12:50 AM
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!
Apr 20 2020 02:31 AM
Apr 20 2020 02:59 AM - edited Apr 20 2020 03:00 AM
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.
Apr 20 2020 03:04 AM
Apr 20 2020 03:10 AM
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
Apr 20 2020 06:01 AM
Apr 20 2020 08:59 AM - edited Apr 20 2020 09:42 AM
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!
Apr 21 2020 01:42 AM