Read text into Excel

[Brad]

I have a text file that looks like this:

Company Name
Name of Person
Telephone Number
Street Address
City, State Zip
Business Type

Company Name
Name of Person
Telephone Number
etc.....

How can I read these blocks of data into and Excel spreadsheet organized in
columns by Company Name, Name of Person, Telephone Number, etc.

Thanks
Brad

 

[TWR]

If you just want to import it into a spreadsheet manually, No Code, from the
file menu select Open and change the file types to All Files "*.*" and then
follow the import wizard.

If you want to import the text in code, you need to know if the file is
delimited or fixed length, assuming that each record has the complete set of
data.

A comma delimited file would actually look like:
Company Name,Name of Person,Telephone Number,Street
Address,City,State,Zip,Business Type

Is this what the file looks like or is each field of data on a separate line
like you show it?

 

[Rick Rothstein \(MVP - VB\)]

Give the following subroutine a try.

Sub ImportText(FileName As String)
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
Const DataRowStart As Long = 2
Const DataColStart As Long = 4
FileNum = FreeFile
Open "C:\TEMP\Test.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Lines = Split(TotalFile, vbNewLine)
For X = 0 To UBound(Lines)
Cells(DataRowStart + X \ 7, DataColStart + (X Mod 7)).Value = Lines(X)
Next
End Sub

The above code assumes the exact layout of data in the text file that you
showed... 7 lines of data devoted to a single record with the 7th line being
empty (although if there is ever data in that 7th line, it will entered into
the spreadsheet). Simply call the ImportText subroutine from your code and
pass the filename and full path into it. Something like this...

Sub Test()
ImportText "C:\TEMP\Test.txt"
End Sub

Note that the ImportText subroutine has two Const (constant) statements that
allow you to set the first row and column to start entering data into your
spreadsheet at. The above example assumes data starts at Row 2 (to account
for headers in Row 1) and an arbitrary starting column of Column 4 (because
you didn't specify a location for your data). Change these as needed.

Rick

 

[Brad]

Thanks Rick. That did the trick.

Give the following subroutine a try.

Sub ImportText(FileName As String)
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
Const DataRowStart As Long = 2
Const DataColStart As Long = 4
FileNum = FreeFile
Open "C:\TEMP\Test.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Lines = Split(TotalFile, vbNewLine)
For X = 0 To UBound(Lines)
Cells(DataRowStart + X \ 7, DataColStart + (X Mod 7)).Value = Lines(X)
Next
End Sub

The above code assumes the exact layout of data in the text file that you
showed... 7 lines of data devoted to a single record with the 7th line being
empty (although if there is ever data in that 7th line, it will entered into
the spreadsheet). Simply call the ImportText subroutine from your code and
pass the filename and full path into it. Something like this...

Sub Test()
ImportText "C:\TEMP\Test.txt"
End Sub

Note that the ImportText subroutine has two Const (constant) statements that
allow you to set the first row and column to start entering data into your
spreadsheet at. The above example assumes data starts at Row 2 (to account
for headers in Row 1) and an arbitrary starting column of Column 4 (because
you didn't specify a location for your data). Change these as needed.

Rick

 

[TWR]

I think I'll stop answering these things. Your answers a much better.

 

[Brad]

Your answer was fine. Don't stop. All input is appreciated.