execute macro for all documents in the folder

I have the macro which I want to execute for all files in the folder - open
each file, execute macro, close the file. Can I do it without creating the
table with file names ? If yes, how ?

Thanks for help.

Tony

Anne
Thu Oct 27 23:06:57 CDT 2005

Tony, this should help you: http://vbaexpress.com/kb/getarticle.php?kb_id=76
************
Anne Troy
www.OfficeArticles.com

"Tony" <Tony@discussions.microsoft.com> wrote in message
news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
>I have the macro which I want to execute for all files in the folder - open
> each file, execute macro, close the file. Can I do it without creating the
> table with file names ? If yes, how ?
>
> Thanks for help.
>
> Tony

Tony
Thu Oct 27 23:16:02 CDT 2005

Hi Anne,

Thank you very much for help. Works perfect.

Tony

"Anne Troy" wrote:

> Tony, this should help you: http://vbaexpress.com/kb/getarticle.php?kb_id=76
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Tony" <Tony@discussions.microsoft.com> wrote in message
> news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
> >I have the macro which I want to execute for all files in the folder - open
> > each file, execute macro, close the file. Can I do it without creating the
> > table with file names ? If yes, how ?
> >
> > Thanks for help.
> >
> > Tony
>
>
>

Anne
Thu Oct 27 23:24:11 CDT 2005

Cool!
************
Anne Troy
www.OfficeArticles.com

"Tony" <Tony@discussions.microsoft.com> wrote in message
news:8A645431-688F-4402-B979-59EF8426B32F@microsoft.com...
> Hi Anne,
>
> Thank you very much for help. Works perfect.
>
> Tony
>
> "Anne Troy" wrote:
>
>> Tony, this should help you:
>> http://vbaexpress.com/kb/getarticle.php?kb_id=76
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "Tony" <Tony@discussions.microsoft.com> wrote in message
>> news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
>> >I have the macro which I want to execute for all files in the folder -
>> >open
>> > each file, execute macro, close the file. Can I do it without creating
>> > the
>> > table with file names ? If yes, how ?
>> >
>> > Thanks for help.
>> >
>> > Tony
>>
>>
>>

Charles
Fri Oct 28 12:28:25 CDT 2005

http://word.mvps.org/FAQs/MacrosVBA/BatchFR.htm
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

"Tony" <Tony@discussions.microsoft.com> wrote in message
news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
>I have the macro which I want to execute for all files in the folder - open
> each file, execute macro, close the file. Can I do it without creating the
> table with file names ? If yes, how ?
>
> Thanks for help.
>
> Tony

rhamrecitationcom
Mon Oct 31 10:42:07 CST 2005

I'm really leary about posting my code up, but i'm going to cause i think my
macro is better than what they have. I give anyone permission to alter the
code i provide. I'm also embarressed if there are errors in it, as i know
there are a few things that are beyond my programming expertise, as i'm am
self-taught and don't have any VB experience and am learning this on my own.

If you have any suggestions, please give them, i'm very willing and ready to
learn everything i can about how to be a better programmer with this language.

What the program does-

1. Asks you if you want to save the files in FilteredHTML before you run a
macro on them.

2. Asks how many folders you want to run a macro on.

3. Allows input of the paths of all the folders to run the macro on.

4. Asks how many macros you want to run on each folder you input.

5. Asks for the name(s) of the macro(s) you want to run.

6. Asks if you want to save them in FilteredHTML when the macro(s) are done.

And now the code-

Sub MacroRunner()

'this macro will allow for the
'running of a macro on a whole folder
'or, many folders. If you want you can
'save the initial file as FilteredHTML
'so when the macro is actually run
'it will run on a filtered HTML file

Application.ScreenUpdating = False
Dim counter, y, z As Integer
Dim fPath, fiType, path, fLog, mLog, f, d, tLog, flist, _
convert, MtoRun, MtoRunName, fFilename, mFilename, _
nmacros, nfolders As String
Dim doc As Document

Set f = CreateObject("Scripting.FileSystemObject")
fFilename = Environ$("APPDATA") & "\~folders" & ".txt"
mFilename = Environ$("APPDATA") & "\~macros" & ".txt"
Set tLog = f.Createtextfile(fFilename, True)
Set mLog = f.Createtextfile(mFilename, True)

convert = MsgBox(prompt:="Do you want convert everything to Filtered
HTML?", _
buttons:=vbYesNo)

Application.ScreenRefresh

fiType = MsgBox(prompt:="Do you want to save completed files in Filtered
HTML?", _
buttons:=vbYesNo)

Application.ScreenRefresh

