问存储所有当前打开的excel工作簿，然后再打开它。
EN

Stack Overflow用户

提问于 2015-06-12 01:53:35

回答 2查看 393关注 0票数 1

我目前所面对的问题是：

1.将当前打开的所有excel工作簿存储在数组中。

2.保存和关闭工作簿

3.打开所有打开的工作簿

4.重点放在特定的工作簿上

我现在的代码是：

For Each wb In Application.Workbooks

wb.Save

Next wb

工作如预期，但我的不同的excel工作簿保持‘闪烁’，这是一种刺激，因此需要保存和关闭所有。

我确实理解，要回到特定的工作簿上，可以使用activate函数。如果我在“每个循环”中做一个集合数组，它将不能工作，因为它将变成一个双for循环。

由于我是VBA的新手，我非常感谢你们的任何意见。

谢谢!

vba

excel

云点播特惠1元起

提供制作上传、存储、转码、媒体处理、媒体 AI、加速分发播放、版权保护等一体化的高品质媒体服务

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-06-12 02:51:55

在这段代码中，我给出了两种不同的选择。使用集合或数组。当数组需要一个For..Next循环时，您可以为collection循环中的每个项逐步遍历一个集合。

Sub All_OpenWorkBooks_Collection()

    Dim wrkBk As Workbook

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Add to a collection                                 '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim vItem As Variant
    Dim colWorkBooks As Collection
    Set colWorkBooks = New Collection

    For Each wrkBk In Workbooks
        If wrkBk.Name <> ThisWorkbook.Name Then
            colWorkBooks.Add wrkBk.FullName
            wrkBk.Close SaveChanges:=True
        End If
    Next wrkBk
    Set wrkBk = Nothing

    For Each vItem In colWorkBooks
        Workbooks.Open (vItem)
    Next vItem

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Set a reference to a specific workbook - can then use wrkBk to refer to it. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set wrkBk = Workbooks("Copy (4) of New Microsoft Excel Worksheet.xlsx")
    wrkBk.Activate

End Sub

    '------------------------------------------------------------------------

Sub All_OpenWorkbooks_Array()

    Dim wrkBk As Workbook

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Add to an array.                                      '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim x As Long
    Dim arrWrkBk() As Variant
    ReDim arrWrkBk(1 To Workbooks.Count)

    For x = Workbooks.Count To 1 Step -1
        If Workbooks(x).Name <> ThisWorkbook.Name Then
            arrWrkBk(x) = Workbooks(x).FullName
            Workbooks(x).Close SaveChanges:=True
        End If
    Next x

    For x = 1 To UBound(arrWrkBk)
        If arrWrkBk(x) <> "" Then
            Workbooks.Open (arrWrkBk(x))
        End If
    Next x
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Set a reference to a specific workbook - can then use wrkBk to refer to it. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set wrkBk = Workbooks("Copy (4) of New Microsoft Excel Worksheet.xlsx")
    wrkBk.Activate

End Sub