Run a Word Macro from Excel VBA
 

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

Run a Word Macro from Excel VBA
 

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



"Daniel" wrote in message
...
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

Run a Word Macro from Excel VBA
 

Steve,
great,

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

"Steve Yandl" wrote:

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



"Daniel" wrote in message
...
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

Run a Word Macro from Excel VBA
 

No much different

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

Then

Dim objWdApp as Word.Application

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

Faisal...



On Nov 2, 4:00 pm, Daniel wrote:
Steve,
great,

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



"Steve Yandl" wrote:
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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Run a Word Macro from Excel VBA
 

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



"Daniel" wrote in message
...
Steve,
great,

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

"Steve Yandl" wrote:

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



"Daniel" wrote in message
...
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

Run a Word Macro from Excel VBA
 

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


On Nov 2, 4:58 pm, "Steve Yandl" wrote:
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

"Daniel" wrote in message

...



Steve,
great,

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

"Steve Yandl" wrote:

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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Run a Word Macro from Excel VBA
 

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

"Faisal..." wrote:

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


On Nov 2, 4:58 pm, "Steve Yandl" wrote:
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

"Daniel" wrote in message

...



Steve,
great,

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

"Steve Yandl" wrote:

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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Run a Word Macro from Excel VBA
 

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



"Daniel" wrote in message
...
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

"Faisal..." wrote:

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


On Nov 2, 4:58 pm, "Steve Yandl" wrote:
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

"Daniel" wrote in message

...



Steve,
great,

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

"Steve Yandl" wrote:

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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Run a Word Macro from Excel VBA
 

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" wrote:

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



"Daniel" wrote in message
...
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

"Faisal..." wrote:

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


On Nov 2, 4:58 pm, "Steve Yandl" wrote:
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

"Daniel" wrote in message

...



Steve,
great,

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

"Steve Yandl" wrote:

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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Run a Word Macro from Excel VBA
 

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


"Daniel" wrote in message
...

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" wrote:

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



"Daniel" wrote in message
...
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

"Faisal..." wrote:

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


On Nov 2, 4:58 pm, "Steve Yandl" wrote:
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

"Daniel" wrote in message

...



Steve,
great,

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

"Steve Yandl" wrote:

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

"Daniel" wrote in message
...
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- Hide quoted text -

- Show quoted text -