Problems automating Word from Excel

[Walter Briscoe]

I am running Office 2002.

I have Excel documents which "name" certain pages in a given Word
document. I want the Excel document to cause Word to edit that document
so it only consists of the named pages.
My model for text deletion is selection.extend; selection.gotoend;
selection.delete.
I find that selection.extend does not work.
e.g.
This does not work:
Set oWD = CreateObject("Word.Application")
With oWD
.Visible = True
.Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
.Selection.Extend ' This is ineffective
' .Selection.ExtendMode = True ' This is also ineffective
.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="4"
.Selection.Delete Unit:=wdCharacter, Count:=1
End With

OTOH, this does work:
Set oWD = CreateObject("Word.Application")
With oWD
.Visible = True
.Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
.Run "ZapTOC"
End With

where ZapTOC in foo.doc is set as:
Sub ZapTOC()
Selection.Extend
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="4"
Selection.Delete Unit:=wdCharacter, Count:=1
End Sub

Can anyone here advise on such automation?

 

[GS]

Walter Briscoe formulated on Tuesday :

I am running Office 2002.

I have Excel documents which "name" certain pages in a given Word
document. I want the Excel document to cause Word to edit that document
so it only consists of the named pages.
My model for text deletion is selection.extend; selection.gotoend;
selection.delete.
I find that selection.extend does not work.
e.g.
This does not work:
Set oWD = CreateObject("Word.Application")
With oWD
.Visible = True
.Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
.Selection.Extend ' This is ineffective
' .Selection.ExtendMode = True ' This is also ineffective
.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="4"
.Selection.Delete Unit:=wdCharacter, Count:=1
End With

OTOH, this does work:
Set oWD = CreateObject("Word.Application")
With oWD
.Visible = True
.Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
.Run "ZapTOC"
End With

where ZapTOC in foo.doc is set as:
Sub ZapTOC()
Selection.Extend
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="4"
Selection.Delete Unit:=wdCharacter, Count:=1
End Sub

Can anyone here advise on such automation?

I'm not big on using Word much but a couple of things jump out at me
after reading your post:

1st. You can't modify a doc opened as "ReadOnly" unless you save it
under another filename.

2nd. If you're deleting pages in the doc then you should start at the
last page# to delete and work toward the first page#. Reason is that,
for example, if you want to delete pages 2,4,5 then if you delete p2
first then p4 becomes p3 and p5 becomes p4. Or does it not work that
way? (Assumes page numbering in Word works like row numbering in Excel)

 

[Walter Briscoe]

In message <[email protected]> of Tue, 9 Nov 2010
21:45:43 in microsoft.public.excel.programming, GS

Walter Briscoe formulated on Tuesday :

I am running Office 2002.

I have Excel documents which "name" certain pages in a given Word
document. I want the Excel document to cause Word to edit that document
so it only consists of the named pages.

[snip]

Can anyone here advise on such automation?

I'm not big on using Word much but a couple of things jump out at me
after reading your post:

1st. You can't modify a doc opened as "ReadOnly" unless you save it
under another filename.

I know that.

2nd. If you're deleting pages in the doc then you should start at the
last page# to delete and work toward the first page#. Reason is that,
for example, if you want to delete pages 2,4,5 then if you delete p2
first then p4 becomes p3 and p5 becomes p4. Or does it not work that
way? (Assumes page numbering in Word works like row numbering in Excel)

I am not referring to pages by number.
I NOW have a routine which serves my purpose:
Sub GrabPage(ByVal Scenario As String)
'
' GrabPage Macro
' Macro recorded 09/11/2010 by IBM
'
Dim Pages As Long

' Debug.Assert False ' Uncomment this to step through code
Selection.ExtendMode = True
Selection.Find.ClearFormatting
With Selection.Find
.Text = "[!.]" & Scenario
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
If Selection.Find.Execute Then ' Cater for no pages between sections
Selection.MoveLeft Unit:=wdCharacter, Count:=Len(Scenario)
Selection.Delete Unit:=wdCharacter, Count:=1
End If
Selection.ExtendMode = False
Selection.MoveRight Unit:=wdCharacter, Count:=Len(Scenario)
Select Case Scenario
Case "3.1": Pages = 3
Case "3.2", "3.4": Pages = 2
Case Else: Pages = 1
End Select
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Count:=Pages, Name:=""
End Sub

