Solved VB Script to Run Macro

November 10, 2011 at 09:09:19
Specs: Windows XP, Core2
Hello friends,

I've been asked to convert about 700+ word files into Word HTML. As you know, the word document with HTML as its extension. Yes, you guessed it right, Save As HTML.

I created a macro which saves a word document as HTML with the current file name and then closes it. Got this after some online research. This works great with a single open document. I need to automate it for all the 700 docs. I planned to open the word docs and call the macro using a batch or vbscript.

Tried batch first. The batch i created opens just one word document, saves it as html and closes it. Then it opens a new word document, and not the files in the directory. Failed! I have the batch script at office, if you need it for reference, pls tell me. I will post it.

Tried a vb script, but dont know how to add a variable to run it on all word docs in a directory structure, one by one. Here is the code:

Dim objSaveAs

Set objSaveAs = CreateObject("Word.Application")

on error resume next

With objSaveAs

.documents.Open (“D:\Test\Test.doc”)

.Run "Macro1"

.Quit

End With

Set objSaveAs = Nothing

This will run the macro in word for Test.doc. I have many folders and subfolders, inside the folder 'Test', and many word docs in each. I want the batch or script to open each word doc, and run the macro, one by one. The macro will close each doc.

Please help. I know we have many smart techies here. Thanks in advance.


See More: VB Script to Run Macro

Report •


#1
November 10, 2011 at 09:55:34
✔ Best Answer
VBScript:
Const dirPath = "D:\Test"
Set word = CreateObject("Word.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
For Each file In fso.GetFolder(dirPath).Files
  ext = UCase(fso.GetExtensionName(file))
  If ext = "DOC" Or ext = "DOCX" Then
    docName = Left(file, Len(file) - Len(ext)) & "html"
    Set doc = GetObject(file)
    doc.SaveAs docName, 8
    doc.Close 0
  End If
Next 'file
word.Quit 0
WScript.Echo "Done"

How To Ask Questions The Smart Way


Report •

#2
November 10, 2011 at 10:13:36
Thank you very much for the timely help Razor. Seeing this, i think i dont even need a macro script in the word template to get a result. I will test this tomorrow within the required folder tree. And surely will post the result.

Thanks again!


Report •

#3
November 10, 2011 at 10:37:56
within the required folder tree
Note: The script's working against a directory, not diving into its structure. If you need the script to run against a tree, it will need to be modified.

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
November 10, 2011 at 21:39:40
Razor, Thanks very much genius! lovely. I owe you a drink.

It works great within a given folder. I can get my work done using this itself, may be, with an xcopy command to organize the files. If there is possibly a way to get it working in a folder tree, it will be great. I wish to keep this script for any such future requirements. So, something that works within any given folder tree, irrespective of the drive or folder, would be a great solution!

:)


Report •

#5
November 11, 2011 at 06:54:16
Do you wish for it to work against a directory tree or a directory? There's a large difference there. Working against a tree is recursive, and processes any subdirectories. Working against a directory leaves any subdirectories untouched.

How To Ask Questions The Smart Way


Report •

#6
November 11, 2011 at 09:00:46
Hi Razor,

I wanted it to work in any given directory tree. However, the files that I wanted to process follow specific naming conventions. So, I ran a batch and got all the files in a specific folder and ran your script. Ran another batch to reorganize the files. My purpose was definitely met with this. My sincere thanks.

Yet I wished for a script to work in any given directory TREE. In the current situation, I expect such requests to come more often and all the time the files in a directory tree may not follow specific naming conventions.

Thanks a lot for your help.


Report •

#7
November 11, 2011 at 11:02:50
Recursive is so much more work, and I'm so lazy. I also threw in a GUI directory picker, because I felt like it.

Set word = CreateObject("Word.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
rootDir = GetDir
If Len(rootDir) Then _
  ProcessDir(rootDir)
word.Quit 0
WScript.Echo "Done"
WScript.Quit 0

Function GetDir()
  Dim shell : Set shell = CreateObject("Shell.Application")
  Set folder = shell.BrowseForFolder(0, WScript.ScriptName, &H51)
  If Not folder Is Nothing Then
    GetDir = folder.Self.Path
  Else
    GetDir = ""
  End If
End Function

Sub ProcessDir(dirPath)
  Dim dir : Set dir = fso.GetFolder(dirPath)
  For Each folder In dir.SubFolders
    ProcessDir(folder)
  Next 'folder
  
  For Each file In dir.Files
    ext = UCase(fso.GetExtensionName(file))
    If ext = "DOC" Or ext = "DOCX" Then
      docName = Left(file, Len(file) - Len(ext)) & "html"
      Set doc = GetObject(file)
      doc.SaveAs docName, 8
      doc.Close 0
    End If
  Next 'file
End Sub

How To Ask Questions The Smart Way


Report •

#8
November 11, 2011 at 22:26:16
Can I believe this!!! Razor, this is more than what I wanted. Works beautifully.

With this post and your solutions, I have started admiring three - you, this forum and vb script. Wow!

I have also determined to learn VB seriously. And I'm sure that it will reduce a lot of mechanical tasks that we encounter day to day.

I wish to say, it was sooo kind of you to spend your time on this, and express my sincere gratitude. Thanks master! love this. :)


Report •


Ask Question