excelvba

Accessing either a '.xlsb' or a '.xslx' files for a macro procedure


This is a continuation of my original question: Being able to access and sync the data from 1 workbook to another with VBA

I went for the answer that I marked as solution, but there was one problem. If my source workbook is '.xlsb' and my open copy workbook is '.xlsx', the code starts looking for an open copy workbook '.xlsb' since it was changed and re-stringed to be same as the original workbook.

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
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
Function IsValidExcelWorkbookFormat() As Boolean
    Select Case ActiveWorkbook.FileFormat
        Case xlOpenXMLWorkbook, xlExcel12 '.xlsx または .xlsb
            IsValidExcelWorkbookFormat = True
        Case Else
            IsValidExcelWorkbookFormat = False
    End Select
End Function

My question is, how will I let VBA know that there is an open workbook that can either be a '.xlsx' or a '.xlsb'. And the name of the copy workbook will always have "_CPY", like "SourceWorkbookName_CPY.xls-". What is the best approach for this?


Solution

  • Loop through all opened workbooks to get the instance of the desired one.

    Note: The function returns the first workbook that meets the criteria, in case multiple workbooks match.

    Function GetWK() As Workbook
        Dim WK As Workbook, sExt As String
        Const EXT_LIST = "xlsb|xlsx" ' expected ext. name
        Set GetWK = Nothing
        For Each WK In Application.Workbooks ' loop through all workbooks
            sExt = VBA.LCase(Right(WK.Name, 4)) ' get the last 4 chars
            If InStr(1, EXT_LIST, sExt, vbTextCompare) > 0 And InStr(1, WK.Name, "_CPY", vbTextCompare) > 0 Then
                Set GetWK = WK ' return the instance of WK
                Exit Function
            End If
        Next
    End Function
    
    Sub DEMO()  ' for testing
        Dim objWK As Workbook
        Set objWK = GetWK()
        If objWK Is Nothing Then
            MsgBox "Can't find the source workbook"
        Else
            MsgBox "The source workbook is " & objWK.Name
            ' your code ...
        End If
    End Sub