This is typically called from Excel as in:
oWD.Run "GrabPage", "1.3.3"
oWD.Run "GrabPage", "2.3.1"
oWD.Run "GrabPage", "3.1"
oWD.Run "GrabPage", "4.1"
(The real code uses a loop and I have to skip to the end from the last
selected page.)

As you can see, I find pages by context, rather than by number.

Thanks for your help.

My question remains. In the code beloe, why does extend quietly fail?

Set oWD = CreateObject("Word.Application")
With oWD
.Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
.Selection.Extend ' This is ineffective
' .Selection.ExtendMode = True ' This is also ineffective
End With

I now have code which serves my purpose. Having code in Word rather than
in Excel is flawed design. I also found my Excel code needed constants
such as:
Const wdGoToPage = 1
Const wdGoToNext = 2
Const wdCharacter = 1
Const wdWord = 2
Const wdExtend = 1
because those names are not available to my code in Excel.

I want to drive Word from Excel with Word code in Excel.
I currently drive Word from Excel with Excel calling Word functions.

 

[norie]

In message <[email protected]> of Tue, 9 Nov 2010
21:45:43 in microsoft.public.excel.programming, GS

Walter Briscoe formulated on Tuesday :

I am running Office 2002.
I have Excel documents which "name" certain pages in a given Word
document. I want the Excel document to cause Word to edit that document
so it only consists of the named pages.

[snip]

Can anyone here advise on such automation?

I'm not big on using Word much but a couple of things jump out at me
after reading your post:

1st. You can't modify a doc opened as "ReadOnly" unless you save it
under another filename.

I know that.

2nd. If you're deleting pages in the doc then you should start at the
last page# to delete and work toward the first page#. Reason is that,
for example, if you want to delete pages 2,4,5 then if you delete p2
first then p4 becomes p3 and p5 becomes p4. Or does it not work that
way? (Assumes page numbering in Word works like row numbering in Excel)

I am not referring to pages by number.
I NOW have a routine which serves my purpose:
Sub GrabPage(ByVal Scenario As String)
'
' GrabPage Macro
' Macro recorded 09/11/2010 by IBM
'
    Dim Pages As Long

    ' Debug.Assert False ' Uncomment this to step through code
    Selection.ExtendMode = True
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "[!.]" & Scenario
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    If Selection.Find.Execute Then ' Cater for no pages between sections
        Selection.MoveLeft Unit:=wdCharacter, Count:=Len(Scenario)
        Selection.Delete Unit:=wdCharacter, Count:=1
    End If
    Selection.ExtendMode = False
    Selection.MoveRight Unit:=wdCharacter, Count:=Len(Scenario)
    Select Case Scenario
    Case "3.1": Pages = 3
    Case "3.2", "3.4": Pages = 2
    Case Else: Pages = 1
    End Select
    Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Count:=Pages, Name:=""
End Sub

This is typically called from Excel as in:
            oWD.Run "GrabPage", "1.3.3"
            oWD.Run "GrabPage", "2.3.1"
            oWD.Run "GrabPage", "3.1"
            oWD.Run "GrabPage", "4.1"
(The real code uses a loop and I have to skip to the end from the last
selected page.)

As you can see, I find pages by context, rather than by number.

Thanks for your help.

My question remains. In the code beloe, why does extend quietly fail?

    Set oWD = CreateObject("Word.Application")
    With oWD
        .Documents.Open Filename:="C:\foo.doc", ReadOnly:=True
        .Selection.Extend ' This is ineffective
        ' .Selection.ExtendMode = True ' This is also ineffective
    End With

I now have code which serves my purpose. Having code in Word rather than
in Excel is flawed design. I also found my Excel code needed constants
such as:
    Const wdGoToPage = 1
    Const wdGoToNext = 2
    Const wdCharacter = 1
    Const wdWord = 2
    Const wdExtend = 1
because those names are not available to my code in Excel.

I want to drive Word from Excel with Word code in Excel.
I currently drive Word from Excel with Excel calling Word functions.

Why don't you use bookmarks to identify the pages/sections in the Word
document?

It's pretty easy to delete a bookmark range.

ActiveDocument.BookMarks("BookMark").Range.Delete

I suppose this is similar to your use of Find but might work better -
you shouldn't need to use Selection or Extend.

As for problems with Selection, I'm not really surprised - Selection
in Word can be even more trouble than using it in Excel.

