import text w/macro

[Guest]

I've tried recording a macro that will open a text file into Excel 2003. The
text file has over 256 columns, so while recording the macro i set some
columns to be "skipped" (not imported). When i clicked "finish", i got an
error message about "Too many line continuations".

The text file did open fine, but when i looked at the recorded macro in VBA,
the code was in red and was clearly cut off after a certain point.

Is there a way i can write code/record a macro to open this text file
(again, i just want to import some of the 256+ columns) that won't run into
the "Too many line continuations" problem?

Thanks
rachael

 

[Wild Bill]

Maybe import it line by line, cut the information you need, and paste
it. Here's an example:

strSourceFile = "C:\Sample.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iRowCount = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strOutText = Left(strInText, 40) & Mid(strInText,100,40) &
Right(strInText,50)
Cells(iRowCount,"A")=strOutText
iRowCount = iRowCount + 1
Wend
Close lngInputFile

 

[Myrna Larson]

I believe the limit on continuation characters is 9. Take the code and
manually remove some of the line continuations so the individual "pieces" are
longer. They'll probably run off the screen to the right, so it will be harder
to read.

You say the line has been truncated, so you'll have to figure out what has
been lost and add those fields manually. Maybe you can determine that by
looking at the properties for the fields that have been included in the code.

If that fails, you'll have to read the file line by line, splitting it with
the Split command (let's hope it's a TAB rather than a comma). You'll have to
remove any quote characters too.

If the delimiter is a comma and you have quoted text with embedded commas,
this won't work.

 

[Guest]

Of course! I actually thought of that last night as i was falling asleep (why
didn't i think of it before??).

I think removing the line continuations will be easier than importing line
by line.

Thanks!
rachael