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

=}
..

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).

--
Regards,
Tom Ogilvy


"Matthew =}" wrote in message
...
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

=}
.

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

=}
-----Original Message-----
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).

--
Regards,
Tom Ogilvy


"Matthew =}" wrote
in message
...
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

=}
.




.

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.

--
Regards,
Tom Ogilvy


"Matthew =}" wrote in message
...
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

=}
-----Original Message-----
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).

--
Regards,
Tom Ogilvy


"Matthew =}" wrote
in message
...
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

=}
.




.

See:
http://www.exceltip.com/show_tip/Dat...Excel/494.html

Matthew =} wrote:

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?

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 =}
-----Original Message-----
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.

--
Regards,
Tom Ogilvy


"Matthew =}" wrote
in message
...
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

=}
-----Original Message-----
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).

--
Regards,
Tom Ogilvy


"Matthew =}"
wrote
in message
...
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

=}
.




.



.