ERROR 1004 Using macro for automate tables from .txt querie

Coacho

New Member
Joined
Aug 1, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,224,830
Messages
6,181,229
Members
453,026
Latest member
cknader

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top