VBA如何识别.xlsb/.xlsx格式的_CPY后缀已打开工作簿？

这是此前《实现Excel工作簿间的数据访问与同步（VBA）》问题的延续。我采用了标记为解决方案的代码，但发现一个问题：当源工作簿为.xlsb格式、已打开的副本工作簿为.xlsx格式时，代码会错误地拼接出同格式的.xlsb副本文件名，导致无法找到正确的已打开副本。

原有问题代码 #

SYNC子程序

Private Sub SYNC()

If Not Cells(2, 8).value = "WS_Sales" Then
  End
End If

ORG_BOOK = ActiveWorkbook.Name

Dim last9 As String
Dim ext As String
Dim baseName As String

last9 = Right(ORG_BOOK, 9)
'"\.xlsb"または"\.xlsx"
ext = Right(ORG_BOOK, 5)
'ext = GetFileExtension(ORG_BOOK)

If Not IsValidExcelWorkbookFormat() Then
    MsgBox "拡張子 .xlsbと.xlsx 以外は対応していません。"
    End
End If

If last9 = "_CPY.xlsb" Or last9 = "_CPY.xlsx" Then
    CPY_book = ORG_BOOK
    ORG_BOOK = Left(ORG_BOOK, Len(ORG_BOOK) - 9) & "\." & ext
Else
    CPY_book = Left(ORG_BOOK, Len(ORG_BOOK) - 5) + "_CPY" + ext
End If

Application.ScreenUpdating = False

Workbooks(ORG_BOOK).Activate
VIS
WSC = Worksheets.Count
Workbooks(CPY_book).Activate
VIS
If Not WSC = Worksheets.Count Then
  MsgBox " シート数が一致しません"
  End
End If

'比較処理開始

For i = 1 To WSC
  Workbooks(ORG_BOOK).Activate
  Worksheets(i).Activate

GetFileExtension函数

Function GetFileExtension(fileName As String) As String
    Dim dotPos As Long
    dotPos = InStrRev(fileName, "\.")
    
    If dotPos > 0 Then
        GetFileExtension = Mid(fileName, dotPos)
    Else
        GetFileExtension = ""
    End If
End Function

IsValidExcelWorkbookFormat函数

Function IsValidExcelWorkbookFormat() As Boolean
    Select Case ActiveWorkbook.FileFormat
        Case xlOpenXMLWorkbook, xlExcel12 '.xlsx または .xlsb
            IsValidExcelWorkbookFormat = True
        Case Else
            IsValidExcelWorkbookFormat = False
    End Select
End Function

最优解决方案 #

核心思路是遍历已打开的所有工作簿，匹配包含指定前缀+_CPY的文件名，兼容不同格式后缀，替代原有的文件名拼接逻辑，彻底解决跨格式查找问题。

修改后的完整代码

SYNC子程序

Private Sub SYNC()
    ' 校验触发条件
    If Not Cells(2, 8).Value = "WS_Sales" Then Exit Sub

    Dim orgWB As Workbook
    Set orgWB = ActiveWorkbook
    Dim orgBaseName As String
    
    ' 提取不带后缀和_CPY的源工作簿基础名称
    If Right(orgWB.Name, 9) Like "_CPY.xls?" Then
        orgBaseName = Left(orgWB.Name, Len(orgWB.Name) - 9)
    Else
        orgBaseName = Left(orgWB.Name, InStrRev(orgWB.Name, ".") - 1)
    End If

    ' 校验格式合法性
    If Not IsValidExcelWorkbookFormat(orgWB) Then
        MsgBox "仅支持 .xlsb 和 .xlsx 格式。"
        Exit Sub
    End If

    ' 遍历已打开工作簿，查找匹配的_CPY副本
    Dim cpyWB As Workbook
    Set cpyWB = Nothing
    For Each wb In Workbooks
        If wb.Name Like orgBaseName & "_CPY.xls?" Then
            Set cpyWB = wb
            Exit For
        End If
    Next wb

    ' 未找到副本的提示
    If cpyWB Is Nothing Then
        MsgBox "未找到已打开的副本工作簿（名称需包含" & orgBaseName & "_CPY）。"
        Exit Sub
    End If

    Application.ScreenUpdating = False

    ' 校验工作表数量一致性
    orgWB.Activate
    VIS
    Dim wsc As Integer
    wsc = orgWB.Worksheets.Count
    
    cpyWB.Activate
    VIS
    If Not wsc = cpyWB.Worksheets.Count Then
        MsgBox "工作表数量不一致。"
        Application.ScreenUpdating = True
        Exit Sub
    End If

    ' 执行后续比较同步逻辑（保留原有代码）
    For i = 1 To wsc
        orgWB.Worksheets(i).Activate
        ' ... 此处添加原有比较处理代码
    Next i

    Application.ScreenUpdating = True
End Sub

修改后的格式校验函数

Function IsValidExcelWorkbookFormat(wb As Workbook) As Boolean
    ' 直接传入工作簿对象，避免依赖ActiveWorkbook
    Select Case wb.FileFormat
        Case xlOpenXMLWorkbook, xlExcel12 ' .xlsx 或 .xlsb
            IsValidExcelWorkbookFormat = True
        Case Else
            IsValidExcelWorkbookFormat = False
    End Select
End Function

方案优势

• 跨格式兼容：不管源工作簿是.xlsb还是.xlsx，只要副本名称包含[源名称]_CPY前缀，就能正确识别，不受格式后缀影响
• 查找更准确：直接遍历已打开工作簿，避免文件名拼接带来的格式匹配错误
• 代码更健壮：通过工作簿对象传递参数，减少对ActiveWorkbook的依赖，避免切换激活状态导致的潜在问题

内容的提问来源于stack exchange，提问作者Anpo Desu