How to switch to open Excel spreadsheet and work on it from Word with VBA

Question

I'm just trying to work on an open Excel spreadsheet from Word using VBA. I did a search on this and found the following code on How to get reference to an open Excel spreadsheet from Word?. The problem is, it seems to open a separate instance of Excel rather than the one I already have open, and then closes it after the action. How can I get the procedure to switch to the open Excel spreadsheet, perform the desired actions, and leave the spreadsheet open?

   Sub DoStuffWithExcelInWord()
       Dim xl As Excel.Application
       Dim wkbk As Excel.Workbook
       Dim wk As Excel.Worksheet
       Set xl = CreateObject("Excel.Application")
       Set wkbk = xl.Workbooks.Open("C:\test.csv")
       Set wk = wkbk.Sheets(1)

       Debug.Print wk.Cells(1, 1).Value 'Here's where I would like to insert my code

       xl.Quit
       Set wk = Nothing
       Set wkbk = Nothing
       Set xl = Nothing
   End Sub

Thanks for any assistance!

KLJKevin · Accepted Answer · 2014-06-13T16:10:40.993

Thanks again to dcromley for his earlier response. In the meantime, here's what I came up after a couple more searches on the Internet. It takes care of the situation where the Excel document is already open. Hopefully that will help others with similar situations, although it's not exhaustive (for example, if several Excel documents are open).

Sub DoStuffWithExcelInWordRevised()
    Dim xl As Excel.Application
    Dim wkbk As Excel.Workbook
    Dim wk As Excel.Worksheet

    On Error Resume Next
    Set xl = GetObject(, "Excel.Application")
    On Error GoTo 0

    If xl Is Nothing Then
        Set xl = CreateObject("Excel.Application")
        Set wkbk = xl.Workbooks.Open("C:\test.xlsx")
        Set wk = wkbk.Sheets(1)
    End If

    xl.Visible = True

    With xl
        ' Insert Excel code here
    End With

    Set wk = Nothing
    Set wkbk = Nothing
    Set xl = Nothing
End Sub

Thanks, Sean. Your answer was correct. Looks like we posted at the same time, so I didn't see your post before adding my answer. — KLJKevin, Jun 13 '14 at 15:08
anyone get the "user defined type not defined" error while attempting to run this? — user32882, Aug 17 '15 at 00:35

score 0 · Answer 2 · answered Jun 11 '14 at 19:15

0

Try:
Add xl.Visible = True
Del xl.Quit

answered Jun 11 '14 at 19:15

dcromley

1,373
1
8
23

Thanks, that works partially, however I still have the same problem that the code opens a separate instance of the Excel document in read-only. I just keep wanting to interface with the same open Excel document (going back and forth to Word). – KLJKevin Jun 12 '14 at 20:15

score 0 · Answer 3 · answered Jun 13 '14 at 00:50

0

It appears that you are trying to do this with a Workbook that is already open. Since you want to use the open workbook / application then you need to:

Set xl = GetObject(,"Excel.Application")

answered Jun 13 '14 at 00:50

Sean W.

863
5
14

Thanks, Sean. Your answer was correct. Looks like we posted at the same time, so I didn't see your post before adding my answer. – KLJKevin Jun 13 '14 at 15:13

How to switch to open Excel spreadsheet and work on it from Word with VBA

3 Answers3

Linked