vba - 使用 Excel vba 在源书中进行更改后检查工作簿是否打开

Question

我制作了一个宏，可以将数据从一本书传输到另一本书。如果源书没有打开宏打开它，复制数据然后关闭它，这样用户就看不到发生了什么。现在的问题 - 如果源书已经打开，它需要在操作后保持打开状态。所以，我做了一个 If 语句，它使用了我在这里找到的这个函数:

Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0:    IsWorkBookOpen = False
Case 70:   IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

它第一次工作，但如果我在第一次之后再次启动宏，它就像文件没有打开一样，并在完成操作时关闭它......

If IsWorkBookOpen(ActiveSheet.Range("AA1").Value) Then

Set Invoice = Workbooks(ActiveSheet.Range("AA4").Value)
openF = True

Else
openF = False

Set Invoice = Workbooks.Open(ActiveSheet.Range("AA1").Value, True, True)
End If

For Each sheet In Invoice.Worksheets
If sheet.Range("B2").Value = "active" Then
    newD = vbNull
    lRow = sheet.Cells(2000, 7).End(xlUp).Row
    counter = sheet.Cells(2000, 1).End(xlUp).Row

totalR = num

For k = 7 To counter

        If sheet.Cells(k, 6).Value = "n" Then

            sheet.Range("A" & k, "F" & k).Copy
            rep.Range("A" & num, "F" & num).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

            rep.Cells(num, 7) = newD
            If Year(rep.Cells(num, 7).Value) = Year(1) Then rep.Cells(num, 7).Clear
            newD = DateAdd("d", sheet.Cells(3, 2).Value, newD)
            num = num + 1
        End If

Next k

rep.Cells(num, 4) = "Total"
rep.Cells(num, 5) = Application.Sum(rep.Range("E" & totalR, "E" & (num - 1)))
rep.Cells(num, 5).NumberFormat = "$#,###0"

num = num + 3

End If

Next sheet

If openF = False Then
    Invoice.Close False
    Set Invoice = Nothing
End If
Application.ScreenUpdating = True

问题是:第一轮宏代码是否会以某种方式改变源书的状态或名称，以使函数不会将其视为“打开”？

Answer 1

您可以通过遍历工作簿并检查名称来检查文件是否在 Excel 中打开。

Public Function IsWorkbookOpen(WorkbookName) As Boolean
    Dim i As Long

    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = WorkbookName Then
            IsWorkbookOpen = True
            Exit Function
        End If
    Next i
End Function