vba 如何使用 Word 宏从已打开的 Excel 工作簿中获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22150619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get data from an already opened excel workbook with a Word macro?
提问by Eure4
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.
我目前正在努力使用 vba 宏，你能帮我吗？我会非常感谢任何能帮助我解决这个问题的人。
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.
我想从 Word 文档宏访问 Excel 打开的工作簿的数据。由于某些原因，我需要从已在我的用户会话中打开的 Excel 工作簿中获取数据，而不是使用其路径在后台重新打开它。
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.
这是我想要转换的当前宏位，以便我使用已经打开的“GUI.xls”。窗口的名称是“Microsoft Excel – GUI.xls [兼容模式]”我希望我能很好地解释我的问题，我没有在别处找到这个主题，如果它已经存在，抱歉。
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.
如果可能，我希望 AppExcel 对象直接链接到打开的工作簿。问题是我尝试从 Word 访问工作簿。
Thank you very much for your help,
非常感谢您的帮助，
Simon
西蒙
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
回答by Dmitry Pavliv
Try to use GetObject:
尝试使用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
