Format a file created by FSO.CreateTextFile?

[Ed from AZ]

I've got a very long file which is a string of docs that need to be
broken out and saved as individual docs. I can do this with either
Word's Document.Add and SaveAs or by using FSO.CreateTextFile.

I have to strike a balance between two issues, or find a better
solution altogether. The docs are formatted to look right in a
smaller mono-spaced font - I use Courier New, 10 pt. The files are
saved with a .doc extension and opened in Word (either 2003 or 2007).

If I create the files with Add, it takes 5-15 seconds per file, but I
can format them so they look right when opened. If I use FSO, it's
fractions of a second per file, but the file will use the Normal
default style - often Times 12 pt - when opening.

Is there a way to add font formatting to the FSO file? Or speed up
the doc saving? Or what about creating all files by FSO, then
accessing each doc in the folder and setting font characteristics?

Ed

 

[Karl E. Peterson]

I've got a very long file which is a string of docs that need to be
broken out and saved as individual docs. I can do this with either
Word's Document.Add and SaveAs or by using FSO.CreateTextFile.

I have to strike a balance between two issues, or find a better
solution altogether. The docs are formatted to look right in a
smaller mono-spaced font - I use Courier New, 10 pt. The files are
saved with a .doc extension and opened in Word (either 2003 or 2007).

If I create the files with Add, it takes 5-15 seconds per file, but I
can format them so they look right when opened. If I use FSO, it's
fractions of a second per file, but the file will use the Normal
default style - often Times 12 pt - when opening.

Is there a way to add font formatting to the FSO file? Or speed up
the doc saving? Or what about creating all files by FSO, then
accessing each doc in the folder and setting font characteristics?

The files you're creating with FSO are just pure text and nothing but text. No
formatting whatsoever. That's the very definition of "text files".

Btw, there's really no good excuse for using FSO in any language that has native
file i/o built-in. FSO is for scripting languages only, and is pathetically inept
(not to mention redundant overhead) compared to the language you're using.

 

[Ed from AZ]

Thanks for the reply, Karl.

The files you're creating with FSO are just pure text and nothing but text.  No
formatting whatsoever.  That's the very definition of "text files".

That's what I was afraid of. Which means if I want to add any kind of
formatting, I need to iterate through all the files, open, format, and
save - which gives me back my time overhead. Even if it's only 5
seconds a file, if I have to do 5,000 files, that's almost 7 hours!!

Btw, there's really no good excuse for using FSO in any language that hasnative
file i/o built-in.  FSO is for scripting languages only, and is pathetically inept
(not to mention redundant overhead) compared to the language you're using..

So in VBA, what's the preferred method? Everything I read in Help
points me back to FSO.

Ed

 

[Karl E. Peterson]

Thanks for the reply, Karl.


That's what I was afraid of. Which means if I want to add any kind of
formatting, I need to iterate through all the files, open, format, and
save - which gives me back my time overhead.

I'm not sure how to say this without it sounding bad. There is *no* formatting in
text files. If you open, format, and save a text file, you still have a text file
which has *no* formatting.

Even if it's only 5
seconds a file, if I have to do 5,000 files, that's almost 7 hours!!

If you have code that can format those text files, why bother at all? Why not do
the formatting when someone actually takes the time to look at them? I don't think
I'd mind a 5 second delay waiting for a file to open in Word. I'd figure something
else was happening, or whatever. Stuff happens, huh?

I think I'm missing some piece of this puzzle. If you want formatting, you'll need
to apply formatting at some point, right? I don't see the lost time.

So in VBA, what's the preferred method? Everything I read in Help
points me back to FSO.

In VB(A), text files are best handled with native file i/o. Especially if speed is
of the essence. You can even take it a step further and use the Windows API to
squeeze the last little bit out, but that's generally overkill for simple i/o. To
give you an idea of the range of difference, here's a comparison of file search
algorithms:

http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm

