I have been looking for way to code that I can open a workbook and access its contents with a macro. The workbook can either be '.xlsb' or '.xslx'.
ORG_BOOK = ActiveWorkbook.Name
If Not Right(ORG_BOOK, 5) = ".xlsb" Or Right(ORG_BOOK, 5) = ".xslx" Then
MsgBox "Please open a '.xlsb' or '.xlsx' file."
End
End If
If Right(ORG_BOOK, 9) = "_CPY.xlsb" Then
CPY_book = ORG_BOOK
ORG_BOOK = Left(ORG_BOOK, Len(ORG_BOOK) - 9) + ".xlsb"
Else
CPY_book = Left(ORG_BOOK, Len(ORG_BOOK) - 5) + "_CPY" + ".xlsb"
End If
WC = Workbooks.Count
O_STUS = 0
For i = 1 To WC
If Workbooks(i).Name = ORG_BOOK Then O_STUS = O_STUS + 1
If Workbooks(i).Name = CPY_book Then O_STUS = O_STUS + 1
Next i
If Not O_STUS = 2 Then
MsgBox "Please open both workbooks to sync."
End
End If
Application.ScreenUpdating = False
Workbooks(ORG_BOOK).Activate
WSC = Worksheets.Count
Workbooks(CPY_book).Activate
For now, this works with '.xlsb' files, but I need it for also work on '.xlsx' files. What would be the best way to tackle this?
For such logic we need to extract some values and inspect their extensions as well as last part of the name.
Please see below code snippets with inline comments for clarity:
Dim last9 As String
Dim ext As String
Dim baseName As String
last9 = Right(ORG_BOOK, 9)
' Extract the extension from last9, e.g., ".xlsb" or ".xlsm"
ext = LCase(Right(ORG_BOOK, 5))
If Not (ext = ".xlsb" Or ext = ".xlsx") Then
MsgBox "Please open a '.xlsb' or '.xlsx' file."
End
End If
If last9 = "_CPY.xlsb" Or last9 = "_CPY.xlsm" 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
To make your code even better, you could use such utility function to extract extensions:
Function GetFileExtension(fileName As String) As String
Dim dotPos As Long
dotPos = InStrRev(fileName, ".")
If dotPos > 0 Then
GetFileExtension = LCase(Mid(fileName, dotPos))
Else
' No extension found
GetFileExtension = ""
End If
End Function
Then you could use it like:
ext = GetFileExtension(ORG_BOOK)
P.S. Accordingly to comments, you could improve checking of the file type, too:
Function IsValidExcelWorkbookFormat() As Boolean
Select Case ActiveWorkbook.FileFormat
Case xlOpenXMLWorkbook, xlExcel12 ' .xlsx or .xlsb
IsValidExcelWorkbookFormat = True
Case Else
IsValidExcelWorkbookFormat = False
End Select
End Function
and use it like
If Not IsValidExcelWorkbookFormat() Then
MsgBox "Please open a '.xlsb' or '.xlsx' file."
End
End If