如何使用 Word 宏从已打开的 Excel 工作簿中获取数据? [英] How to get data from an already opened excel workbook with a Word macro?

问题描述

我目前正在努力使用 vba 宏，你能帮我吗?我会非常感谢任何能帮助我解决这个问题的人.

I am currently struggling with a vba macro, could you help me please? I would be very thankful to anyone who can help me on this.

我想从 Word 文档宏访问 Excel 打开的工作簿的数据.由于某些原因，我需要从已在我的用户会话中打开的 Excel 工作簿中获取数据，而不是使用其路径在后台重新打开它.

I want to access data of an Excel opened workbook from a Word Document macro. For some reasons I need to take data from the Excel workbook which is already opened on my user session and not to reopen it in the background by using its path.

这是我想要转换的当前宏位，以便我使用已经打开的GUI.xls".该窗口的名称是Microsoft Excel – GUI.xls [兼容模式]"我希望我能很好地解释我的问题，我在其他地方没有找到这个主题，如果它已经存在，抱歉.

Here is the current bit of macro I want to transform so that I use the "GUI.xls" which is already opened. The window’s name is "Microsoft Excel – GUI.xls [Compatibility Mode]" I hope I explained well enough my problem, I didn’t find this topic elsewhere, sorry if it already exists.

如果可能，我希望 AppExcel 对象直接链接到打开的工作簿.问题是我尝试从 Word 访问工作簿.

If it is possible I would like that the AppExcel Object is directly linked to the opened Workbook. The problem is that I try to access the Workbook from Word.

非常感谢您的帮助，

西蒙

Set AppExcel = CreateObject("Excel.Application")
AppExcel.Workbooks.Open SourcePath1 & "" & "GUI.xls"

    Data_Name = AppExcel.Worksheets("Output").Cells(RowNum, 2).value
    Data_Ending = AppExcel.Worksheets("Output").Cells(RowNum, 3).value

Dim WordFileName As String
WordFileName = Data_Name & Data_Ending

    Call candp(SourcePath, WordFileName, TargetName)

AppExcel.Activeworkbook.Close savechanges:=False
AppExcel.Quit
Set AppExcel = Nothing

推荐答案

尝试使用GetObject:

Dim WordFileName As String
Dim exWb As Object
Dim AppExcel As Object

'try to get file if it's already open
On Error Resume Next
Set exWb = GetObject(SourcePath1 & "GUI.xls")
On Error GoTo 0

'if it's not already opened - open it
If exWb Is Nothing Then
    Set AppExcel = CreateObject("Excel.Application")
    Set exWb = AppExcel.Workbooks.Open(SourcePath1 & "GUI.xls")
End If

With exWb.Worksheets("Output")
    Data_Name = .Cells(RowNum, 2).Value
    Data_Ending = .Cells(RowNum, 3).Value
End With

WordFileName = Data_Name & Data_Ending

Call candp(SourcePath, WordFileName, TargetName)

exWb.Close savechanges:=False
Set exWb = Nothing

If Not AppExcel Is Nothing Then AppExcel.Quit
Set AppExcel = Nothing

这篇关于如何使用 Word 宏从已打开的 Excel 工作簿中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！