Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Edit a Word Doc Embedded in Excel Sheet

Posted on 2011-02-28
15
Medium Priority
?
780 Views
Last Modified: 2012-05-11
I have two word documents  embedded in an Excel worksheet and as part of the embedding process for the second document I open the document  and attempt to edit the document ( I'm trying  to delete page 1) I get the message that the method is not recognized.
Any help would be appreciated
Sub AddCSR()
    Dim FullFile As Variant
    Dim work_book As Workbook
    Dim last_sheet As Worksheet, WS As Worksheet
    Dim OLEWd As OLEObject
    Dim wdBM As Bookmark
    Dim WD As Word.Document
  
    Application.ScreenUpdating = False
    Set work_book = Application.ActiveWorkbook
    Set last_sheet = work_book.Sheets(work_book.Sheets.Count)
    Set WS = work_book.Sheets.Add(After:=last_sheet)
    
    WS.Name = "CSR1"
    
    FullFile = Application.GetOpenFilename _
    (" WORD files(*.doc),*doc", 1, "SELECT and OPEN the CSR1 File", , False)
    
    If VarType(FullFile) = vbBoolean Then
        MsgBox "No File Specified", vbExclamation
        Exit Sub
    End If
    
    With OLEWd
        Set OLEWd = ActiveSheet.OLEObjects.Add(Filename:=FullFile, Link:=False, DisplayAsIcon:=False)
        'OLEWd.Verb xlVerbOpen
        OLEWd.Name = "CSR1"
        OLEWd.Width = 400
        OLEWd.Height = 800
        OLEWd.Top = 30
        OLEWd.Left = 0
        Set WD = OLEWd.Object
    End With
    
    With OLEWd
        Set OLEWd = ActiveSheet.OLEObjects.Add(Filename:=FullFile, Link:=False, DisplayAsIcon:=False)
        OLEWd.Verb xlVerbOpen
        Selection.GoTo What:=wdGoToBookmark, Name:="Text26"
    Selection.Bookmarks(wdBM).Delete
    Selection.Bookmarks("\Page").Range.Delete
        OLEWd.Name = "CSR2"
        OLEWd.Width = 400
        OLEWd.Height = 800
        OLEWd.Left = 500
        OLEWd.Top = 30
        Set WD = OLEWd.Object
    End With
    Set WS = Nothing
    Set FullFile = Nothing
    Set OLEWd = Nothing
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:llawrenceg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35003186
It might be confusing the Excel and the Word Selection objects.

Also Selection.Bookmarks(wdBM).Delete looks wrong. wdBM is decalred as a bookmark, but it needs to be a bookmark name.

Try this


'...   
      With OLEWd
        Set OLEWd = ActiveSheet.OLEObjects.Add(Filename:=FullFile, Link:=False, DisplayAsIcon:=False)
        OLEWd.Verb xlVerbOpen
        WD.Application.Selection.GoTo What:=wdGoToBookmark, Name:="Text26"
        WD.Application.Selection.Bookmarks("\Page").Range.Delete
        OLEWd.Name = "CSR2"
        OLEWd.Width = 400
'...

Open in new window

0
 

Author Comment

by:llawrenceg
ID: 35007831
Graham:
I get a runtime error 4605 " This command is not available" on
WD.Application.Selection.Bookmarks("\Page").Range.Delete
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35009595
I have now tested the whole of the code and can get this to work.

There are a couple of variable name changes and soem extra code that you might not need when it is fully running.

I think that the main problem was that your document object variable (WD) was set from the first instance of the OLEwd object, but you then recreated OLEwd, but tried to use WD again.
Option Explicit

Sub AddCSR()
    Dim FullFile As Variant
    Dim work_book As Workbook
    Dim last_sheet As Worksheet, WS As Worksheet
    Dim OLEWd As OLEObject
    'Dim wdBM As Bookmark
    Dim wdDoc As Word.Document
    Dim wdApp As Word.Application
    
    Application.ScreenUpdating = False
    
    Set work_book = Application.ActiveWorkbook
    Set last_sheet = work_book.Sheets(work_book.Sheets.Count)
    For Each WS In work_book.Worksheets
        If WS.Name = "CSR1" Then
            WS.Delete
        End If
    Next WS
    Set WS = work_book.Sheets.Add(After:=last_sheet)
    
    WS.Name = "CSR1"
    
    FullFile = Application.GetOpenFilename _
    (" WORD files(*.doc),*doc", 1, "SELECT and OPEN the CSR1 File", , False)
    
    If VarType(FullFile) = vbBoolean Then
        MsgBox "No File Specified", vbExclamation
        Exit Sub
    End If
    
    Set OLEWd = ActiveSheet.OLEObjects.Add(Filename:=FullFile) ', Link:=False, DisplayAsIcon:=False)
        With OLEWd
        'OLEWd.Verb xlVerbOpen
        OLEWd.Name = "CSR1"
        OLEWd.Width = 400
        OLEWd.Height = 800
        OLEWd.Top = 30
        OLEWd.Left = 0
     End With
    
    Set OLEWd = ActiveSheet.OLEObjects.Add(Filename:=FullFile, Link:=False, DisplayAsIcon:=False)
        With OLEWd
        OLEWd.Verb xlVerbOpen
        Set wdDoc = OLEWd.Object
        If wdDoc.Bookmarks.Exists("Text26") Then
            wdDoc.Application.Selection.GoTo What:=wdGoToBookmark, Name:="Text26"
            'Selection.Bookmarks(wdBM).Delete
            wdDoc.Application.Selection.Bookmarks("\Page").Range.Delete
        Else
            MsgBox "Bookmark missing"
        End If
        OLEWd.Name = "CSR2"
        OLEWd.Width = 400
        OLEWd.Height = 800
        OLEWd.Left = 500
        OLEWd.Top = 30
    End With
    Set WS = Nothing
    Set FullFile = Nothing
    Set OLEWd = Nothing
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:llawrenceg
ID: 35012420
I get the same eror message. This command is not available
            wdDoc.Application.Selection.Bookmarks("\Page").Range.Delete
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35012761
That line does not appear in my latest revision of your code. That version tests for the presence of the bookmark. If it exists, the VBA code deletes the page that contains the bookmark. Otherwise the code reports that the bookmark is missing.
0
 

Author Comment

by:llawrenceg
ID: 35017060
"wdDoc.Application.Selection.Bookmarks("\Page").Range.Delete"
is the line of code giving me a problem as  the message says this command is not available
Did you have a different version from the one just up above. That is the one I copied
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35018611
Sorry, I was looking at an intermediate version.

I'll check it again.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35022518
I can't reproduce the problem with my own Word document. Can you post the document (edited if necessary)?
0
 

Author Comment

by:llawrenceg
ID: 35101154
Sorry< I have been out with the flu for the past week and apologize for a lack of response
0
 

Author Comment

by:llawrenceg
ID: 35131121
I'm attachng a copy of the document
CSR1-for-fiscal-plans.doc
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35132019
I note that there is no bookmark called Text26, so no deletion occurs, and the message box reports that. There is no VB error message.
0
 

Author Comment

by:llawrenceg
ID: 35147269
My mistake. It should have been "Text41"
0
 

Author Comment

by:llawrenceg
ID: 35147372
I get the same eror message. This command is not available
            wdDoc.Application.Selection.Bookmarks("\Page").Range.Delete
0
 

Accepted Solution

by:
llawrenceg earned 0 total points
ID: 35305762
I figured it out. First , you can not delete a "page" in Word per se. but if you set a bookmark  or section break at he end of the page, you can delete to there.

Here are several options:

Sub DeleteToEndOfPage()
    Dim myRange As Range
    Set myRange = Selection.Range
    myRange.End = ActiveDocument.Bookmarks("\page").Range.End
    myRange.Delete
End Sub

Sub DeleteToEndOfDocument()
    Dim myRange As Range
    Set myRange = Selection.Range
    myRange.End = ActiveDocument.Range.End
    myRange.Delete
End Sub

Sub DelBookmarks()
    Dim wdBM As Bookmark
   
For Each wdBM In ActiveDocument.Bookmarks
    If wdBM Like "BM*" Then  ' BM is a BM name
    Selection.GoTo What:=wdGoToBookmark, Name:=wdBM
    Selection.Bookmarks(wdBM).Delete
    Selection.Bookmarks("\Page").Range.Delete
    End If
   
Next
End Sub

0
 

Author Closing Comment

by:llawrenceg
ID: 35339946
I discovered my own answer
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question