execute macro for all documents in the folder

T

Tony

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
 
C

Charles Kenyon

R

rhamre

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
 
G

Greg

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
 
R

rhamre

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

Greg Maxey

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?
 
R

rhamre

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)
Click to expand...

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

Greg Maxey

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 ;-)
 
J

Jean-Guy Marcil

Greg Maxey was telling us:
Greg Maxey nous racontait que :
Instead of typing in the folder paths, you migh like using the
filecopy dialog.
Click to expand...

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
G

Greg Maxey

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
 
D

DGjr.

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
 
T

Tony Jollans

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

David Turner

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

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
 
D

David Turner

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Need Help with Macro Security 1
VBA - exporting serial letters to single documents 0
Need help modifying code 0
Synchronization problems while moving folders to new email 1
VBA word change keys 0
Making a macro only work on certain files 2
Want to have macro execute automatically when doc created 3

Top