The real time killer is overdoing the i/o, generally. If you can build a large
string *fast* and output it all at once, that's optimum. I have a StringBuilder
class on my site (http://vb.mvps.org/samples/StrBldr) that's about as optimized as
that can get.

Anyway, here's all it takes to output a textfile:

Public Function WriteFile(ByVal FileName As String, ByVal Text As String) As
Boolean
Dim hFile As Long
On Error GoTo Hell
hFile = FreeFile
Open FileName For Output As #hFile
Print #hFile, Text;
Close #hFile
Hell:
WriteFile = Not CBool(Err.Number)
End Function

One thing that doesn't get mentioned in the helpfile is that IT folks are afraid of
things like FSO, and often disable it on the machines under their deathgrip.
Relying on native techniques, and avoiding external dependencies, is always the more
robust option to choose when it's available.

 

[Ed from AZ]

I think I'm missing some piece of this puzzle. If you want formatting, you'll need

to apply formatting at some point, right? I don't see the lost time.

Sorry 'bout that, Karl. I've got a macro code that allows me to
navigate to a company web site and collect the contents of all needed
reports into a big string. I paste this string into a Word doc, then
use ranges to split the big file into individual files. I also output
an Excel file list of all these reports. I want to be able to open
the appropriate file from a hyperlink in the Excel file - no macros
allowed here, otherwise I'd use a Word.Doc.Open code and apply
formatting then.

Since I can't do that, I need to make sure the fiiles, all saved with
a ".doc" extension even though they may not be "real" Word files, have
the correct format when opened. So I either create Word docs as I
break up the big file into smaller ones and eat the time overhead but
gain the formatting, or I guess I write a second procedure to go back
and open all files in Word, apply formatting, and SaveAs a real Word
file. Again, the time thing.

I might gain some time by splitting the big string while it's still a
string, and not paste it into Word and use ranges, but that might be a
bit trickier - for me at least!

Ed

 

[Jay Freedman]

I've got a very long file which is a string of docs that need to be
broken out and saved as individual docs. I can do this with either
Word's Document.Add and SaveAs or by using FSO.CreateTextFile.

I have to strike a balance between two issues, or find a better
solution altogether. The docs are formatted to look right in a
smaller mono-spaced font - I use Courier New, 10 pt. The files are
saved with a .doc extension and opened in Word (either 2003 or 2007).

If I create the files with Add, it takes 5-15 seconds per file, but I
can format them so they look right when opened. If I use FSO, it's
fractions of a second per file, but the file will use the Normal
default style - often Times 12 pt - when opening.

Is there a way to add font formatting to the FSO file? Or speed up
the doc saving? Or what about creating all files by FSO, then
accessing each doc in the folder and setting font characteristics?

Ed

Hi Ed,

I agree with Karl about the best way of saving long strings. But the formatting
issue may be easier than you think.

By default, when you open a text file in Word, the style named Plain Text is
automatically applied to it. I think the definition of that style in all
versions up to Word 2003 is Courier New 10 pt. Of course, Word 2007 has to be a
PITA, so it defines the Plain Text style as 10.5 pt, but you can modify it.

By the way, save yourself some grief and assign the text files the .txt
extension instead of .doc.

 

[Karl E. Peterson]

Sorry 'bout that, Karl. I've got a macro code that allows me to
navigate to a company web site and collect the contents of all needed
reports into a big string. I paste this string into a Word doc, then
use ranges to split the big file into individual files. I also output
an Excel file list of all these reports. I want to be able to open
the appropriate file from a hyperlink in the Excel file - no macros
allowed here, otherwise I'd use a Word.Doc.Open code and apply
formatting then.

Since I can't do that, I need to make sure the fiiles, all saved with
a ".doc" extension even though they may not be "real" Word files, have
the correct format when opened. So I either create Word docs as I
break up the big file into smaller ones and eat the time overhead but
gain the formatting, or I guess I write a second procedure to go back
and open all files in Word, apply formatting, and SaveAs a real Word
file. Again, the time thing.

Ahhhh. Hmmmm, can a hyperlink in Excel be set to pass a parameter to a program?
Just trying to think of some way out of that dilemma. Yes, it appears they can.
What if you created a hyperlink that passed the document name to a program that
would import and format the file in Word? I know that's getting a little bit crazy,
but...? (Only question then would be if there's some way to turn off that *inane*
warning about linking to an EXE?)

 

[Karl E. Peterson]

By default, when you open a text file in Word, the style named Plain Text is
automatically applied to it. I think the definition of that style in all
versions up to Word 2003 is Courier New 10 pt. Of course, Word 2007 has to be a
PITA, so it defines the Plain Text style as 10.5 pt, but you can modify it.

D'oh! I was really wondering why he was seeing something funny like that. If I
dragged a TXT file into Word, I got a nice clean Courier here. I didn't even think
about 2007 horsing something *that* fundamental up, too! It's appearing I'm stuck
(for life?) at Office 2003 here.

I think I also glazed over his desire to just make it look like an ordinary text
file. I figured by "formatting" he was meaning headings and such. Yeah, I agree,
this oughta be far easier than I was implying might be the case.

By the way, save yourself some grief and assign the text files the .txt
extension instead of .doc.

Agreed! But then, if he links to them, they'll open in Notepad (or whatever) rather
than Word. Probably better, but maybe not?

 

[Jay Freedman]

D'oh! I was really wondering why he was seeing something funny like that. If I
dragged a TXT file into Word, I got a nice clean Courier here. I didn't even think
about 2007 horsing something *that* fundamental up, too! It's appearing I'm stuck
(for life?) at Office 2003 here.

I think I also glazed over his desire to just make it look like an ordinary text
file. I figured by "formatting" he was meaning headings and such. Yeah, I agree,
this oughta be far easier than I was implying might be the case.


Agreed! But then, if he links to them, they'll open in Notepad (or whatever) rather
than Word. Probably better, but maybe not?

Yeah, I wrote that before I saw the part about hyperlinking from Excel. That
isn't going to work with .txt extensions. But I did try renaming a .txt file
with a .doc extension, and Word still applied Plain Text style to it -- I think
it does that whenever the file doesn't have any style information stored in it.

 

[Karl E. Peterson]

Yeah, I wrote that before I saw the part about hyperlinking from Excel. That
isn't going to work with .txt extensions. But I did try renaming a .txt file
with a .doc extension, and Word still applied Plain Text style to it -- I think
it does that whenever the file doesn't have any style information stored in it.

Yeah, damned thing is, I even tried the same test back around my first or second
reply to the thread. You nailed the answer, if he can redefine that style for
whoever's using his macros. Otherwise, I'm afraid the "best answer" is to upgrade
to Office 2003, eh? ;-)

 

