VBA code to get data from Text File?

Dave Smith

New Member
Joined
Jul 5, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. 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
 

Attachments

  • Screenshot 2022-02-28 143130.jpg
    Screenshot 2022-02-28 143130.jpg
    37 KB · Views: 12
  • Screenshot 2022-02-28 143608.jpg
    Screenshot 2022-02-28 143608.jpg
    121.3 KB · Views: 12

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,181,436
Messages
5,929,868
Members
436,700
Latest member
lewismorg

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
Top