none
Activating Embedded Worksheet in Word 2010: How do I select cells in sheet with macro? RRS feed

  • Question

  • I am using this code to open a sheet embedded in a word 2010 document.  I cannot figure out how to initialize variables, etc., so that I can move to the cell I want to go to (next open cell in Column A) and paste data.  I am consistently getting this error:

    Run-time error '1004': Select method of Range class failed with this code. 

    Other incarnations have resulted in

    Run-time error '1004' Method 'Worksheets' of object '_Global' failed

    Would really appreciate help with getting this working.

    'Language is VBA, which is not a choice from the menu
    Sub ThuMacro2()
    '
    ' ThuMacro2 Macro
    '
    '
    Selection.GoTo What:=wdGoToBookmark, Name:="HIDTable"
        With ActiveDocument.Bookmarks
            .DefaultSorting = wdSortByName
            .ShowHidden = False
        End With
        Selection.Copy
        
    Dim oWB As Workbook
    Dim oWS As Worksheet
    Dim oIShape As InlineShape
    For Each oIShape In ActiveDocument.InlineShapes
        If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
            oIShape.OLEFormat.Activate
            
            Set oWB = oIShape.OLEFormat.Object
            Set oWS = oWB.Sheets("Sheet1")
            
            
                oWB.Activate
                oWS.Activate
                oWS.Visible = True
        
                ' From Here
                oWS.Range("A3:A5").Select
                oWS.Range("A3").Value = "Frustrated"
                oWS.Range("A3").End(xlDown).Select
                oWS.ActiveCell.Offset(1, 0).Select
                oWS.PasteSpecial Format:="Text", _ 
                  Link:=False, DisplayAsIcon:= _
                  False
                ' To Here
            
        End If
    Next oIShape
    End Sub


    Debbie B Carson

    Thursday, August 30, 2012 7:50 PM

Answers

  • Hi Debbie

    If I make the following changes to your code, it works. I don't know why the Select method doesn't work, but I suspect it has something to do with OLE Automation.

                ' From Here
                'oWS.Range("A3:A5").Select
                oWS.Range("A3").Value = "Frustrated"
                'oWS.Range("A3").End(xlDown).Select
                Dim rngUsed As Excel.Range
                Set rngUsed = oWS.UsedRange
                Dim rngAfterUsed As Excel.Range
                Set rngAfterUsed = oWS.Range("A" & rngUsed.Rows.Count + 1)
                rngAfterUsed.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
                'oWS.ActiveCell.Offset(1, 0).Select
                'oWS.PasteSpecial Format:="Text", _
                  Link:=False, DisplayAsIcon:= _
                  False
                ' To Here


    Cindy Meister, VSTO/Word MVP

    • Proposed as answer by Leo_GaoModerator Wednesday, September 5, 2012 1:36 AM
    • Marked as answer by Writing831 Wednesday, September 5, 2012 4:18 PM
    Friday, August 31, 2012 12:49 PM
    Moderator

All replies

  • I am using this code to open a sheet embedded in a word 2010 document.  I cannot figure out how to initialize variables, etc., so that I can move to the cell I want to go to (next open cell in Column A) and paste data.  I am consistently getting this error:

    Run-time error '1004': Select method of Range class failed with this code. 

    Other incarnations have resulted in

    Run-time error '1004' Method 'Worksheets' of object '_Global' failed

    Would really appreciate help with getting this working.

    'Language is VBA, which is not a choice from the menu
    Sub ThuMacro2()
    '
    ' ThuMacro2 Macro
    '
    '
    Selection.GoTo What:=wdGoToBookmark, Name:="HIDTable"
        With ActiveDocument.Bookmarks
            .DefaultSorting = wdSortByName
            .ShowHidden = False
        End With
        Selection.Copy
        
    Dim oWB As Workbook
    Dim oWS As Worksheet
    Dim oIShape As InlineShape
    For Each oIShape In ActiveDocument.InlineShapes
        If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
            oIShape.OLEFormat.Activate
            
            Set oWB = oIShape.OLEFormat.Object
            Set oWS = oWB.Sheets("Sheet1")
            
            
                oWB.Activate
                oWS.Activate
                oWS.Visible = True
        
                ' From Here
                oWS.Range("A3:A5").Select
                oWS.Range("A3").Value = "Frustrated"
                oWS.Range("A3").End(xlDown).Select
                oWS.ActiveCell.Offset(1, 0).Select
                oWS.PasteSpecial Format:="Text", _ 
                  Link:=False, DisplayAsIcon:= _
                  False
                ' To Here
            
        End If
    Next oIShape
    End Sub


    Debbie B Carson

    • Moved by Youen Zen Monday, September 3, 2012 5:21 AM To provide better support (From:Visual Basic General)
    • Merged by Cindy Meister MVPModerator Monday, September 3, 2012 9:18 AM same question
    Thursday, August 30, 2012 7:45 PM
  • Hi Debbie

    If I make the following changes to your code, it works. I don't know why the Select method doesn't work, but I suspect it has something to do with OLE Automation.

                ' From Here
                'oWS.Range("A3:A5").Select
                oWS.Range("A3").Value = "Frustrated"
                'oWS.Range("A3").End(xlDown).Select
                Dim rngUsed As Excel.Range
                Set rngUsed = oWS.UsedRange
                Dim rngAfterUsed As Excel.Range
                Set rngAfterUsed = oWS.Range("A" & rngUsed.Rows.Count + 1)
                rngAfterUsed.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
                'oWS.ActiveCell.Offset(1, 0).Select
                'oWS.PasteSpecial Format:="Text", _
                  Link:=False, DisplayAsIcon:= _
                  False
                ' To Here


    Cindy Meister, VSTO/Word MVP

    • Proposed as answer by Leo_GaoModerator Wednesday, September 5, 2012 1:36 AM
    • Marked as answer by Writing831 Wednesday, September 5, 2012 4:18 PM
    Friday, August 31, 2012 12:49 PM
    Moderator
  • Perfect!  Thank you so much!

    Debbie B Carson

    Friday, August 31, 2012 4:36 PM
  • This was great, but now I realize that I need to have the table length change to display all of the newly inserted rows.  Is there a way to do this in a macro?  In English, it would be

    PasteNewData

    Display All Rows, don't change width

    Thanks so much!


    Debbie B Carson

    Friday, August 31, 2012 7:52 PM
  • Hi Debbie

    I'm afraid there's no way to change the size of the "grid" the embedded OLE "window" exposes in the document. That's been a pain since the feature was new - it can only be changed by dragging with the mouse.

    The only work-around I've ever found has been to maintain the spreadsheet as a separate file, then insert it anew each time there's a change that affects the numbers of rows/columns to be displayed. When Words inserts a table from an existing spreadsheet it automatically checks the "used range" and makes the "window" the right size to show that data.


    Cindy Meister, VSTO/Word MVP

    Saturday, September 1, 2012 8:54 AM
    Moderator
  • Hi Debbie,

    This topic is relevant to word develop and we have a forum for this:http://social.msdn.microsoft.com/Forums/en-US/worddev/threads. In order to provide better support, I will move this thread.

    Thanks for understanding!

    Best regards,


    Shanks Zen
    MSDN Community Support | Feedback to us

    Monday, September 3, 2012 5:18 AM