I'm trying to transform some data that is in html and are within a txt to .xls in table form, that data is pertinent there is an email body that extracts, if there is any other idea I might be pulling that body and playing as xls would help me a lot, below the attached code
The error that occurs is in listobject.displayname = ""
It says it can not find the table or the file.
If Name = "gpa txt" Then
Workbooks.Close
ActiveWorkbook.Queries.Add Name:="gpa txt", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Fonte = Web.Page(File.Contents(""C:\Users\T714225\Documents\Pão de Açucar\gpa.txt""))," & Chr(13) & "" & Chr(10) & " #""Data Expandido"" = Table.ExpandTableColumn(Fonte, ""Data"", {""Column13"", ""Endereco"", ""Loja"", ""R$ 0,01"", ""R$ 0,05"", ""R$ 0,10"", ""R$ 0,25"", ""R$ 0,50"", ""R$ 1,00"", ""R$ 2,00"", ""R$ 5,00"", ""Total R$"", ""UF""}, {""Column13"", ""Endereco"", ""Loja""," & _
" ""R$ 0,01"", ""R$ 0,05"", ""R$ 0,10"", ""R$ 0,25"", ""R$ 0,50"", ""R$ 1,00"", ""R$ 2,00"", ""R$ 5,00"", ""Total R$"", ""UF""})," & Chr(13) & "" & Chr(10) & " #""Colunas Removidas"" = Table.RemoveColumns(#""Data Expandido"",{""Caption"", ""Source"", ""ClassName"", ""Id"", ""Column13""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Colunas Removidas""" _
& "#""Colunas Reordenadas "" = Table.ReorderColumns(#""Tipo Alterado"",{""Endereco"", ""UF"", ""Loja"", ""R$ 5,00"", ""R$ 2,00"", ""R$ 1,00"", ""R$ 0 ,50"", ""R$ 0,25"", ""R$ 0,10"", ""R$ 0,05"", ""R$ 0,01"", ""Total R$""}), " & Chr(13) & "" & Chr(10) & ""
Else
Workbooks.Add
End If
With ActiveSheet.listObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""gpa txt""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [gpa txt]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "gpa.txt"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWindow.SmallScroll Down:=24
ActiveSheet.listObjects("gpa_txt").Range.AutoFilter Field:=1, Criteria1:= _
"TOTAL DAS LOJAS"
Rows("6:159").Select
Selection.Delete Shift:=xlUp
ActiveSheet.listObjects("gpa_txt").Range.AutoFilter Field:=1
ActiveWorkbook.SaveAs
Call lsSalva
End Sub