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

Thanks much! I will try both yours and Kurt's methods.