import external data

[Swan0tree]

I try to import an email address list of 500 which I have in deliminated
text. I do all the steps in the Text Import Wizard but when I get to the
point to import, I can only import into row 1 horizontal, each email in one
column which doesn't work since there are too many email addresses to fit.
I can't figure out how I can import the email addresses into Column A
vertical down, row 1 -500. There is no option to choose.

Thanks for your help. Gary

 

[Don Guillett]

More info../..

 

[David Biddulph]

When you've imported into a row, you can transpose into a column.
You may, however, be better changing your input text file. Perhaps change
your delimiter from comma or tab to a new line or paragraph character, then
when you import it will put each in a new row.

 

[Steve Yandl]

Gary,

If the delimiter is a comma, here is a subroutine you could place in a
module and use to bring the email address entries into Column A.
You would need to edit the line
strFilePath = "C:\Test\myList.txt"
so that it pointed to the correct path and file name for your text file. If
the delimiter is something other than a comma, the VBA can be modified very
easily.

'-----------------------------------
Sub ImportTextAsColumn()

Const ForReading = 1
Dim strList As String

strFilePath = "C:\Test\myList.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

strLine = objFile.ReadAll
objFile.Close
strLine = Replace(strLine, vbCrLf, ",")

arrList = Split(strLine, ",")

For i = 0 To UBound(arrList)
Cells(i + 1, 1).Value = Trim(arrList(i))
Next i

Set objFile = Nothing
Set objFSO = Nothing
End Sub

'-----------------------------------

Steve Yandl