[Ed from AZ]

Karl and Jay, thanks for all your help.

I incorporated Karl's FreeFile function and re-ran the macro, saving
the files out as .txt. It did run much faster - about 250 files
finished in less than a minute.

Unfortunately, I have the formatting issues. These are reports
generated by a database and retrieved through a web interface by
getting the content of the web page:
strMsg = docIE.Body.InnerText

So I've already lost any formatting in the jumble of translations
here. Also, each line is supposed to be a certain number of
characters long, ending in a hard break (in Word, it's Chr(13)). If I
have the breaks and the monospaced font, it all looks great.

But when I opened the txt file from a hyperlink, it opened in Notepad
- and the breaks were not there, so every line ran into the next one.
If I changed the extension to doc, the hyperlink opened in Word, but
gave me the Convert File dialogue. Plain text did not have the line
breaks, but had the monospaced font; Recover had the breaks, but Times
New Roman font.

The ultimate aim is to write these files and the hyperlinked Excel
file onto a CD, so I will have no guarentee what the customer will
have in the way of styles or knowledge. So I need to make sure these
open correctly, even if it means running it overnight in Word. (Then
I can work on creating hyperlinks that will find the file on a CD!!
Grrr!!)

Ed

 

[Peter Jamieson]

You could consider outputting your text in RTF format - it's "plain
text" in the sense that you can emit it quite easily using fso, VBA
native file stuff, and doubtless Karl's function. Trouble is, you have
to work out exactly what RTF "wrapping" to put round the entire file,
and probably each paragraph/line, and you will probably also have to
ensure that each line in the RTF file is no longer than (say) 80 characters.

If the target word processors are only WOrd 2003 and Word 2007, you
could also consider using Word 2003 .xml format, but I'm not sure /all/
versions of Word 2003 can read that.

I can't provide detailed instructions about the RTF coding you'd need
without working through it myself, but what I tend to do is
a. use the earliest version of Word I can lay my hands on to save a
file (you get simpler RTF that way, and it's much harder to make sense
of the newer versions). You might also be able to use WordPad to emit
"simpler" RTF
b. work out what I can strip out from the RTF header area
c. work out what I need to do "per paragraph" or "per line"
d. create a stripped down .rtf file and verify that it will open in
(say) Word 2003/2007 with the appearance I want and without causing
problems in Word.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Karl E. Peterson]

