英文:
How can I use VBA to close Workbooks opened in a separate instance of Excel?
问题
我有一个Excel文件,其中运行各种SAP查询,然后将其导出到Excel中,然后在新的Excel实例中打开。我不希望这些文件被打开。不幸的是,似乎没有一个很好的方法告诉SAP停止这样做。我认为一个简单的解决方法是通过在我的原始脚本中添加循环来关闭它打开的约15个文件; 但是,宏似乎具有有限的范围,只能关闭包含在“这个”Excel实例中的文件。
我已经阅读了Stackoverflow和许多旧的论坛,似乎没有人真正知道如何做到这一点。如果这些文件包含在与宏相同的实例中,此代码可以正常工作,但似乎无法影响外部文件。
对于每个PartNumber In PNArray
exportFileName = PartNumber & "_Export.xlsx"
Workbooks(exportFileName).Close SaveChanges:=False
下一个PartNumber
有人有什么巧妙的方法可以关闭这些文件吗?我更喜欢一种有针对性的(按文件名)方法,但也可能只能关闭整个实例。
英文:
I have an Excel file that runs various queries in SAP, which are then exported to Excel and then opened in a new Excel instance. I don't want those files to be open. Unfortunately, there doesn't seem to be a great way to tell SAP to stop doing that. What I thought would be an easy workaround would be to just close the ~15 files it opens by adding a loop to my original script; however, the macro seems to have limited scope and can only close files contained in 'this' instance of Excel.
I've read through Stackoverflow and many older forums and no one really seems to have a way to do this. This code works fine if the files are contained in the same instance as the macro but doesn't seem to reach outside.
For Each PartNumber In PNArray
exportFileName = PartNumber & "_Export.xlsx"
Workbooks(exportFileName).Close SaveChanges:=False
Next PartNumber
Does anyone have any neat tricks for how I can close these files? I'd prefer a targeted (by file name) approach, but might be able to just kill the entire instance instead.
答案1
得分: 1
我有这段代码,它返回所有正在运行的Excel实例的集合。您可以循环遍历每个返回的实例中的工作簿,并决定哪些需要关闭和/或哪些实例需要退出。
来源:https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f
'(32位)
Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
Declare Function FindWindowExA Lib "user32" ( _
ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long
Sub Tester()
Dim col As Collection, xl As Object, wb As Object
Set col = GetExcelInstances()
Debug.Print col.count & " 个 Excel 实例找到"
For Each xl In col
Debug.Print "----------------------"
Debug.Print "实例 " & xl.hwnd & " 工作簿:"
For Each wb In xl.Workbooks
Debug.Print , wb.Name
Next wb
Next xl
End Sub
'返回所有打开的 Excel 实例的集合
Private Function GetExcelInstances() As Collection
Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
guid(0) = &H20400
guid(1) = &H0
guid(2) = &HC0
guid(3) = &H46000000
Dim AlreadyThere As Boolean
Dim xl As Application
Set GetExcelInstances = New Collection
Do
hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
If hwnd = 0 Then Exit Do
hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0, guid(0), acc) = 0 Then
AlreadyThere = False
For Each xl In GetExcelInstances
If xl Is acc.Application Then
AlreadyThere = True
Exit For
End If
Next
If Not AlreadyThere Then
GetExcelInstances.Add acc.Application
End If
End If
Loop
End Function
英文:
I have this bit of code which returns a collection of all running Excel instances. You can loop over the workbooks in each returned instance and decide which ones need to be closed and/or which instances to Quit.
'(32-bit)
Declare Function AccessibleObjectFromWindow Lib "oleacc" ( _
ByVal hwnd As Long, ByVal dwId As Long, riid As Any, ppvObject As Object) As Long
Declare Function FindWindowExA Lib "user32" ( _
ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, ByVal lpszWindow As String) As Long
Sub Tester()
Dim col As Collection, xl As Object, wb As Object
Set col = GetExcelInstances()
Debug.Print col.count & " instance(s) of Excel found"
For Each xl In col
Debug.Print "----------------------"
Debug.Print "Instance " & xl.hwnd & " workbooks:"
For Each wb In xl.Workbooks
Debug.Print , wb.Name
Next wb
Next xl
End Sub
'return a collection of all open Excel instances
Private Function GetExcelInstances() As Collection
Dim guid&(0 To 3), acc As Object, hwnd, hwnd2, hwnd3
guid(0) = &H20400
guid(1) = &H0
guid(2) = &HC0
guid(3) = &H46000000
Dim AlreadyThere As Boolean
Dim xl As Application
Set GetExcelInstances = New Collection
Do
hwnd = FindWindowExA(0, hwnd, "XLMAIN", vbNullString)
If hwnd = 0 Then Exit Do
hwnd2 = FindWindowExA(hwnd, 0, "XLDESK", vbNullString)
hwnd3 = FindWindowExA(hwnd2, 0, "EXCEL7", vbNullString)
If AccessibleObjectFromWindow(hwnd3, &HFFFFFFF0, guid(0), acc) = 0 Then
AlreadyThere = False
For Each xl In GetExcelInstances
If xl Is acc.Application Then
AlreadyThere = True
Exit For
End If
Next
If Not AlreadyThere Then
GetExcelInstances.Add acc.Application
End If
End If
Loop
End Function
答案2
得分: 0
使用这个简单的代码,您可以关闭所有的Excel实例。请注意,包含以下代码的活动工作簿也将被关闭。
Sub CloseAllExcelProcesses()
Dim objExcel As Object
For Each objExcel In GetObject("winmgmts:").ExecQuery("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
objExcel.Terminate
Next objExcel
End Sub
英文:
With a simple code, you can close all instances of Excel. Please note that the active workbook, which contains the following code, will also be closed.
Sub CloseAllExcelProcesses()
Dim objExcel As Object
For Each objExcel In GetObject("winmgmts:").ExecQuery("Select * from Win32_Process Where Name = 'EXCEL.EXE'")
objExcel.Terminate
Next objExcel
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论