Run the Same Macro on Multiple Files

[James Reid]

I'm proud of myself. I actually came up with a very useful Word VBA
macro without having to ask anyone how to do it:

Sub Macro2()
Dim theFileName As String
theFileName = Dir("C:\Temp\*.htm")
Do While Len(theFileName) > 0
ChangeFileOpenDirectory "C:\Temp\"
Documents.Open FileName:=theFileName
Application.Run MacroName:="Normal.NewMacros.Macro1"
ChangeFileOpenDirectory "C:\Temp2\"
ActiveDocument.SaveAs FileName:=theFileName, _
FileFormat:=wdFormatFilteredHTML
ActiveWindow.Close
theFileName = Dir
Loop
End Sub

This time-saving macro opens an .htm file in "C:\Temp", edits it by
running a macro called "Macro1", and then saves it as an .htm file by
the same name in folder "C:\Temp2". Then it automatically repeats the
procedure for all of the other .htm files in folder "C:\Temp".

I found out how to do the above for one file by recording a macro while
going through the procedure manually, but this didn't tell me how to
perform the operation on multiple files automatically. For that
information I did a search and found the following old posting on this
same amazing news-group (microsoft.public.word.vba.general):

http://groups.google.com/group/microsoft.public.word.vba.general/msg/0b1408cc4f19cd26

Hooray for Google Groups! I love you!

 

[Jezebel]

You could improve it by using a document variable, rather than relying on
the ActiveDocument and ActiveWindow --

Dim pDoc as Word.Document
:
set pDoc = Documents.Open(FileName:=...)
:
pDoc.SaveAs ...
pDoc.Close

 

[zkid]

Regardless, James. Thank you for sharing this with everyone. I'm sure it
will help someone out tremendously.

 

[James Reid]

Regardless, James. Thank you for sharing this with everyone. I'm sure it
will help someone out tremendously.

You are very welcome, zkid.

 

[James Reid]

You could improve it by using a document variable, rather than relying on
the ActiveDocument and ActiveWindow --

Dim pDoc as Word.Document
:
set pDoc = Documents.Open(FileName:=...)
:
pDoc.SaveAs ...
pDoc.Close

I like it. It makes the macro more compact, and I've made it even more
compact by deleting some unneccessary code:

Sub Macro2()
Dim fname As String
Dim myDoc As Word.Document
fname = Dir("C:\Temp\*.htm")
Do While Len(fname) > 0
ChangeFileOpenDirectory "C:\Temp\"
Set myDoc = Documents.Open(fname)
Application.Run "Macro1"
ChangeFileOpenDirectory "C:\Temp2\"
myDoc.SaveAs fname, wdFormatFilteredHTML
myDoc.Close
fname = Dir
Loop
End Sub

I changed "pDoc" (What is the meaning of the "p"?) to "myDoc", because
I saw "myDoc" used in an example in the MS Word "help" for "Document
Property".

It works! Thanks, Jezebel!

 

[Jezebel]

The p prefix is my own variable naming convention: p = procedure-level, m =
module-level, g = global. There are other convetions that also work well (eg
using the prefix to indicate data type). It seems to me that scoping errors
are the more serious issue (I've never had problems debugging type errors,
but scoping problems can be invidious); but the important thing is to choose
a convention you like, and stick to it. It will save you a *lot* of time
long-term.

 

[James Reid]

"Invidious?" Don't you mean "insidious?"

IMHO, programming languages should require prefixes, not only for
clarity, but also so as to speed up the compilation / interpretation
process. Perl & PHP do it to some extent. In JavaScript a "$" prefix is
allowed for variable names, but is seldom used.

Maybe I'll follow your example, Jezebel, and try to come up with my own
prefix standards.

 

[Jezebel]

I think 'requiring' prefixes would be overkill. VB/VBA determines data type
from the declaration, which is all it needs for compilation.