excel - 仅打开指定的工作簿

我每天运行这段代码会影响目录中的每个 .xlsx 工作簿，但是如果文件名不在“好的”数组中，我怎么能忽略工作簿？

这是我当前的语法:

Option Explicit
Public Sub OpenExcelInDir()
Dim MyFolder As String
Dim MyFile As String
    MyFolder = "C:\ExcelSheets"
    MyFile = Dir(MyFolder & "\*.xlsx")
    Do While MyFile <> ""
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
        MyFile = Dir
        Call UpdateAllSheets
    Loop
End Sub

现在我想改变它，以便我可以声明一个字符串数组，并且只打开数组中的工作簿，如下所示:

Dim goodWB() As String
goodWB= Split("ABC123,DEF456,GHI789", ",")

最佳答案

我认为这将满足您的要求。我添加了一行来测试文件名是否与数组中的一个匹配。

已测试

Option Explicit
Public Sub OpenExcelInDir()
Dim goodWB() As String
goodWB = Split("ABC123.xlsx,DEF456.xlsx,GHI789.xlsx", ",")
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\ExcelSheets"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
    If IsInArray(MyFile, goodWB) Then
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
        MyFile = Dir
        Call UpdateAllSheets
    End If
    MyFile = Dir
Loop
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

关于excel - 仅打开指定的工作簿，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34252987/