Hi, I had a plan about using a macro to make some report automatic each week for the month is being used, but when im getting the second report, I get this error 1004 .ListObject.DisplayName = "_Mb25"
Is there any solution to the code?
Public SapGuiAuto, WScript, msgcol
Public objGui As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Public objSBar As GuiStatusbar
Public objSheet As Worksheet
Sub OPENMB25()
If objGui Is Nothing Then
Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
End If
If objConn Is Nothing Then
Set objConn = objGui.Children(0)
End If
If session Is Nothing Then
Set session = objConn.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject objGui, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "mb25"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Mb25.txt"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
ActiveWorkbook.Queries.Add Name:="Mb25", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Origen = Csv.Document(File.Contents(""D:\OneDrive - Oleoducto de los Llanos Orientales - Oleoducto Bicentenario de Colombia\Desktop\Seguimiento a reservas\Mb25.txt""),[Delimiter=""|"", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado"" = Table.TransformColumnTypes(Origen,{{""Column1"", type text}, {""Column2"", type text}, {""C" & _
"olumn3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {" & _
"""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}})," & Chr(13) & "" & Chr(10) & " #""Columnas quitadas"" = Table.RemoveColumns(#""Tipo cambiado"",{""Column1""})," & Chr(13) & "" & Chr(10) & " #""Filas en blanco eliminadas"" = Table.SelectRows(#""Columnas quitadas"", each not List.IsEmpty(List.RemoveMatchin" & _
"gItems(Record.FieldValues(_), {"""", null})))," & Chr(13) & "" & Chr(10) & " #""Encabezados promovidos"" = Table.PromoteHeaders(#""Filas en blanco eliminadas"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Columnas quitadas1"" = Table.RemoveColumns(#""Encabezados promovidos"",{""""})," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado1"" = Table.TransformColumnTypes(#""Columnas quitadas1"",{{""Fecha nec."", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " " & _
" #""Tipo cambiado1"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Mb25;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Mb25]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_Mb25"
.Refresh BackgroundQuery:=False
Thanks.
Is there any solution to the code?
Public SapGuiAuto, WScript, msgcol
Public objGui As GuiApplication
Public objConn As GuiConnection
Public session As GuiSession
Public objSBar As GuiStatusbar
Public objSheet As Worksheet
Sub OPENMB25()
If objGui Is Nothing Then
Set SapGuiAuto = GetObject("SAPGUI")
Set objGui = SapGuiAuto.GetScriptingEngine
End If
If objConn Is Nothing Then
Set objConn = objGui.Children(0)
End If
If session Is Nothing Then
Set session = objConn.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject objGui, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "mb25"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "Mb25.txt"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
ActiveWorkbook.Queries.Add Name:="Mb25", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Origen = Csv.Document(File.Contents(""D:\OneDrive - Oleoducto de los Llanos Orientales - Oleoducto Bicentenario de Colombia\Desktop\Seguimiento a reservas\Mb25.txt""),[Delimiter=""|"", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado"" = Table.TransformColumnTypes(Origen,{{""Column1"", type text}, {""Column2"", type text}, {""C" & _
"olumn3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {" & _
"""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}})," & Chr(13) & "" & Chr(10) & " #""Columnas quitadas"" = Table.RemoveColumns(#""Tipo cambiado"",{""Column1""})," & Chr(13) & "" & Chr(10) & " #""Filas en blanco eliminadas"" = Table.SelectRows(#""Columnas quitadas"", each not List.IsEmpty(List.RemoveMatchin" & _
"gItems(Record.FieldValues(_), {"""", null})))," & Chr(13) & "" & Chr(10) & " #""Encabezados promovidos"" = Table.PromoteHeaders(#""Filas en blanco eliminadas"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Columnas quitadas1"" = Table.RemoveColumns(#""Encabezados promovidos"",{""""})," & Chr(13) & "" & Chr(10) & " #""Tipo cambiado1"" = Table.TransformColumnTypes(#""Columnas quitadas1"",{{""Fecha nec."", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " " & _
" #""Tipo cambiado1"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Mb25;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Mb25]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_Mb25"
.Refresh BackgroundQuery:=False
Thanks.