Dave Peterson wrote:
Option Explicit
sub testme

dim myRng as range
dim myCell as range
dim TestStr as string
dim myPath as string
dim myFileName as string

with worksheets("sheet999")
set myrng = .range("a2:A10")
end with

myPath = "h:\textfiles\"

for each mycell in myrng.cells
if mycell.value = "" then
'skip it
else
myfilename = mypath & mycell.value & ".txt"
teststr = ""
on error resume next
teststr = dir(myfilename)
on error goto 0
if teststr = "" then
'not found!
msgbox myfilename & " wasn't found!"
else
ImportTextFile myfilename, ";"
end if
end if
next mycell

end sub

is one way.

wrote:

I want to use Chip Pearson text import code from:
http://www.cpearson.com/excel/imptext.htm.

I see that I can feed the text file names to the subroutine by using
the code: ImportTextFile "c:\temp\test.txt", ";".

This is an easy programming question, but how do I feed the sub several
text file names derived from cells in a column, that don't have the
file extension ".txt" appended? I see that VBA is somewhat like
functions, but can I just say: ImportTextFiles ("h:\textfiles\" & D47 &
" "), " " to have it import a text file from the h:\textfiles\
Directory with the name of whatever the name is in cell D47 to the
active cell?

--

Dave Peterson

Mr. Peterson,

Excellent it works great but there are two problems:

1. The text files keep overwriting themselves on import instead of
moving to the next free row down (I have the text import start at Cell
K251 and this can actually stay as a constant instead of pasting to the
active cell).

2. Secondly, only the first and second columns of the data paste in.
The data is in the form of drive mapping information preceded by user
names. For instance:

pinchpa H: \\inetpub.application.net\change Netware Server
pinchpa J: \\inetpub.application.net\test2
pinchpa S: \\inetpub.application.net\change_shared Microsoft Windows
Server

You can see there is potentially 6 columns separated by spaces. I need
only the irst three to import. VBA is stopping after the colon and not
importing the third column (let alone the 4th, 5th and 6th which I
don't want anyway). Do I need to invoke the text wizard?

I appreciate your help.