Run the Same Macro on Multiple Files

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
Mon Jul 31 17:23:11 CDT 2006

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





"James Reid" <a8736d53@hotmail.com> wrote in message
news:1154383262.780160.286600@s13g2000cwa.googlegroups.com...
> 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!
>

zkid
Mon Jul 31 20:33:01 CDT 2006

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

"Jezebel" wrote:

> 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
>
>
>
>
>
> "James Reid" <a8736d53@hotmail.com> wrote in message
> news:1154383262.780160.286600@s13g2000cwa.googlegroups.com...
> > 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!
> >
>
>
>

James
Tue Aug 01 16:04:26 CDT 2006

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

You are very welcome, zkid.

James
Tue Aug 01 16:07:13 CDT 2006

Jezebel wrote:
> 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
Thu Aug 03 04:17:17 CDT 2006

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" <a8736d53@hotmail.com> wrote in message
news:1154466433.761763.65730@b28g2000cwb.googlegroups.com...
> Jezebel wrote:
>> 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!
>

James
Thu Aug 03 17:21:20 CDT 2006

"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 wrote:
> 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.

Jezebel
Thu Aug 03 18:14:31 CDT 2006

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


"James Reid" <a8736d53@hotmail.com> wrote in message
news:1154643680.126793.280890@b28g2000cwb.googlegroups.com...
> "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 wrote:
>> 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
Sun Aug 06 12:31:07 CDT 2006

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

I'm not talking about "needs". I'm talking about "fast".

Without mandatory prefixes the compiler or interpreter has to compare
each "word" to a list of reserved words, a list of built-in functions,
a list of user generated variables, a list of user generated functions,
etc. That takes time. Mandatory prefixes would speed things up
considerably.

Perl, which make limited use of some mandatory prefixes ($, %, @, and
&) compiles things so fast that you might not realize that it's
actually a compiler, even though it functions like a super-fast
interpreter.

Jezebel
Sun Aug 06 16:39:23 CDT 2006

If types are already declared (as they are in VB/VBA) then comparing
prefixes would be an extra step, and thus slower, than the current method.


"James Reid" <a8736d53@hotmail.com> wrote in message
news:1154885467.238689.126430@m79g2000cwm.googlegroups.com...
> Jezebel wrote:
>> I think 'requiring' prefixes would be overkill. VB/VBA determines data
>> type
>> from the declaration, which is all it needs for compilation.
>
> I'm not talking about "needs". I'm talking about "fast".
>
> Without mandatory prefixes the compiler or interpreter has to compare
> each "word" to a list of reserved words, a list of built-in functions,
> a list of user generated variables, a list of user generated functions,
> etc. That takes time. Mandatory prefixes would speed things up
> considerably.
>
> Perl, which make limited use of some mandatory prefixes ($, %, @, and
> &) compiles things so fast that you might not realize that it's
> actually a compiler, even though it functions like a super-fast
> interpreter.
>

James
Mon Aug 07 17:20:22 CDT 2006

Jezebel wrote:
> If types are already declared (as they are in VB/VBA) then comparing
> prefixes would be an extra step, and thus slower, than the current method.

Suppose all user generated variables begin with "$". Then the compiler
doesn't have to waste time searching through a bunch of other lists
when it encounters a "word" beginning with "$".

Checking for one of 4 possible prefixes would only require two
compares. Going through a big list of possible matches to a particular
"word", even if the binary search method was used, would require
several compares.

Why do you think the newer languages such as Perl & PHP use prefixes,
but the older languages don't?

James
Mon Aug 07 17:33:40 CDT 2006

James Reid wrote:
> Checking for one of 4 possible prefixes would only require two
> compares.

Actually, using the binary search method, 7 possible prefixes would
still only require 2 compares, and 3 possible prefixes would only
require one compare.