Run WORD code from Excel

[ErikJan]

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.

[HansV]

Why don't you use Automation to run the code directly from Excel?

[ErikJan]

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?

[HansV]

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:

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

To run this from Excel, you wrap the Word code in Automation code:

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

[ErikJan]

Thank you, busy implementing.

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

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

[HansV]

ActiveWindow is also an Excel object, so without further qualification Excel VBA interprets it as the active Excel window.
Try app.ActiveWindow.

[HansV]

In light of your edit: see what happens if you comment out those lines.

[ErikJan]

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

[HansV]

Perhaps ActiveWindow doesn't work if Word is invisible.

[ErikJan]

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

Code: Select all

    Selection.WholeStory
    With Selection.ParagraphFormat
        .RightIndent = InchesToPoints(-1.02)
        .SpaceBeforeAuto = False
        .SpaceAfterAuto = False
    End With

That doesn't work and creates an error on the first line, so I make it into this:

Code: Select all

    app.Selection.WholeStory
    With app.Selection.ParagraphFormat
        .RightIndent = InchesToPoints(-1.02)
        .SpaceBeforeAuto = False
        .SpaceAfterAuto = False
    End With

Now I get an error on ".RightIndent" and I'm lost...

[HansV]

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.

[ErikJan]

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

[HansV]

Alternatively, you could use

.RightIndent = Application.InchesToPoints(-1.02)

for InchesToPoints is also a method of the Excel Application object.