Run WORD code from Excel
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Run WORD code from Excel
I need to run WordBasic code in WORD on a WORD document. I want to control this from Excel.
I can use Excel to start WORD, load a file and run the code. Problem is that I need to 'find' the code somewhere. One way is to first load a WORD file with the WORD BAsic code and then load the WORD target file. Now let Excel run the code and make it do its job on the target WORD document.
I'd like to do this more 'compact'... can I (from Excel) open WORD, then (all still from Excel), copy text WORD basic code from an Excel (hidden) sheet into the target WORD file and then run it? Of course after I'm done, I need to eliminate the code from the WORD file but I can do that by saving the result WORD file as DOCX in stead of DOCM... Does this make sense? If so, how would I code the part where I copy the Excel code into WORD Basic? I can get it onto the clipboard but then what?
End result is that all is in Excel, and from there I also run the WORD code.
I can use Excel to start WORD, load a file and run the code. Problem is that I need to 'find' the code somewhere. One way is to first load a WORD file with the WORD BAsic code and then load the WORD target file. Now let Excel run the code and make it do its job on the target WORD document.
I'd like to do this more 'compact'... can I (from Excel) open WORD, then (all still from Excel), copy text WORD basic code from an Excel (hidden) sheet into the target WORD file and then run it? Of course after I'm done, I need to eliminate the code from the WORD file but I can do that by saving the result WORD file as DOCX in stead of DOCM... Does this make sense? If so, how would I code the part where I copy the Excel code into WORD Basic? I can get it onto the clipboard but then what?
End result is that all is in Excel, and from there I also run the WORD code.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
Why don't you use Automation to run the code directly from Excel?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Run WORD code from Excel
Yes, that's better still but how do I start? Can you give a short example maybe?
Also, the WordBasic code I was given is many pages; I'm not a WordBasic expert; would I have to 'translate' that all to VBA based code that runs from Excel then?
Also, the WordBasic code I was given is many pages; I'm not a WordBasic expert; would I have to 'translate' that all to VBA based code that runs from Excel then?
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
Automating Word from Excel means that you run Word VBA in Excel.
Here is an example.
Start by selecting Tools | References in the Visual Basic Editor in Excel.
Scroll down until you see Microsoft Word n.0 Object Library, where n is the internal version of Office on your PC (e.g. 11 = Office 2003, 12 = Office 2007, 14 = Office 2010).
Tick its check box and click OK.
Here is a Word macro:
To run this from Excel, you wrap the Word code in Automation code:
Here is an example.
Start by selecting Tools | References in the Visual Basic Editor in Excel.
Scroll down until you see Microsoft Word n.0 Object Library, where n is the internal version of Office on your PC (e.g. 11 = Office 2003, 12 = Office 2007, 14 = Office 2010).
Tick its check box and click OK.
Here is a Word macro:
Code: Select all
Sub ProcessDoc()
Dim doc As Document
Set doc = Documents.Open("MyDoc.doc")
With doc.Content.Find
.Text = "Word"
.Replacement.Text = "Excel"
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
doc.Close SaveChanges:=True
Set doc = Nothing
End Sub
Code: Select all
Sub ProcessDoc()
Dim app As Word.Application
Dim doc As Word.Document
Dim blnStart As Boolean
On Error Resume Next
Set app = GetObject(, "Word.Application")
If app Is Nothing Then
Set app = CreateObject("Word.Application")
If app Is Nothing Then
MsgBox "Cannot start Word", vbExclamation
Exit Sub
End If
blnStart = True
End If
On Error GoTo ErrHandler
' Here is the slightly modified original code
Set doc = app.Documents.Open("MyDoc.doc")
With doc.Content.Find
.Text = "Word"
.Replacement.Text = "Excel"
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
doc.Close SaveChanges:=True
Set doc = Nothing
' End of the original code
ExitHandler:
On Error Resume Next
If Not doc Is Nothing Then
doc.Close SaveChanges:=False
End If
If Not app Is Nothing And blnStart Then
app.Quit
End If
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Run WORD code from Excel
Thank you, busy implementing.
I get an error in the WORD Basic code on ActiveWindow
Tried to precede it with "Doc." but that was too simple ;-). Any suggestions?
EDIT: To be honest... upon reviewing, these ActiveWindow calls don't seem to make sense they don't do anything with the document I guess. It probably came there when the author recorded the code...
I get an error in the WORD Basic code on ActiveWindow
Code: Select all
If ActiveWindow.View.SplitSpecial = wdPaneNone Then
ActiveWindow.ActivePane.View.Type = wdPageView
Else
ActiveWindow.View.Type = wdPageView
End If
EDIT: To be honest... upon reviewing, these ActiveWindow calls don't seem to make sense they don't do anything with the document I guess. It probably came there when the author recorded the code...
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
ActiveWindow is also an Excel object, so without further qualification Excel VBA interprets it as the active Excel window.
Try app.ActiveWindow.
Try app.ActiveWindow.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
In light of your edit: see what happens if you comment out those lines.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Run WORD code from Excel
Yeah, I'll edit them out (there's many, the full -recorded- code was 27 pages... I'm sure I can reduce and optimize that).
By the way... the "app." prefix doesn't help
By the way... the "app." prefix doesn't help
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
Perhaps ActiveWindow doesn't work if Word is invisible.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Run WORD code from Excel
Well. maybe it does (the "App." prefix), I forgot to transfer the variable to the code sub... sorry.
Before I get there, I still need to 'get the idea' as errors are generated still (Word, does open up and automation works... a bit)
This is the original start of the WORDBasic code
That doesn't work and creates an error on the first line, so I make it into this:
Now I get an error on ".RightIndent" and I'm lost...
Before I get there, I still need to 'get the idea' as errors are generated still (Word, does open up and automation works... a bit)
This is the original start of the WORDBasic code
Code: Select all
Selection.WholeStory
With Selection.ParagraphFormat
.RightIndent = InchesToPoints(-1.02)
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
Code: Select all
app.Selection.WholeStory
With app.Selection.ParagraphFormat
.RightIndent = InchesToPoints(-1.02)
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
I guess we'll have to know more about the code - when I run your code by itself from Excel (after creating app and doc, of course), it runs without error.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Run WORD code from Excel
I already got it...
The error was on the line: ".RightIndent = InchesToPoints(-1.02)"
I changed it to: ".RightIndent = app.InchesToPoints(-1.02)"
Now it's gone (and that makes sense as all the is required for WORD should be preceded with 'app.')
The error was on the line: ".RightIndent = InchesToPoints(-1.02)"
I changed it to: ".RightIndent = app.InchesToPoints(-1.02)"
Now it's gone (and that makes sense as all the is required for WORD should be preceded with 'app.')
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run WORD code from Excel
Alternatively, you could use
.RightIndent = Application.InchesToPoints(-1.02)
for InchesToPoints is also a method of the Excel Application object.
.RightIndent = Application.InchesToPoints(-1.02)
for InchesToPoints is also a method of the Excel Application object.
Best wishes,
Hans
Hans