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(whateverthevariableisfortheinputr ecord)

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

socrtwo wrote:

socrtwo wrote:
socrtwo wrote:
Dave Peterson wrote:
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.

Dave Peterson wrote:

#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:
<<snipped
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

--

Dave Peterson

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.

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