Use XL 2 Edit a .txt File?

M

Matthew =}

Happy Monday!

{{No reply repost from Friday...with clarifications
added, 'cause I guess this is more challenging than I
first anticipated.}}

={

I receive an HTML page from the county, and I save it as
a .prn (csv text) file to preserve columnar allignment.
Recently however, they have changed their HTML editor,
and now I get misinterpreted ampersands in the file.
Consequently, when the "&" is present, the HTML editor
creates "&" from it, and kills the columnar
allignment in the .txt document for the row containing
the ampersand.

Are you with me so far?

If I open the .prn file with Notepad, I simply can Find:
"_&_" and Replace this string with "_&_", then all is
right with the world.

The problem is that this is a very small part of a large
macro that interprets the county's file, digests it,
formats it, archives it as an .xls file, and runs some
other MSO formatting macros against it to produce instant
website posting of the data. I need it to remain
automated completely, as the file size is considerable.

(How) Can I perform the Find & Replace solution on
the .prn file using MSO VB code? I tried Word, but that
jacks up the column formatting when Word opens
the 'Story', interpreted as a webpage document. I tried
Excel, but the HTML file enters Excel as entire rows of
data in single cells (the first cell in each column
contains the entire concatenated record), and that won't
do at all.

Is there a simple, non-formatting, text editor in my
Office Suite? Is there a mode or method (like Open As...
or OpenAs()) that I can apply via VB to the text within
this file (without actually opening it)? I can run an
Excel import from .csv text, but I need to manipulate the
columns first to justify them.

If you want samples, email above.

Thanks in advance, as always,

Matthew

=}
..
 
T

Tom Ogilvy

Possibly you could open it in excel and get all the data in a single column.
Then do the replace with Edit=>Replace (using the code equivalent). Then do
Data=>Text to Columns to get your data spread out into separate columns (the
code/VBA equivalent).

You could get the basic code for each operation by turning on the macro
recorder and doing it manually. The Text to columns takes you through the
Text import wizard, but it records a single command (with many options).
 
M

Matthew =}

Right on, Tom,

The macro records the With proceedure, and I can
manipulate specific lines of code after recording it.
What you suggested is my fall-back option, and I already
have started the subs for the Find/Replace and
reformatting. This proceedure creates a lengthy script
as you can imagine, so I hope my solicitation yields
something more concise.

Nonetheless, thank you very much, as any and all advice
and brainstorming here makes us think in different
directions. I may even post in the Word and Access NGs
as well.

Workin' it,

Matthew

=}
 
T

Tom Ogilvy

It should only generate about 3 commands since you are using the built in
capabilities of Excel. Apparently you have a different vision than I. But
I admit that I make some assumptions that the file can be parsed by Text to
Columns.
 
G

Guest

Righ, right,

A lot of the .With stuff appears to be of a default
(unnecessary) nature, and I can just cut all that out.
I'm gettin' there. Thanks again!

Matthew =}
 
Top