Karl and Jay, thanks for all your help.

I incorporated Karl's FreeFile function and re-ran the macro, saving
the files out as .txt. It did run much faster - about 250 files
finished in less than a minute.
Great.

Unfortunately, I have the formatting issues. These are reports
generated by a database and retrieved through a web interface by
getting the content of the web page:
strMsg = docIE.Body.InnerText

So I've already lost any formatting in the jumble of translations
here. Also, each line is supposed to be a certain number of
characters long, ending in a hard break (in Word, it's Chr(13)). If I
have the breaks and the monospaced font, it all looks great.

But when I opened the txt file from a hyperlink, it opened in Notepad
- and the breaks were not there, so every line ran into the next one.

Before outputing, replace every carriage return with a cr/lf pair.

Call WriteFile(FileName, Replace(FileText, vbCr, vbCrLf)

The ultimate aim is to write these files and the hyperlinked Excel
file onto a CD, so I will have no guarentee what the customer will
have in the way of styles or knowledge. So I need to make sure these
open correctly, even if it means running it overnight in Word. (Then
I can work on creating hyperlinks that will find the file on a CD!!
Grrr!!)

Then you'll almost certainly want to keep them as TXT files which will be associated
with whatever the default text editor on your users machine will be. On mine, it'd
be Textpad, for instance. At any rate, you gotta make them *proper* text files,
which means a 13/10 pair rather than a simple 13 at each EOL.

 

[Ed from AZ]

Hi. Peter. Thanks for your input.

and you will probably also have to
ensure that each line in the RTF file is no longer than (say) 80 characters.

I'm not at all sure about RTFs, but you did get me to thinking about
perhaps another approach.

Each line of these reports is supposed to be exactly 80 characters,
which includes the line break. What if I just:
-- read the big "master" 80 characters at a time,
-- trim off the 80th character (the line break that doesn't come
through)
-- replace it with a vbCrLf or Chr(13) or whatever a text file will
recognize
-- then rinse and repeat until I hit the end of the report
-- write that into the file
-- continue until all done

I could either use WriteLine (but isn't that FSO?), or put it all into
a string and write it out at once. That looks a whole lot like what
I'm doing now, except I would be sure that my line break character is
correct (hopefully!).

Ed

 

[Karl E. Peterson]

Each line of these reports is supposed to be exactly 80 characters,
which includes the line break. What if I just:
-- read the big "master" 80 characters at a time,
-- trim off the 80th character (the line break that doesn't come
through)
-- replace it with a vbCrLf or Chr(13) or whatever a text file will
recognize
-- then rinse and repeat until I hit the end of the report
-- write that into the file
-- continue until all done

Have you actually looked at your output in a hex editor? I think it's time you did
so, if you're at all unsure about what's there. If you know it's a 13, replace it
with a 13/10. Problem solved. One function call.

 

[Ed from AZ]

Have you actually looked at your output in a hex editor?  I think it's time you did

so, if you're at all unsure about what's there.  If you know it's a 13,replace it
with a 13/10.  Problem solved.  One function call.

No - I copy from the wweb page and paste directly into Word. If I
highlight the return at the end of the line and use ASC, I get 13. I
actually have a line in the code to replace all Chr(13) with "^p".
Maybe I'll try changing that to ReplaceWith:=Chr(13) & Chr(10) and see
if it helps.

I don't know what a hex editor would be. Is there one native to XP
Pro or Office 2007?

Ed

 

[Ed from AZ]

No - I copy from the wweb page and paste directly into Word.  If I
highlight the return at the end of the line and use ASC, I get 13.  I
actually have a line in the code to replace all Chr(13) with "^p".
Maybe I'll try changing that to ReplaceWith:=Chr(13) & Chr(10) and see
if it helps.

Okay - here's what I tried:

First, after pasting the clipboard into the Word doc, I always run:
With docRng.Find
.ClearFormatting
.Execute FindText:="^13", ReplaceWith:="^p", Replace:=wdReplaceAll
End With

I changed that to:
With docRng.Find
.ClearFormatting
.Execute FindText:="^13", ReplaceWith:=vbCrLf, Replace:=wdReplaceAll
End With

My FreeFile text file had the correct line breaks! Yay!
But -- to split all the files out of the big "master" file, the code
that comes after the lines above uses:
With parRng.Find
If .Execute(FindText:="common text near end of file") = True Then
parRng.MoveEnd Unit:=wdParagraph, Count:=7
myRng.SetRange Start:=docRng.Start, End:=parRng.End

Apparently, VBA does not recognize a vbCrLf as a valid wdParagraph,
because I wound up with one long text file - nothing got split up!

Next:
I went back to
..Execute FindText:="^13", ReplaceWith:="^p", Replace:=wdReplaceAll

I added the Replace function to work on the string for one split-out
report:
myText = myRng.Text
myText = Replace(myText, Chr(13), vbCrLf, 1, , vbBinaryCompare)

This time everything split up just fine - but I did not have my pretty
line breaks.
I changed it to
myText = myRng.Text
myText = Replace(myText, Chr(13), Chr(13) & Chr(10), 1, ,
vbBinaryCompare)
but still no joy!

Next, I changed the .Execute to
.Execute FindText:="^13", ReplaceWith:="^13^10",
Replace:=wdReplaceAll
and changed the way I found the end of my report by using
parRng.MoveEndUntil Cset:=Chr(13) seven times.

I got my line breaks - but no splits. *sigh*

Ed

 

[Ed from AZ]

I got my line breaks - but no splits. *sigh*

Found the problem.

Apparently, when I do
With docRng.Find
.ClearFormatting
.Execute FindText:="^13", ReplaceWith:="^13^10",
Replace:=wdReplaceAll
End With
Word VBA no longer "sees" paragraphs!!

So when my code hit
If docRng.Paragraphs.Count < 15 Then Exit Do
it saw only one paragraph and quit - just like I told it to!

But since the loop starts with
With parRng.Find
If .Execute(FindText:="common text near end of file") = True Then
and the Else is Exit Do, I'm okay. It all runs okay, splitting files
and giving me line breaks!
*whew!*

Thanks, Karl, Jay, and Peter, for all your help.
Ed

Now to tackle the hyperlinks . . .

 

[Karl E. Peterson]

Found the problem.

Apparently, when I do
With docRng.Find
.ClearFormatting
.Execute FindText:="^13", ReplaceWith:="^13^10",
Replace:=wdReplaceAll
End With
Word VBA no longer "sees" paragraphs!!

Maybe you need to quit using Word for this task? <g>

If you grab a String, just do the Replace on that, and write it to disk.

No? I think you're making this /way/ harder than it needs to be.

 

[Ed from AZ]

Maybe you need to quit using Word for this task? <g>

Oh, please Mr. Wizard!! Point me to something better!! Then again, I
don't know if I could use it - this is for work and I'm stuck with
what I have: Office and VBA, and VB6 (I can probably find someone with
dot Fred). Then, too, I'm also stuck with what I know - which
obviously isn't much!!

If you grab a String, just do the Replace on that, and write it to disk.

Well, I did try
myText = Replace(myText, Chr(13), Chr(13) & Chr(10), 1, ,
vbBinaryCompare)
but it didn't seem to work. Bad syntax? Bad breath?

No?  I think you're making this /way/ harder than it needs to be.

It sure seems harder than I thought it would be! But when you're
using a screwdriver to pound nails, it's _all_ hard! <G>

Ed