nfolders = InputBox(prompt:="How many folders do you want to run macro
on?")

If Len(nfolders) = 0 Then
MsgBox ("You hit cancel or didn't put anything in.")
Exit Sub
End If

Application.ScreenRefresh

For y = 1 To nfolders

If nfolders = 1 Then
path = InputBox(prompt:="Input the folder's path.")
ElseIf y = nfolders Then
path = InputBox(prompt:="Input the last folder's path.")
ElseIf y = 1 Then
path = InputBox(prompt:="Input the 1st folder's path.")
ElseIf y = 2 Then
path = InputBox(prompt:="Input the 2nd folder's path.")
ElseIf y = 3 Then
path = InputBox(prompt:="Input the 3rd folder's path.")
Else
path = InputBox(prompt:="Input the " & y & "th folder's path.")
End If

If Len(path) = 0 Then
MsgBox ("You hit cancel or didn't put anything in.")
Exit Sub
End If

tLog.writeline (path)

Application.ScreenRefresh

Next y

nmacros = InputBox(prompt:="How many macros do you want to run?")

If Len(nfolders) = 0 Then
MsgBox ("You hit cancel or didn't put anything in.")
Exit Sub
End If

Application.ScreenRefresh

For y = 1 To nmacros

If nmacros = 1 Then
MtoRun = InputBox(prompt:="Input the macro's name you want to
run on all of the files.")
ElseIf y = nmacros Then
MtoRun = InputBox(prompt:="Input the last macro's name you want
to run on all of the files.")
ElseIf y = 1 Then
MtoRun = InputBox(prompt:="Input the 1st macro's name you want
to run on all of the files.")
ElseIf y = 2 Then
MtoRun = InputBox(prompt:="Input the 2nd macro's name you want
to run on all of the files.")
ElseIf y = 3 Then
MtoRun = InputBox(prompt:="Input the 3rd macro's name you want
to run on all of the files.")
Else
MtoRun = InputBox(prompt:="Input the " & y & "th macro's name
you want to run on all of the files.")
End If

If Len(MtoRun) = 0 Then
MsgBox ("You hit cancel or didn't put anything in.")
Exit Sub
End If

mLog.writeline (MtoRun)

Application.ScreenRefresh

Next y

tLog = (fFilename)
mLog = (mFilename)

Open tLog For Input As #1

Do While Not EOF(1)

Line Input #1, fPath

If convert = vbYes Then
With Application.FileSearch

.NewSearch
.LookIn = fPath
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles

If Not .Execute() = 0 Then
For counter = 1 To .FoundFiles.Count
Set doc = Documents.Open(.FoundFiles(counter))
ActiveDocument.SaveAs
FileFormat:=wdFormatFilteredHTML
ActiveDocument.Close
Set doc = Nothing
Next counter
Else
MsgBox "No Word Documents in specified folder(s)"
End If
End With
End If

With Application.FileSearch

.NewSearch
.LookIn = fPath
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles

If Not .Execute() = 0 Then
For counter = 1 To .FoundFiles.Count
Open mLog For Input As #2
Set doc = Documents.Open(.FoundFiles(counter))
Do While Not EOF(2)
Line Input #2, MtoRunName
Application.Run MtoRunName
Loop
If fiType = vbYes Then
ActiveDocument.SaveAs
FileFormat:=wdFormatFilteredHTML
Else
ActiveDocument.Save
End If
ActiveDocument.Close
Set doc = Nothing
Close #2
Next counter
Else
MsgBox "No files in specified folder(s)"
End If
End With
Loop

Close #1

f.deletefile mFilename
f.deletefile fFilename

Application.ScreenUpdating = True
MsgBox "Finally... Done"

End Sub

"Tony" wrote:

> I have the macro which I want to execute for all files in the folder - open
> each file, execute macro, close the file. Can I do it without creating the
> table with file names ? If yes, how ?
>
> Thanks for help.
>
> Tony

Greg
Mon Oct 31 19:30:36 CST 2005

I think you code is pretty neat. The Filter HTML stuff doesn't appear
to be applicable to Word2000, so I took it out. I also worked with
your method to add folders and macros:

Sub MacroRunner()

Dim fFolderName As String, mFileName As String, oFolderPath As String,
_
MtoRun As String, MtoRunName As String
Dim f
Dim fLog
Dim mLog
Dim i As Long
Dim oDoc As Document

Set f = CreateObject("Scripting.FileSystemObject")
fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
Set fLog = f.Createtextfile(fFolderName, True)
Set mLog = f.Createtextfile(mFileName, True)

Application.ScreenUpdating = False

Do
oFolderPath = InputBox("Enter the compelete path of the folder
containing " _
& "the files you want to process (e.g, C:\My Documents\Batch
Folder)", _
"Folder Path")
If Len(oFolderPath) = 0 Then
MsgBox ("Nothing entered. Exiting routine.")
Exit Sub
Else
fLog.writeline (oFolderPath)
End If
Loop While MsgBox("Do you want to process an additional folder?",
vbYesNo + vbQuestion, _
"More Folders?") = vbYes

Do
MtoRun = InputBox("Enter the name of the macro that you want to run.",
"Macro Name")
If Len(MtoRun) = 0 Then
MsgBox ("Nothing entered. Exiting routine.")
Exit Sub
Else
mLog.writeline (MtoRun)
End If
Loop While MsgBox("Do you want to run an additional macro?", vbYesNo +
vbQuestion, _
"More macros?") = vbYes

fLog = (fFolderName)
mLog = (mFileName)
Open fLog For Input As #1
Do While Not EOF(1)
Line Input #1, oFolderPath
With Application.FileSearch
.NewSearch
.LookIn = oFolderPath
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles
If Not .Execute() = 0 Then
For i = 1 To .FoundFiles.Count
Open mLog For Input As #2
Set oDoc = Documents.Open(.FoundFiles(i))
Do While Not EOF(2)
Line Input #2, MtoRunName
Application.Run MtoRunName
Loop
ActiveDocument.Save
ActiveDocument.Close
Set oDoc = Nothing
Close #2
Next i
Else
MsgBox "No files in specified folder(s)"
End If
End With
Loop
Close #1
f.deletefile mFileName
f.deletefile fFolderName
Application.ScreenUpdating = True
MsgBox "Finally... Done"
End Sub
Sub Test()
MsgBox ActiveDocument.ComputeStatistics(wdStatisticPages)
End Sub

rhamrecitationcom
Tue Nov 01 10:54:06 CST 2005

I'm glad you liked it, the yes/no message box at the end of inputting path
and macro is a good idea. I have to say though, i'm a little partial to
stating how many folders and how many macro's prior, just because it's less
program interaction, and i usually know how many folders and how many macros
before i even run the program.

It's too bad the FilteredHTML stuff doesnt work with 2000. In 2003 you can
save things as FilteredHTML and it gets rid of the MS Word Specific
Programming in files. This allows windows lists to become text space lists,
and you don't have to deal with a bunch of other things as well.

Also, with the FilteredHTML files, they can be editted in Frontpage or even
notepad for that matter, letting you format your document by the tags inside
the file.

All in all, i'm glad you liked the program, and i hope it helps you or
anyone else for that matter.

"Greg" wrote:

> I think you code is pretty neat. The Filter HTML stuff doesn't appear
> to be applicable to Word2000, so I took it out. I also worked with
> your method to add folders and macros:
>
> Sub MacroRunner()
>
> Dim fFolderName As String, mFileName As String, oFolderPath As String,
> _
> MtoRun As String, MtoRunName As String
> Dim f
> Dim fLog
> Dim mLog
> Dim i As Long
> Dim oDoc As Document
>
> Set f = CreateObject("Scripting.FileSystemObject")
> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
> Set fLog = f.Createtextfile(fFolderName, True)
> Set mLog = f.Createtextfile(mFileName, True)
>
> Application.ScreenUpdating = False
>
> Do
> oFolderPath = InputBox("Enter the compelete path of the folder
> containing " _
> & "the files you want to process (e.g, C:\My Documents\Batch
> Folder)", _
> "Folder Path")
> If Len(oFolderPath) = 0 Then
> MsgBox ("Nothing entered. Exiting routine.")
> Exit Sub
> Else
> fLog.writeline (oFolderPath)
> End If
> Loop While MsgBox("Do you want to process an additional folder?",
> vbYesNo + vbQuestion, _
> "More Folders?") = vbYes
>
> Do
> MtoRun = InputBox("Enter the name of the macro that you want to run.",
> "Macro Name")
> If Len(MtoRun) = 0 Then
> MsgBox ("Nothing entered. Exiting routine.")
> Exit Sub
> Else
> mLog.writeline (MtoRun)
> End If
> Loop While MsgBox("Do you want to run an additional macro?", vbYesNo +
> vbQuestion, _
> "More macros?") = vbYes
>
> fLog = (fFolderName)
> mLog = (mFileName)
> Open fLog For Input As #1
> Do While Not EOF(1)
> Line Input #1, oFolderPath
> With Application.FileSearch
> .NewSearch
> .LookIn = oFolderPath
> .SearchSubFolders = False
> .FileType = msoFileTypeAllFiles
> If Not .Execute() = 0 Then
> For i = 1 To .FoundFiles.Count
> Open mLog For Input As #2
> Set oDoc = Documents.Open(.FoundFiles(i))
> Do While Not EOF(2)
> Line Input #2, MtoRunName
> Application.Run MtoRunName
> Loop
> ActiveDocument.Save
> ActiveDocument.Close
> Set oDoc = Nothing
> Close #2
> Next i
> Else
> MsgBox "No files in specified folder(s)"
> End If
> End With
> Loop
> Close #1
> f.deletefile mFileName
> f.deletefile fFolderName
> Application.ScreenUpdating = True
> MsgBox "Finally... Done"
> End Sub
> Sub Test()
> MsgBox ActiveDocument.ComputeStatistics(wdStatisticPages)
> End Sub
>
>

Greg
Tue Nov 01 20:56:37 CST 2005

Instead of typing in the folder paths, you migh like using the filecopy
dialog.

Do
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
oFolderPath = .Directory
fLog.writeline (oFolderPath)
Else
MsgBox "Cancelled by User"
Exit Sub
End If
End With
Loop While MsgBox("Do you want to process an additional folder?", vbYesNo +
vbQuestion, _
"More Folders?") = vbYes

Maybe you could take a minute and explain how you developed this little
snipet:

Set f = CreateObject("Scripting.FileSystemObject")
fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
Set fLog = f.Createtextfile(fFolderName, True)
Set mLog = f.Createtextfile(mFileName, True)

Snooping around in the VBA help I can find no reference to
Scripting.FileSystemObject or APPDATA. How did you discovery/stumble upon
them?



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

rhamre@citation.com wrote:
> I'm glad you liked it, the yes/no message box at the end of inputting
> path and macro is a good idea. I have to say though, i'm a little
> partial to stating how many folders and how many macro's prior, just
> because it's less program interaction, and i usually know how many
> folders and how many macros before i even run the program.
>
> It's too bad the FilteredHTML stuff doesnt work with 2000. In 2003
> you can save things as FilteredHTML and it gets rid of the MS Word
> Specific Programming in files. This allows windows lists to become
> text space lists, and you don't have to deal with a bunch of other
> things as well.
>
> Also, with the FilteredHTML files, they can be editted in Frontpage
> or even notepad for that matter, letting you format your document by
> the tags inside the file.
>
> All in all, i'm glad you liked the program, and i hope it helps you or
> anyone else for that matter.
>
> "Greg" wrote:
>
>> I think you code is pretty neat. The Filter HTML stuff doesn't
>> appear
>> to be applicable to Word2000, so I took it out. I also worked with
>> your method to add folders and macros:
>>
>> Sub MacroRunner()
>>
>> Dim fFolderName As String, mFileName As String, oFolderPath As
>> String, _
>> MtoRun As String, MtoRunName As String
>> Dim f
>> Dim fLog
>> Dim mLog
>> Dim i As Long
>> Dim oDoc As Document
>>
>> Set f = CreateObject("Scripting.FileSystemObject")
>> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
>> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
>> Set fLog = f.Createtextfile(fFolderName, True)
>> Set mLog = f.Createtextfile(mFileName, True)
>>
>> Application.ScreenUpdating = False
>>
>> Do
>> oFolderPath = InputBox("Enter the compelete path of the folder
>> containing " _
>> & "the files you want to process (e.g, C:\My Documents\Batch
>> Folder)", _
>> "Folder Path")
>> If Len(oFolderPath) = 0 Then
>> MsgBox ("Nothing entered. Exiting routine.")
>> Exit Sub
>> Else
>> fLog.writeline (oFolderPath)
>> End If
>> Loop While MsgBox("Do you want to process an additional folder?",
>> vbYesNo + vbQuestion, _
>> "More Folders?") = vbYes
>>
>> Do
>> MtoRun = InputBox("Enter the name of the macro that you want to
>> run.", "Macro Name")
>> If Len(MtoRun) = 0 Then
>> MsgBox ("Nothing entered. Exiting routine.")
>> Exit Sub
>> Else
>> mLog.writeline (MtoRun)
>> End If
>> Loop While MsgBox("Do you want to run an additional macro?", vbYesNo
>> + vbQuestion, _
>> "More macros?") = vbYes
>>
>> fLog = (fFolderName)
>> mLog = (mFileName)
>> Open fLog For Input As #1
>> Do While Not EOF(1)
>> Line Input #1, oFolderPath
>> With Application.FileSearch
>> .NewSearch
>> .LookIn = oFolderPath
>> .SearchSubFolders = False
>> .FileType = msoFileTypeAllFiles
>> If Not .Execute() = 0 Then
>> For i = 1 To .FoundFiles.Count
>> Open mLog For Input As #2
>> Set oDoc = Documents.Open(.FoundFiles(i))
>> Do While Not EOF(2)
>> Line Input #2, MtoRunName
>> Application.Run MtoRunName
>> Loop
>> ActiveDocument.Save
>> ActiveDocument.Close
>> Set oDoc = Nothing
>> Close #2
>> Next i
>> Else
>> MsgBox "No files in specified folder(s)"
>> End If
>> End With
>> Loop
>> Close #1
>> f.deletefile mFileName
>> f.deletefile fFolderName
>> Application.ScreenUpdating = True
>> MsgBox "Finally... Done"
>> End Sub
>> Sub Test()
>> MsgBox ActiveDocument.ComputeStatistics(wdStatisticPages)
>> End Sub

rhamrecitationcom
Wed Nov 02 08:50:05 CST 2005

Oh, i like that! that will totally work.

About the-

> Set f = CreateObject("Scripting.FileSystemObject")
> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
> Set fLog = f.Createtextfile(fFolderName, True)
> Set mLog = f.Createtextfile(mFileName, True)

As far as i know this is just normal VB, not specifically VBA.

This method was tought to me buy a programmer i work with to create a log
file. I've been using his method to create log files and files to store data
and then pull it back into macro's. It's really a nifty little nippet, and
i've been using it for varius things ever since i learned it.

I hope you're able to put it to good use. I don't know of any other way to
do these things as i don't know how to program quite well yet.

Enjoy the program, thanks for the filecopy code, i'll tear it apart, figure
it out, and rebuild it. then see what i can do with it. ;-)

"Greg Maxey" wrote:

> Instead of typing in the folder paths, you migh like using the filecopy
> dialog.
>
> Do
> With Dialogs(wdDialogCopyFile)
> If .Display <> 0 Then
> oFolderPath = .Directory
> fLog.writeline (oFolderPath)
> Else
> MsgBox "Cancelled by User"
> Exit Sub
> End If
> End With
> Loop While MsgBox("Do you want to process an additional folder?", vbYesNo +
> vbQuestion, _
> "More Folders?") = vbYes
>
> Maybe you could take a minute and explain how you developed this little
> snipet:
>
> Set f = CreateObject("Scripting.FileSystemObject")
> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
> Set fLog = f.Createtextfile(fFolderName, True)
> Set mLog = f.Createtextfile(mFileName, True)
>
> Snooping around in the VBA help I can find no reference to
> Scripting.FileSystemObject or APPDATA. How did you discovery/stumble upon
> them?
>
>
>
> --
> Greg Maxey/Word MVP
> See:
> http://gregmaxey.mvps.org/word_tips.htm
> For some helpful tips using Word.
>
> rhamre@citation.com wrote:
> > I'm glad you liked it, the yes/no message box at the end of inputting
> > path and macro is a good idea. I have to say though, i'm a little
> > partial to stating how many folders and how many macro's prior, just
> > because it's less program interaction, and i usually know how many
> > folders and how many macros before i even run the program.
> >
> > It's too bad the FilteredHTML stuff doesnt work with 2000. In 2003
> > you can save things as FilteredHTML and it gets rid of the MS Word
> > Specific Programming in files. This allows windows lists to become
> > text space lists, and you don't have to deal with a bunch of other
> > things as well.
> >
> > Also, with the FilteredHTML files, they can be editted in Frontpage
> > or even notepad for that matter, letting you format your document by
> > the tags inside the file.
> >
> > All in all, i'm glad you liked the program, and i hope it helps you or
> > anyone else for that matter.
> >
> > "Greg" wrote:
> >
> >> I think you code is pretty neat. The Filter HTML stuff doesn't
> >> appear
> >> to be applicable to Word2000, so I took it out. I also worked with
> >> your method to add folders and macros:
> >>
> >> Sub MacroRunner()
> >>
> >> Dim fFolderName As String, mFileName As String, oFolderPath As
> >> String, _
> >> MtoRun As String, MtoRunName As String
> >> Dim f
> >> Dim fLog
> >> Dim mLog
> >> Dim i As Long
> >> Dim oDoc As Document
> >>
> >> Set f = CreateObject("Scripting.FileSystemObject")
> >> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
> >> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
> >> Set fLog = f.Createtextfile(fFolderName, True)
> >> Set mLog = f.Createtextfile(mFileName, True)
> >>
> >> Application.ScreenUpdating = False
> >>
> >> Do
> >> oFolderPath = InputBox("Enter the compelete path of the folder
> >> containing " _
> >> & "the files you want to process (e.g, C:\My Documents\Batch
> >> Folder)", _
> >> "Folder Path")
> >> If Len(oFolderPath) = 0 Then
> >> MsgBox ("Nothing entered. Exiting routine.")
> >> Exit Sub
> >> Else
> >> fLog.writeline (oFolderPath)
> >> End If
> >> Loop While MsgBox("Do you want to process an additional folder?",
> >> vbYesNo + vbQuestion, _
> >> "More Folders?") = vbYes
> >>
> >> Do
> >> MtoRun = InputBox("Enter the name of the macro that you want to
> >> run.", "Macro Name")
> >> If Len(MtoRun) = 0 Then
> >> MsgBox ("Nothing entered. Exiting routine.")
> >> Exit Sub
> >> Else
> >> mLog.writeline (MtoRun)
> >> End If
> >> Loop While MsgBox("Do you want to run an additional macro?", vbYesNo
> >> + vbQuestion, _
> >> "More macros?") = vbYes
> >>
> >> fLog = (fFolderName)
> >> mLog = (mFileName)
> >> Open fLog For Input As #1
> >> Do While Not EOF(1)
> >> Line Input #1, oFolderPath
> >> With Application.FileSearch
> >> .NewSearch
> >> .LookIn = oFolderPath
> >> .SearchSubFolders = False
> >> .FileType = msoFileTypeAllFiles
> >> If Not .Execute() = 0 Then
> >> For i = 1 To .FoundFiles.Count
> >> Open mLog For Input As #2
> >> Set oDoc = Documents.Open(.FoundFiles(i))
> >> Do While Not EOF(2)
> >> Line Input #2, MtoRunName
> >> Application.Run MtoRunName
> >> Loop
> >> ActiveDocument.Save
> >> ActiveDocument.Close
> >> Set oDoc = Nothing
> >> Close #2
> >> Next i
> >> Else
> >> MsgBox "No files in specified folder(s)"
> >> End If
> >> End With
> >> Loop
> >> Close #1
> >> f.deletefile mFileName
> >> f.deletefile fFolderName
> >> Application.ScreenUpdating = True
> >> MsgBox "Finally... Done"
> >> End Sub
> >> Sub Test()
> >> MsgBox ActiveDocument.ComputeStatistics(wdStatisticPages)
> >> End Sub
>
>
>

Greg
Wed Nov 02 16:36:53 CST 2005

Well, I don't know what I will do with it but I will certainly keep it
around should the need arise. Glad you like the filecopy piece. I picked
that up from another post. It is amazing what little jewels one can stumble
on ;-)



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

rhamre@citation.com wrote:
> Oh, i like that! that will totally work.
>
> About the-
>
>> Set f = CreateObject("Scripting.FileSystemObject")
>> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
>> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
>> Set fLog = f.Createtextfile(fFolderName, True)
>> Set mLog = f.Createtextfile(mFileName, True)
>
> As far as i know this is just normal VB, not specifically VBA.
>
> This method was tought to me buy a programmer i work with to create a
> log file. I've been using his method to create log files and files to
> store data and then pull it back into macro's. It's really a nifty
> little nippet, and i've been using it for varius things ever since i
> learned it.
>
> I hope you're able to put it to good use. I don't know of any other
> way to do these things as i don't know how to program quite well yet.
>
> Enjoy the program, thanks for the filecopy code, i'll tear it apart,
> figure it out, and rebuild it. then see what i can do with it. ;-)
>
> "Greg Maxey" wrote:
>
>> Instead of typing in the folder paths, you migh like using the
>> filecopy dialog.
>>
>> Do
>> With Dialogs(wdDialogCopyFile)
>> If .Display <> 0 Then
>> oFolderPath = .Directory
>> fLog.writeline (oFolderPath)
>> Else
>> MsgBox "Cancelled by User"
>> Exit Sub
>> End If
>> End With
>> Loop While MsgBox("Do you want to process an additional folder?",
>> vbYesNo + vbQuestion, _
>> "More Folders?") = vbYes
>>
>> Maybe you could take a minute and explain how you developed this
>> little snipet:
>>
>> Set f = CreateObject("Scripting.FileSystemObject")
>> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
>> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
>> Set fLog = f.Createtextfile(fFolderName, True)
>> Set mLog = f.Createtextfile(mFileName, True)
>>
>> Snooping around in the VBA help I can find no reference to
>> Scripting.FileSystemObject or APPDATA. How did you
>> discovery/stumble upon them?
>>
>>
>>
>> --
>> Greg Maxey/Word MVP
>> See:
>> http://gregmaxey.mvps.org/word_tips.htm
>> For some helpful tips using Word.
>>
>> rhamre@citation.com wrote:
>>> I'm glad you liked it, the yes/no message box at the end of
>>> inputting path and macro is a good idea. I have to say though, i'm
>>> a little partial to stating how many folders and how many macro's
>>> prior, just because it's less program interaction, and i usually
>>> know how many folders and how many macros before i even run the
>>> program.
>>>
>>> It's too bad the FilteredHTML stuff doesnt work with 2000. In 2003
>>> you can save things as FilteredHTML and it gets rid of the MS Word
>>> Specific Programming in files. This allows windows lists to become
>>> text space lists, and you don't have to deal with a bunch of other
>>> things as well.
>>>
>>> Also, with the FilteredHTML files, they can be editted in Frontpage
>>> or even notepad for that matter, letting you format your document by
>>> the tags inside the file.
>>>
>>> All in all, i'm glad you liked the program, and i hope it helps you
>>> or anyone else for that matter.
>>>
>>> "Greg" wrote:
>>>
>>>> I think you code is pretty neat. The Filter HTML stuff doesn't
>>>> appear
>>>> to be applicable to Word2000, so I took it out. I also worked with
>>>> your method to add folders and macros:
>>>>
>>>> Sub MacroRunner()
>>>>
>>>> Dim fFolderName As String, mFileName As String, oFolderPath As
>>>> String, _
>>>> MtoRun As String, MtoRunName As String
>>>> Dim f
>>>> Dim fLog
>>>> Dim mLog
>>>> Dim i As Long
>>>> Dim oDoc As Document
>>>>
>>>> Set f = CreateObject("Scripting.FileSystemObject")
>>>> fFolderName = Environ$("APPDATA") & "\~folders" & ".txt"
>>>> mFileName = Environ$("APPDATA") & "\~macros" & ".txt"
>>>> Set fLog = f.Createtextfile(fFolderName, True)
>>>> Set mLog = f.Createtextfile(mFileName, True)
>>>>
>>>> Application.ScreenUpdating = False
>>>>
>>>> Do
>>>> oFolderPath = InputBox("Enter the compelete path of the folder
>>>> containing " _
>>>> & "the files you want to process (e.g, C:\My Documents\Batch
>>>> Folder)", _
>>>> "Folder Path")
>>>> If Len(oFolderPath) = 0 Then
>>>> MsgBox ("Nothing entered. Exiting routine.")
>>>> Exit Sub
>>>> Else
>>>> fLog.writeline (oFolderPath)
>>>> End If
>>>> Loop While MsgBox("Do you want to process an additional folder?",
>>>> vbYesNo + vbQuestion, _
>>>> "More Folders?") = vbYes
>>>>
>>>> Do
>>>> MtoRun = InputBox("Enter the name of the macro that you want to
>>>> run.", "Macro Name")
>>>> If Len(MtoRun) = 0 Then
>>>> MsgBox ("Nothing entered. Exiting routine.")
>>>> Exit Sub
>>>> Else
>>>> mLog.writeline (MtoRun)
>>>> End If
>>>> Loop While MsgBox("Do you want to run an additional macro?",
>>>> vbYesNo + vbQuestion, _
>>>> "More macros?") = vbYes
>>>>
>>>> fLog = (fFolderName)
>>>> mLog = (mFileName)
>>>> Open fLog For Input As #1
>>>> Do While Not EOF(1)
>>>> Line Input #1, oFolderPath
>>>> With Application.FileSearch
>>>> .NewSearch
>>>> .LookIn = oFolderPath
>>>> .SearchSubFolders = False
>>>> .FileType = msoFileTypeAllFiles
>>>> If Not .Execute() = 0 Then
>>>> For i = 1 To .FoundFiles.Count
>>>> Open mLog For Input As #2
>>>> Set oDoc = Documents.Open(.FoundFiles(i))
>>>> Do While Not EOF(2)
>>>> Line Input #2, MtoRunName
>>>> Application.Run MtoRunName
>>>> Loop
>>>> ActiveDocument.Save
>>>> ActiveDocument.Close
>>>> Set oDoc = Nothing
>>>> Close #2
>>>> Next i
>>>> Else
>>>> MsgBox "No files in specified folder(s)"
>>>> End If
>>>> End With
>>>> Loop
>>>> Close #1
>>>> f.deletefile mFileName
>>>> f.deletefile fFolderName
>>>> Application.ScreenUpdating = True
>>>> MsgBox "Finally... Done"
>>>> End Sub
>>>> Sub Test()
>>>> MsgBox ActiveDocument.ComputeStatistics(wdStatisticPages)
>>>> End Sub

Jean-Guy
Wed Nov 02 21:10:51 CST 2005

Greg Maxey was telling us:
Greg Maxey nous racontait que :

> Instead of typing in the folder paths, you migh like using the
> filecopy dialog.

At the beginning of this sub-thread, that was the first thing that popped
into my head.... Why use an InputBox?

I am glad I read the whole thread ... so I did not have to repost something
you had already taken care of.

Just a question though...

Why create log files? Will the info in them be used again at some other
time? I guess not because they get overwritten every time the macro is run.

Why not use array variables to store the information? Seems like a lot of
work (Add a reference, create an object, manipulate the object, etc.) when a
simple array variable would do as well. No?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Greg
Wed Nov 02 21:16:57 CST 2005

Would you be willing to show me how ;-)


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Jean-Guy Marcil wrote:
> Greg Maxey was telling us:
> Greg Maxey nous racontait que :
>
>> Instead of typing in the folder paths, you migh like using the
>> filecopy dialog.
>
> At the beginning of this sub-thread, that was the first thing that
> popped into my head.... Why use an InputBox?
>
> I am glad I read the whole thread ... so I did not have to repost
> something you had already taken care of.
>
> Just a question though...
>
> Why create log files? Will the info in them be used again at some
> other time? I guess not because they get overwritten every time the
> macro is run.
> Why not use array variables to store the information? Seems like a
> lot of work (Add a reference, create an object, manipulate the
> object, etc.) when a simple array variable would do as well. No?

Greg
Wed Nov 02 21:56:04 CST 2005

Something like this I suppose:

Sub MacroRunnerWithArrays()

Dim folderList() As String
Dim macroList() As String
Dim oFolderPath As String
Dim macroName As String
Dim i As Long, j As Long, k As Long
Dim oDoc As Document

Application.ScreenUpdating = False
ReDim folderList(0)
ReDim macroList(0)
Do
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
oFolderPath = .Directory
folderList(UBound(folderList)) = oFolderPath
ReDim Preserve folderList(UBound(folderList) + 1)
Else
MsgBox "Cancelled by User"
Exit Sub
End If
End With
Loop While MsgBox("Do you want to process an additional folder?", vbYesNo +
vbQuestion, _
"More Folders?") = vbYes
Do
macroName = InputBox("Enter the name of the macro that you want to run.",
"Macro Name")
If Len(macroName) = 0 Then
MsgBox ("Nothing entered. Exiting routine.")
Exit Sub
Else
macroList(UBound(macroList)) = macroName
ReDim Preserve macroList(UBound(macroList) + 1)
End If
Loop While MsgBox("Do you want to run an additional macro?", vbYesNo +
vbQuestion, _
"More macros?") = vbYes

For i = 0 To UBound(folderList) - 1
With Application.FileSearch
.NewSearch
.LookIn = folderList(i)
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles
If Not .Execute() = 0 Then
For j = 1 To .FoundFiles.Count
Set oDoc = Documents.Open(.FoundFiles(j))
For k = 0 To UBound(macroList) - 1
Application.Run macroList(k)
Next k
ActiveDocument.Save
ActiveDocument.Close
Set oDoc = Nothing
Next j
Else
MsgBox "No files in specified folder(s)"
End If
End With
Next i
Application.ScreenUpdating = True
MsgBox "All Done"
End Sub


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Jean-Guy Marcil wrote:
> Greg Maxey was telling us:
> Greg Maxey nous racontait que :
>
>> Instead of typing in the folder paths, you migh like using the
>> filecopy dialog.
>
> At the beginning of this sub-thread, that was the first thing that
> popped into my head.... Why use an InputBox?
>
> I am glad I read the whole thread ... so I did not have to repost
> something you had already taken care of.
>
> Just a question though...
>
> Why create log files? Will the info in them be used again at some
> other time? I guess not because they get overwritten every time the
> macro is run.
> Why not use array variables to store the information? Seems like a
> lot of work (Add a reference, create an object, manipulate the
> object, etc.) when a simple array variable would do as well. No?

Jean-Guy
Thu Nov 03 09:32:08 CST 2005

Greg Maxey was telling us:
Greg Maxey nous racontait que :

> Something like this I suppose:
>

Exactly :-)

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

