如何使用VBA关闭在单独的Excel实例中打开的工作簿?

huangapple go评论127阅读模式
英文:

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.

Source: https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f

'(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

huangapple
  • 本文由 发表于 2023年7月14日 04:46:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76683132.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定