Importing a text file

  • Thread starter Thread starter Simple
  • Start date Start date
S

Simple

Is it possible to import a text file into Excel? I have a text file with
thousands of company names and address on (all formatted as below) that I
would like to import into Excel...problem is that I want to import them into
rows with each address detail falling under the correct column heading i.e.
Column for Company name, column for Address1 column for address 2 etc.

Company Name
Address
Address2
City
Postal Code

Company Name
Address
Address2
City
Postal Code
etc etc

Ultimately I need to use these address's to mail merge, otherwise if
somebody has a better suggestion for using a text file to mail merge with MS
Word I'm more than happy to listen.
Thanks
Si
 
Hi
do all records ALWAYS consist of 5 rows and are they ALL separated by
one blank row?
 
Hi Frank
There is one row separating the address's yes and no they consist of more
than 5 rows...some have 7 rows some have 8 rows. If this is a problem with
the differing rows I could manual edit the number of rows so that they all
have 7 , unless that is you can tell me a way of doing this with differing
number of rows.
Si
 
One way:

Assuming the records are always separated by a blank row:

Public Sub ImportVerticalRecords()
Dim vInput As Variant
Dim nFileHandle As Long
Dim nRow As Long
Dim nCol As Long
Dim sFileName As String

nFileHandle = FreeFile
sFileName = Application.GetOpenFilename()
If sFileName = "" Then Exit Sub
Open sFileName For Input As #nFileHandle
nRow = 1
nCol = 1
Do While Not EOF(nFileHandle)
Line Input #nFileHandle, vInput
If Len(Trim(vInput)) = 0 Then
nRow = nRow + 1
nCol = 1
Else
ActiveSheet.Cells(nRow, nCol) = vInput
nCol = nCol + 1
End If
Loop
End Sub
 
Back
Top