DGjr
Thu Apr 13 10:10:01 CDT 2006

Anne or Tony: Where in the macro provided by vbaexpress do you actually plug
in the name of the macro that you want to run against all files in a
directory?

e.g. I want strip data from a form and append an excel spreadsheet - code
welcomed ;)

Donnie

"Anne Troy" wrote:

> Tony, this should help you: http://vbaexpress.com/kb/getarticle.php?kb_id=76
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "Tony" <Tony@discussions.microsoft.com> wrote in message
> news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
> >I have the macro which I want to execute for all files in the folder - open
> > each file, execute macro, close the file. Can I do it without creating the
> > table with file names ? If yes, how ?
> >
> > Thanks for help.
> >
> > Tony
>
>
>

Tony
Mon Apr 17 11:18:58 CDT 2006

I just took a quick look at that link - and right at the end is this:

'this is where a macro would be that would actually do something
Sub DoWork(wdDoc As Document)

End Sub

You would, presumably put your code in there.

--
Enjoy,
Tony

"DGjr." <DGjr@discussions.microsoft.com> wrote in message
news:1CF93965-0878-4171-849E-F1A01840F9E1@microsoft.com...
> Anne or Tony: Where in the macro provided by vbaexpress do you actually
plug
> in the name of the macro that you want to run against all files in a
> directory?
>
> e.g. I want strip data from a form and append an excel spreadsheet - code
> welcomed ;)
>
> Donnie
>
> "Anne Troy" wrote:
>
> > Tony, this should help you:
http://vbaexpress.com/kb/getarticle.php?kb_id=76
> > ************
> > Anne Troy
> > www.OfficeArticles.com
> >
> > "Tony" <Tony@discussions.microsoft.com> wrote in message
> > news:BE4E8CE8-EE82-450C-B4DA-A8A369F6824D@microsoft.com...
> > >I have the macro which I want to execute for all files in the folder -
open
> > > each file, execute macro, close the file. Can I do it without creating
the
> > > table with file names ? If yes, how ?
> > >
> > > Thanks for help.
> > >
> > > Tony
> >
> >
> >

