如何使用 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屋!