Code to import from txt to excel

[Guest]

Any help, highly appreciated:

I am trying to get a macro to import a text file in a table format into excel
I have the following code but this code does not give me the format properly:

Sub importtext()
x = 1
Open "c:\trial.txt" For Input As #1
Do While Not EOF(1)
Input #1, Line
Worksheets("sheet1").Cells(x, 1).Value = Line
x = x + 1
Loop
Close #1
End Sub

 

[Earl Kiosterud]

N.F.:

You might better set up an import query. Data - Import external data - Import data. It
will give you a Select Data Source dialog. Navigate to the folder of your text file, change
the file type box to that of your text file (*.txt, *.csv, whatever), and select it. It
will then start the Text Import Wizard, where you can tell it if it's delimited or fixed,
and all kinds of stuff. When you've done, the data will be in the sheet. Then when you
want it to read the file again (into the same sheet, replacing the old data), right click
the area in the sheet, and choose Refresh. You can record a macro for doing the refresh if
you wish. It takes the grunt work out of importing data very nicely.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Guest]

Thank you Earl

N.F.:

You might better set up an import query. Data - Import external data - Import data. It
will give you a Select Data Source dialog. Navigate to the folder of your text file, change
the file type box to that of your text file (*.txt, *.csv, whatever), and select it. It
will then start the Text Import Wizard, where you can tell it if it's delimited or fixed,
and all kinds of stuff. When you've done, the data will be in the sheet. Then when you
want it to read the file again (into the same sheet, replacing the old data), right click
the area in the sheet, and choose Refresh. You can record a macro for doing the refresh if
you wish. It takes the grunt work out of importing data very nicely.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...