DavidTurner
Tue Jul 15 13:53:03 PDT 2008



> macroName = InputBox("Enter the name of the macro that you want to run.",
> "Macro Name")

This great macro might be even better if the macro name(s) could be entered
through a dop-down list using Word's built-in dialog box, as with Alt+F8.
Unfortunately, I can't seem to get it to work. After much trial and error, I
found that Dialogs(215) displays the Tools/Macros dialog box but:

Dim macroName as Dialog
Set macroName = Dialogs(215)
macroName.Display

doesn't seem to return the macro name selected (it just returns 215).
Any ideas?
Thanks.

David Turner

Doug
Tue Jul 15 20:00:27 PDT 2008

Take a look at Word KB article Q262961 "WD2000: Macro Example to Return
Macro and Procedure Names" at:

http://support.microsoft.com/Default.aspx?kbid=262961


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"David Turner" <DavidTurner@discussions.microsoft.com> wrote in message
news:98C35FB1-508B-49BD-B2AA-D4FC87CB4BE4@microsoft.com...
>
>
>> macroName = InputBox("Enter the name of the macro that you want to run.",
>> "Macro Name")
>
> This great macro might be even better if the macro name(s) could be
> entered
> through a dop-down list using Word's built-in dialog box, as with Alt+F8.
> Unfortunately, I can't seem to get it to work. After much trial and error,
> I
> found that Dialogs(215) displays the Tools/Macros dialog box but:
>
> Dim macroName as Dialog
> Set macroName = Dialogs(215)
> macroName.Display
>
> doesn't seem to return the macro name selected (it just returns 215).
> Any ideas?
> Thanks.
>
> David Turner