There are all sorts of other things to take into consideration.

In the specific code you've posted the first thing you would need to
think about is what is actually selected if anything.

There doesn't seem to be anything in the code that selects anything.

When you open Word or open a document in Word there might be a default
selection but there might not.

Also if it's a document the selection could be based on what was
selected the last time the document was saved.

 

[Walter Briscoe]

In message <[email protected]
s.com> of Wed, 10 Nov 2010 04:54:51 in microsoft.public.excel.programmin

[snip]

Why don't you use bookmarks to identify the pages/sections in the Word
document?

It's pretty easy to delete a bookmark range.

ActiveDocument.BookMarks("BookMark").Range.Delete

I thought that might be a good idea. I was deleting data between chunks
I wanted to see. With bookmarks, I copied areas I wanted to see to an
output file. I first used the add method. This meant I lost things like
page layout. I then opened two copies of the input file, the first
readonly, the second emptied then populated from the desired bookmarks
and "saved as" to the output file name.

My code is something like this:
With Sheets(SheetName)
Set oWD0 = CreateObject("Word.Application")
oWD0.Visible = True
oWD0.documents.Open Filename:=input, ReadOnly:=True
Set oWD1 = CreateObject("Word.Application")
oWD1.Visible = True
oWD1.documents.Open Filename:=input

' Empty output file
With oWD1.Selection: .EndKey Unit:=wdStory, Extend:=wdExtend: .Delete Unit:=wdCharacter, count:=1: End With

' Copy relevant scenario instructions
For I = 1 To .Columns(3).End(xlDown).row
S = CellToBookMark(.Cells(I, 3))
With oWD0.Selection: .Goto What:=wdGoToBookmark, Name:=S: .Copy: End With

' I can't put the next With on one physical line.
With oWD1.Selection
If I > 1 Then: .InsertBreak Type:=wdPageBreak
.Paste
End With
Next I
oWD0.Quit SaveChanges:=wdDoNotSaveChanges
oWD1.ActiveDocument.SaveAs Filename:=DocXxls(ActiveWorkbook.FullName), _
FileFormat:=wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, _
SaveFormsData:=False, SaveAsAOCELetter:=False
oWD1.Quit SaveChanges:=wdDoNotSaveChanges
End With

I suppose this is similar to your use of Find but might work better -
you shouldn't need to use Selection or Extend.

It works well enough with my data. I manually generated 30 bookmarks. If
I had needed 300, an automatic mechanism would have been needed.

As for problems with Selection, I'm not really surprised - Selection
in Word can be even more trouble than using it in Excel.

Can you expand on that, please?

There are all sorts of other things to take into consideration.

In the specific code you've posted the first thing you would need to
think about is what is actually selected if anything.

There doesn't seem to be anything in the code that selects anything.

There does not need to be. Selection object help says "A selection
represents either a selected (or highlighted) area in the document, or
it represents the insertion point if nothing in the document is
selected." My document always opens at its start.

When you open Word or open a document in Word there might be a default
selection but there might not.

Also if it's a document the selection could be based on what was
selected the last time the document was saved.

I don't find a selection is remembered across saving and opening a
document.

Thanks for your help. I have recoded to use a bookmark technique which
seems simpler than my original code.

 

[norie]

Walter

About the Selection thing, and this could be related to deleting
bookmarks.

If you select something in Word you are selecting everything - text,
formatting, alignment etc

So when you delete the Selection you delete all of that.

Rather than use Selection why not create references to the objects in
the document, just like you would create references to ranges in
Excel.

Why don't you try creating references to the objects, like bookmarks,
in the document?

You might also want to consider using fields.

Also why are you creating 2 instances of Word? You should only need
one.

In fact having 2 instances might prove problematic if you are copying/
pasting between them.

You would have to rely on the clipboard I think.

I'd create one instance of Word and 2 references for the documents you
are opening in it.

Something like this perhaps:

Set wdApp = CreateObject("Word.Application")

wdApp.Visible = True

Set wdDocInput = wdApp.documents.Open (Filename:=input,
ReadOnly:=True)

<b>Set wbDocOutput = wdApp.documents.Open("Filename:=input,
ReadOnly:=True)</b>

Mind you writing that and looking back at your code I'm wondering why
you are opening the same document twice.

If you have a document which has all the text you want and you want to
copy from that to create another document why
not open the source document and then create a new blank document.