none
Paste Data from Word to Embedded Excel Sheet RRS feed

  • Question

  • Office 2010

    The code from (http://social.msdn.microsoft.com/forums/en-us/isvvba/thread/C4969934-0A4A-4E2C-BB56-CF05F756DC82) is very useful to what I'm working on, but once I get control of the workbook, anything I change the current cells to responds with an error.

    I am writing a Macro to be called from Word 2010 that will access/activate an embedded (not linked or picture) Excel worksheet and paste data from word into the next available row in Column A.

    When I run this code to activate the spreadsheet, which I found at (http://social.msdn.microsoft.com/for...6-CF05F756DC82), the worksheet is activated and data is copied into the specified locations.

    <code>
    Sub WriteToSS()

    Dim objSS As InlineShape

    With ActiveDocument.InlineShapes(1)
    .OLEFormat.DoVerb wdOLEVerbHide
    With .OLEFormat.Object.Application
    With .workbooks(1).worksheets(1)
    .Cells(1, 1).Value = "Hello"
    .Range("B1").Value = "World"
    End With
    .Quit
    End With
    End With

    End Sub

    </code>

    When I run this macro to paste and sort data from Excel, it pastes the data where I want it to go and sorts it correctly. But I need to be able to activate an embedded spreadsheet, not open an Excel sheet and run an Excel macro, so I need help figuring out how to run this type of code for a worksheet embedded in a word 2010 document.

    <code>
    Sub PasteSort()
    '
    ' Macro5 Macro
    '
    ' Goes to the first cell that contains data, A3, and then finds the first blank row
    Application.Goto Reference:="DataCellA3"
    Selection.End(xlDown).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select

    ' Pastes the data from the Word document into the spreadsheet
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
    False

    Dim ws As Worksheet
    Dim SortRange As Range
    Dim LastRow As Long
    Set ws = ThisWorkbook.Worksheets(1)
    With ws
    'Sets the range to be sorted from the last entry in the row to A3 and sorts the data
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    Set SortRange = .Rows("2:" & LastRow)
    SortRange.Sort _
    Header:=xlYes, _
    MatchCase:=True, _
    Key1:=.Range("A1"), _
    Order1:=xlAscending, _
    Key2:=.Range("C1"), _
    Order2:=xlAscending
    End With
    End Sub
    </code>

    When I put this into the With..End With clause that activates the spreadsheet in Word from the first macro I showed above, I get nothing but errors, such as <code>Run-time 91 object variable or with block variable not set</code> or <code>Run-time error 1004 Method Range of object _Global failed</code>. I'm assuming I'm not declaring and instantiating things correctly, but this is my first macro and I've been trying to figure it out for days, so I'd really appreciate any assistance.

    Tuesday, August 28, 2012 7:07 PM

Answers

  • First of all, use With .. End With blocks very carefully.  While they seem nice on the surface, for reducing code bulk, it's actually *very* easy to confuse VBA with With blocks.  I would also highly recommend never using nested With blocks, because in addition to a greater likelihood of confusing VBA, it makes your code harder to read.  For example, in your Excel code snippet, I had to really look back and forth at the With's in order to tell exactly what the ".Quit" call operated on.

    -----

    Any time you're programming in VBA in a given application, the unqualified "Application" object always refers to that application.  Thus, in this case, an unqualified Application reference will always refer to Word, even inside a With block that's scoped to your embedded Excel sheet.  So, any time you want to work in the Excel.Application that's embedded in the Word doc, you always have to explicitly reference that particular Application object.

    I would recommend including the following in your PasteSort() Sub:

    Dim xlApp As Excel.Application  ' as opposed to just As Application
    
    Set xlApp = ActiveDocument.InlineShapes(1).OLEFormat.Object.Application

    Then, any time you use a property or method that's a member of the Excel Application, you can just use

    xlApp.Goto Reference:="DataCellA3"
    xlApp.Selection.End(xlDown).Select
    xlApp.Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
    xlApp.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False 

    As well, it's better to cast all of your Excel objects explicitly:

    Dim ws As Excel.Worksheet
    Dim SortRange As Excel.Range
    
    ' etc.

    It won't matter as much for a Worksheet, since Word doesn't have Worksheets, but both Word and Excel expose Range objects and you don't want to get them confused.

    Finally, I assume you have the Excel Reference selected in the VB Editor?
    • Marked as answer by Writing831 Monday, January 28, 2013 10:50 PM
    Tuesday, September 11, 2012 1:07 PM

All replies

  • You Can Probably Remove the Embedded Sheet Perform the Action and Embed it back.

    Regards,

    ManjunathRV

    Tuesday, September 11, 2012 11:24 AM
  • First of all, use With .. End With blocks very carefully.  While they seem nice on the surface, for reducing code bulk, it's actually *very* easy to confuse VBA with With blocks.  I would also highly recommend never using nested With blocks, because in addition to a greater likelihood of confusing VBA, it makes your code harder to read.  For example, in your Excel code snippet, I had to really look back and forth at the With's in order to tell exactly what the ".Quit" call operated on.

    -----

    Any time you're programming in VBA in a given application, the unqualified "Application" object always refers to that application.  Thus, in this case, an unqualified Application reference will always refer to Word, even inside a With block that's scoped to your embedded Excel sheet.  So, any time you want to work in the Excel.Application that's embedded in the Word doc, you always have to explicitly reference that particular Application object.

    I would recommend including the following in your PasteSort() Sub:

    Dim xlApp As Excel.Application  ' as opposed to just As Application
    
    Set xlApp = ActiveDocument.InlineShapes(1).OLEFormat.Object.Application

    Then, any time you use a property or method that's a member of the Excel Application, you can just use

    xlApp.Goto Reference:="DataCellA3"
    xlApp.Selection.End(xlDown).Select
    xlApp.Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
    xlApp.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False 

    As well, it's better to cast all of your Excel objects explicitly:

    Dim ws As Excel.Worksheet
    Dim SortRange As Excel.Range
    
    ' etc.

    It won't matter as much for a Worksheet, since Word doesn't have Worksheets, but both Word and Excel expose Range objects and you don't want to get them confused.

    Finally, I assume you have the Excel Reference selected in the VB Editor?
    • Marked as answer by Writing831 Monday, January 28, 2013 10:50 PM
    Tuesday, September 11, 2012 1:07 PM