 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / August 2006Run 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! 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'm proud of myself. I actually came up with a very useful Word VBA > macro without having to ask anyone how to do it: [quoted text clipped - 28 lines] > > Hooray for Google Groups! I love you! Regardless, James. Thank you for sharing this with everyone. I'm sure it will help someone out tremendously. > You could improve it by using a document variable, rather than relying on > the ActiveDocument and ActiveWindow -- [quoted text clipped - 38 lines] > > > > Hooray for Google Groups! I love you! > Regardless, James. Thank you for sharing this with everyone. I'm sure it > will help someone out tremendously. You are very welcome, zkid. > You could improve it by using a document variable, rather than relying on > the ActiveDocument and ActiveWindow -- [quoted text clipped - 5 lines] > 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! 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. >> You could improve it by using a document variable, rather than relying on >> the ActiveDocument and ActiveWindow -- [quoted text clipped - 29 lines] > > It works! Thanks, Jezebel! "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. > 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 [quoted text clipped - 3 lines] > a convention you like, and stick to it. It will save you a *lot* of time > long-term. I think 'requiring' prefixes would be overkill. VB/VBA determines data type from the declaration, which is all it needs for compilation. > "Invidious?" Don't you mean "insidious?" :-) > [quoted text clipped - 18 lines] >> a convention you like, and stick to it. It will save you a *lot* of time >> long-term. > 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. 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. >> I think 'requiring' prefixes would be overkill. VB/VBA determines data >> type [quoted text clipped - 12 lines] > actually a compiler, even though it functions like a super-fast > interpreter. > 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? > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.