Dave Smith
New Member
- Joined
- Jul 5, 2021
- Messages
- 32
- Office Version
- 2016
- Platform
- Windows
Hi Experts,
I am new to vba code & got stuck in between the vba code, i am here to get help for this forum.
I need to get data from the .txt file for that i have recorded macro, but for this it created query along with its name. Now if i delete the data to read another file its not working.
pl. find image showing data stored in text file.
Below is the code which i have recorded.
ActiveWorkbook.Queries.Add Name:="head_2d", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Table.FromColumns({Lines.FromBinary(File.Contents(""C:\Users\BGTY\Desktop\Type_2d.out""), null, null, 1252)})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.SplitColumn(Source, ""Column1"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {""Column1.1"", ""Column1.2""})," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Split Column by D" & _
"elimiter"",{{""Column1.1"", type text}, {""Column1.2"", type text}})" & 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=Type_2d;Extended Properties=""""" _
, Destination:=Range("$A$3")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [head_2d]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "head_2d"
.Refresh BackgroundQuery:=False
End With
Range("L13").Select
Any Help are welcomed.
Regards,
Dave
I am new to vba code & got stuck in between the vba code, i am here to get help for this forum.
I need to get data from the .txt file for that i have recorded macro, but for this it created query along with its name. Now if i delete the data to read another file its not working.
pl. find image showing data stored in text file.
Below is the code which i have recorded.
ActiveWorkbook.Queries.Add Name:="head_2d", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Table.FromColumns({Lines.FromBinary(File.Contents(""C:\Users\BGTY\Desktop\Type_2d.out""), null, null, 1252)})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.SplitColumn(Source, ""Column1"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {""Column1.1"", ""Column1.2""})," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Split Column by D" & _
"elimiter"",{{""Column1.1"", type text}, {""Column1.2"", type text}})" & 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=Type_2d;Extended Properties=""""" _
, Destination:=Range("$A$3")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [head_2d]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "head_2d"
.Refresh BackgroundQuery:=False
End With
Range("L13").Select
Any Help are welcomed.
Regards,
Dave