I have one .xlsm file generated by SAP BeX Analyzer on sharedir, if i try to open the file and extract it to another workbook, I get error due to the fact, BeX is 32bit and I have 64bit Office. Therefore I went with powerquery, it works fine, but from time to time, it drops Runtime 1004 Expression.SyntaxError: Token Equal expected.
The problem happens when .Refresh BackgroundQuery:=False is inititated.
I made some changes in the code - mostly variables.
My code:
Set wb = Workbooks.Add
wb.Queries.Add Name:="DF_GRID_1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(" & Chr(34) & path & sfile & Chr(34) & "), null, true)," & Chr(13) & "" & Chr(10) & " DF_GRID_1_DefinedName = Source{[Item=""DF_GRID_1"",Kind=""DefinedName""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(DF_GRID_1_De" & _
"finedName, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Sending System"", type text}, {""Organizational Unit"", type text}, {""Contract Number"", type text}, {""Contract Number Description"", type text}, {""Contract Start Date"", type date}, {""Contract position"", Int64.Type}, {""Material number ID"", type" & _
" text}, {""Material Description"", type text}, {""Start date of accept"", type date}, {""Minimum contract ter"", Int64.Type}, {""End date of previous"", type text}, {""Blacklist ID"", type text}, {""Contract position st"", Int64.Type}, {""Contract position ty"", Int64.Type}, {""Portfolio ID"", Int64.Type}, {""Business Area"", Int64.Type}, {""Distribution channel"", " & _
"Int64.Type}, {""Material group"", Int64.Type}, {""Condition Type"", Int64.Type}, {""Date From"", type date}, {""Price duration in mo"", Int64.Type}, {""Currency"", type text}, {""Months before min. c"", type text}, {""Amortization Period"", Int64.Type}, {""Selection period"", type date}, {""Sales Quantity of contract position"", Int64.Type}, {""Value per month"", ty" & _
"pe number}, {""SSP Value"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DF_GRID_1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [DF_GRID_1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "DF_GRID_1"
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
I removed adding new sheet, make file and workbook variable, table from BeX (DF_GRID_1) remains.
The code when I record macro:
ActiveWorkbook.Queries.Add Name:="DF_GRID_1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""\\server\_Monthly closing\IFRS_CNTR_RAW_DATA.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & " DF_GRID_1_DefinedName = Source{[Item=""DF_GRID_1"",Kind=""DefinedName""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(DF_GRID_1_De" & _
"finedName, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Sending System"", type text}, {""Organizational Unit"", type text}, {""Contract Number"", type text}, {""Contract Number Description"", type text}, {""Contract Start Date"", type date}, {""Contract position"", Int64.Type}, {""Material number ID"", type" & _
" text}, {""Material Description"", type text}, {""Start date of accept"", type date}, {""Minimum contract ter"", Int64.Type}, {""End date of previous"", type text}, {""Blacklist ID"", type text}, {""Contract position st"", Int64.Type}, {""Contract position ty"", Int64.Type}, {""Portfolio ID"", Int64.Type}, {""Business Area"", Int64.Type}, {""Distribution channel"", " & _
"Int64.Type}, {""Material group"", Int64.Type}, {""Condition Type"", Int64.Type}, {""Date From"", type date}, {""Price duration in mo"", Int64.Type}, {""Currency"", type text}, {""Months before min. c"", type text}, {""Amortization Period"", Int64.Type}, {""Selection period"", type date}, {""Sales Quantity of contract position"", Int64.Type}, {""Value per month"", ty" & _
"pe number}, {""SSP Value"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DF_GRID_1;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [DF_GRID_1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "DF_GRID_1"
.Refresh BackgroundQuery:=False
End With
Is there any way to find out whats gone wrong? power query shows same error as VBA. I don't need anything to modify from the file, I just need to load DF_GRID_1 to worksheet of opened workbook.
The file cannot be generated differently - it is RPA generated file used by other processes. I cannot open the file with my 64bit office unless getting error message which cannot be suppressed by
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.AskToUpdateLinks = False
End With
