如何切换到打开 Excel 电子表格并使用 VBA 从 Word 处理它 [英] How to switch to open Excel spreadsheet and work on it from Word with VBA

问题描述

我只是想使用 VBA 从 Word 处理一个打开的 Excel 电子表格.我对此进行了搜索，并在 如何从 Word 中获取对打开的 Excel 电子表格的引用?.问题是，它似乎打开了一个单独的 Excel 实例，而不是我已经打开的那个，然后在操作后关闭它.如何获得切换到打开的 Excel 电子表格、执行所需操作并保持电子表格打开的过程?

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

感谢您的帮助！

推荐答案

再次感谢 dcromley 之前的回复.与此同时，这是我在互联网上进行了几次搜索后得出的结论.它处理 Excel 文档已经打开的情况.希望这会帮助其他有类似情况的人，尽管它并不详尽(例如，如果打开了多个 Excel 文档).

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

这篇关于如何切换到打开 Excel 电子表格并使用 VBA 从 Word 处理它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！