In a continuation of my previous post

I thought I’d provide some sample code to do the same thing in Excel.

Opening a Workbook Via Excel Automation

The code itself is very similar in structure to the Word automation version.

'---------------------------------------------------------------------------------------
' Procedure : Excel_OpenWrkBk
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open an Excel Workbook
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> None required
'             Early Binding -> Microsoft Excel XX.X Object Library
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified path and filename of the Excel Workbook to open
' bReadOnly : Whether the workbook should be opened in read-only more, or not
'
' Usage:
' ~~~~~~
' Call Excel_OpenWrkBk("C:\Temp\Statistics.xlsx", True) -> Opens in read-only mode
' Call Excel_OpenWrkBk("C:\Temp\Statistics.xlsx", False) -> Opens in normal mode
' Call Excel_OpenWrkBk("C:\Temp\Statistics.xlsx") -> Opens in normal mode
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-02-09              Initial Public Release
'---------------------------------------------------------------------------------------
Public Sub Excel_OpenWrkBk(ByVal sFile As String, Optional bReadOnly As Boolean = False)
    On Error GoTo Error_Handler
    #Const Excel_EarlyBind = True    'True => Early Binding / False => Late Binding
    #If Excel_EarlyBind = True Then
        Dim oExcel            As Excel.Application
        Dim oWrkBk            As Excel.Workbook
    #Else
        Dim oExcel            As Object
        Dim oWrkBk            As Object
    #End If

    Set oExcel = CreateObject("Excel.Application")
    Set oWrkBk = oExcel.Workbooks.Open(sFile, , bReadOnly)
    oExcel.Visible = True

Error_Handler_Exit:
    On Error Resume Next
    If Not oWrkBk Is Nothing Then Set oWrkBk = Nothing
    If Not oExcel Is Nothing Then Set oExcel = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Excel_OpenWrkBk" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Usage Examples

Open A Workbook in Read-Only Mode

Call Excel_OpenWrkBk("C:\Temp\Statistics.xlsx", True)

Open A Workbook in Normal Mode

CCall Excel_OpenWrkBk("C:\Temp\Statistics.xlsx")

Or

Call Excel_OpenWrkBk("C:\Temp\Statistics.xlsx", False)

Code Features

This code

• can be used as both Early or Late Binding, the choice is yours and set by changing the value of the Excel_EarlyBind constant.
• is architecture/bitness independent (works in both 32 and 64-bit installations)
• is application independent (should work in any VBA applications – Access, Outlook, PowerPoint, Word, …)

A Few Resources on the Subject

Workbooks.Open method (Excel)

Opens a workbook.