How to Use Chip Pearson's Text Import Code

[socrtwo]

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?

 

[Kurt]

Dim fname as String
Dim row as Integer, col as Integer
col = <the column containing the filenames> ' A = 1, B = 2, etc.
For row = <startrow> To <endrow>
fname = Sheets(<sheetname>).Cells(row,col).Value
do_your_stuff_here
Next row

 

[Dave Peterson]

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.

 

[socrtwo]

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.

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

 

[socrtwo]

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.

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.

 

[Dave Peterson]

#1. You'll have to adjust Chip's code to move down to the next available cell
after it imports a file (but before it imports the next).

#2. This two is in your variation of Chip's code. I don't understand how you
got potentially 6 columns out of the data you posted.

socrtwo wrote:

 

[Dave Peterson]

ps. When I do this kind of stuff, I usually just open the file to its own
workbook (recording a macro when I specify the file type) and then copy that
used range to my "real" location.

 

[socrtwo]

ps. When I do this kind of stuff, I usually just open the file to its own
workbook (recording a macro when I specify the file type) and then copy that
used range to my "real" location.

It turns out the data was pasting to other columns several to the right
as if they were piped there. The column where the drive mapping path
was simply supposed to copy the contents of column L251 on down, but
ended up somehowho emptying it and retaining the only copy of the
paths. Strange to me.

 

[socrtwo]

It turns out the data was pasting to other columns several to the right
as if they were piped there. The column where the drive mapping path
was simply supposed to copy the contents of column L251 on down, but
ended up somehowho emptying it and retaining the only copy of the
paths. Strange to me.

OK Dave, if your still there, I got it to paste the first text file at
K251 the active cell, and then offset 25 rows down with this line after
the "money shot" line:

ImportTextFile myFileName, " "
ActiveCell.Offset(25, 0).Select

OK so the mystery of why the data is being thrown to column S is still
there. Small victories are encouraging though.

 

[socrtwo]

OK Dave, if your still there, I got it to paste the first text file at
K251 the active cell, and then offset 25 rows down with this line after
the "money shot" line:

ImportTextFile myFileName, " "
ActiveCell.Offset(25, 0).Select

OK so the mystery of why the data is being thrown to column S is still
there. Small victories are encouraging though.

OK again, sorry for wasting your time. This is what the data really
looks like. I tried to make up data and it wasn't like really there.
It appears for every space, Excel is moving one column over until it
ends up in the S column before writing the path. I think I'm going to
need to invoke the text import wizard with it's count consecutive
delimiters as one option.:

PinchPa H: \\sample.net.test.org\WoodEl$ Microsoft Windows
Server
PinchPa I: \\FH_beta\SYS NetWare Services
PinchPa J: \\sample02.net.inova.org\fh_nurs
PinchPa M: \\FH_MAIL\VOL1 NetWare Services
PinchPa S: \\sample02.net.test.org\fh_nursshared
PinchPa V: \\sample.net.test.org\fh_nurslwcshared
PinchPa Z: \\FH_beta\SYS\PUBLIC NetWare Services

Thanks for the long suffering and help. I'll write back the solution
when I get it.

 

[Dave Peterson]

I would think that using excel's import features would be quicker.

But you may want to look at using application.trim() against the input record.

whateverthevariableisfortheinputrecord _
= application.trim(whateverthevariableisfortheinputrecord)

It will remove any leading/trailing or multiple embedded spaces from a string.

 

[socrtwo]

I would think that using excel's import features would be quicker.

But you may want to look at using application.trim() against the input record.

whateverthevariableisfortheinputrecord _
= application.trim(whateverthevariableisfortheinputrecord)

It will remove any leading/trailing or multiple embedded spaces from a string.

Yes that solved the problem. I added

WholeLine = Application.Trim(WholeLine)

after Chip Pearson's lines

While Not EOF(1)
Line Input #1, WholeLine

It removed the extraneous spaces. I'm excited.

Also I was able to get the text insert to move to the next blank line
for each subsequest file insert, by adding:

ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

under your code of:

MsgBox myFileName & " wasn't found!"
Else
ImportTextFile myFileName, ";"

This is my first VBA script, and you got me through it. Thanks for the
help!

 

[Dave Peterson]

Glad you got it working.

Yes that solved the problem. I added

WholeLine = Application.Trim(WholeLine)

after Chip Pearson's lines

While Not EOF(1)
Line Input #1, WholeLine

It removed the extraneous spaces. I'm excited.

Also I was able to get the text insert to move to the next blank line
for each subsequest file insert, by adding:

ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

under your code of:

MsgBox myFileName & " wasn't found!"
Else
ImportTextFile myFileName, ";"

This is my first VBA script, and you got me through it. Thanks for the
help!