DavidTurner
Wed Jul 16 07:22:01 PDT 2008

Thanks for the link Doug. I guess that macro could be adapted to present the
user with a list of macros to be run in a List Box. Pity you can't seem to
use the Tools/Macros built-in dialog box though. And surprising as Alt+F8
must be one of the first things you learn to do when working with macros.

Regards,
David Turner

"Doug Robbins - Word MVP" wrote:

> Take a look at Word KB article Q262961 "WD2000: Macro Example to Return
> Macro and Procedure Names" at:
>
> http://support.microsoft.com/Default.aspx?kbid=262961
>
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "David Turner" <DavidTurner@discussions.microsoft.com> wrote in message
> news:98C35FB1-508B-49BD-B2AA-D4FC87CB4BE4@microsoft.com...
> >
> >
> >> macroName = InputBox("Enter the name of the macro that you want to run.",
> >> "Macro Name")
> >
> > This great macro might be even better if the macro name(s) could be
> > entered
> > through a dop-down list using Word's built-in dialog box, as with Alt+F8.
> > Unfortunately, I can't seem to get it to work. After much trial and error,
> > I
> > found that Dialogs(215) displays the Tools/Macros dialog box but:
> >
> > Dim macroName as Dialog
> > Set macroName = Dialogs(215)
> > macroName.Display
> >
> > doesn't seem to return the macro name selected (it just returns 215).
> > Any ideas?
> > Thanks.
> >
> > David Turner
>
>
>