excelvbaworksheet

Being able to access and sync the data from 1 workbook to another with VBA


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?


Solution

  • 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