Run a Word Macro from Excel VBA

[Guest]

How can I Run a macro recorded in word in excel VBA.

Note: the word macro performs a function on a text file that I cant perform
from excel VBA, which is to get a .txt file and make some replacements and
save it.

thanks

 

[Steve Yandl]

Daniel,

Here is an example. In this case, my macro was stored in a template but you
can run a macro from a document file as well. As an alternate approach, you
can create a reference to the word application and have the same tools
available from your VBA routine within Excel which would have the advantage
of not being tied to some Word document or template.

Set objWdApp = CreateObject("Word.Application")
objWdApp.Documents.Add("C:\Test\myVBA.dot")
objWdApp.Run "myMacroA"


Steve Yandl

 

[Guest]

Steve,
great,

do you have a sample of the alternate approach, I would much prefer that
thx

 

[Faisal...]

No much different

Add a reference to "Microsoft Word 11.0 Object Library" (In VB Editor
go to Tools>References)

Then

Dim objWdApp as Word.Application

Set objWdApp = New Word.Application
objWdApp.Documents.Add("C:\Test\myVBA.dot")
objWdApp.Run "myMacroA"

Faisal...

 

[Steve Yandl]

Daniel,

Below is an example I had handy that I used in a VBScript file but the code
could easily have been run from inside a VBA routine. In this case, I was
making changes to fields within a specific Word document but could have
easily done a find and replace for other items.

If the file you're working with is an actual text document rather than a
Word document, the "Scripting.FileSystemObject" might be an even more
efficient approach. What type text file is it and how complex is the find
and replace operation?

________________________________

Const wdSaveChanges = -1

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open("C:\Scripts\Test1.doc")

For Each itemField In objDoc.Fields
strCode = itemField.Code.Text
strCode = Replace(strCode, "DATE", "CREATEDATE")
strCode = Replace(strCode, "TIME", "CREATEDATE")
itemField.Code.Text = strCode
Next

objDoc.Close(wdSaveChanges)
objWord.Quit
________________________________

Steve

 

[Faisal...]

If it is a txt file then it is worth running everything from one place
(excel or word) by using the open commands:

open FILENAME for output as #1
......

 

[Guest]

Steve, Faisal,
I am trying to modify a text file(fortran script), very few simple
mods/replacements here and there, then run the shell cmd and wait.

Just need to reach the file and make the substitutions, that's all but I am
not there yet

I will try Steve's code, let you know soon

 

[Steve Yandl]

Danial,

I suspect you'll be able to figure out how to do it with Word. If you want
to look at a different option, below is a sub that locates a file named
"C:\Test\myText.txt" and replaces all the less than signs with greater than
signs. As you can see, there isn't much to it.

___________________________

Sub FixTextFile()

Const ForReading = 1
Const ForWriting = 2

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFile = FSO.OpenTextFile("C:\Test\myText.txt", ForReading)

strText = objFile.ReadAll
objFile.Close

strOldText = "<"
strNewText = ">"

strNewText = Replace(strText, strOldText, strNewText)

Set objFile = FSO.OpenTextFile("C:\Test\myText.txt", ForWriting)
objFile.WriteLine strNewText
objFile.Close

Set objFile = Nothing
Set FSO = Nothing

End Sub
__________________________

Steve

 

[Guest]

Steve, worked just perfect
thanks
by the way, do you have or think about a sub to write on a text file a
script in col A1:A100 of workbook data?
thx

Dan

 

[Steve Yandl]

Dan,

If I understand your question correctly, you have composed a script with 100
lines, each line held in its own cell in Column A of a worksheet and you
would like to create a new script or append an existing script with the
lines.

The scripting file system object has a method to create a text file as well
as the open method I used in my example above. If you're using the open
method, you specify whether you're opening ForReading, ForWriting, or
ForAppending. In the case of creating a new text file, there is a boolean
argument that determines whether or not you overwrite any existing file that
happens to already have the file name you're wanting to assign.

Are you needing to create a new text file or append an existing one?


Steve