VBA code to open workbook

The behavior when you open a workbook depends on both global excel settings and arguments chosen when saving the excel file using :

Whether a password needs to be specified.
Whether a message appears when the file is opened, recommending that the file be opened as read-only - see also Workbook.ReadOnlyRecommended property.
Whether external references (links) in the file, such as the reference to a range in another workbook, are to be updated - see also Workbook.UpdateLinks property.

An example of the VBA code te open an Excel file which requires ca password and contains links that need to be updated:


Dim strFilename As String: strFilename = "C:\temp\book24.xslx"
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=strFilename, Password:="hi123", UpdateLinks:=3)

Below sections discuss the arguments you can use to control how Excel files are opened: UpdateLinks, ReadOnly, Password, IgnoreReadOnlyRecommended, arguments for opening a text file or a Excel template, AddToMru, Converter, Local, CorruptLoad.

Note

Note
If you run code to open one or more files using the Workbooks.Open command, as soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are editing an email message in Outlook whilst Excel VBA is processing the code in the background. To prevent this from happening precede the call to the Workbooks.Open methods by `Do While IsShiftKeyDown() DoEvents Loop`

If you run code to open one or more files using the Workbooks.Open command, as soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are editing an email message in Outlook whilst Excel VBA is processing the code in the background. To prevent this from happening precede the call to the Workbooks.Open methods by


Do While IsShiftKeyDown() 
DoEvents 
Loop

Filename

The file name of the workbook to be opened.

UpdateLinks

Specifies the way external references (links) in the file such as the reference to a range in the Budget.xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25) are updated. You can specify one of the following values in the UpdateLinks parameter to determine whether external references (links) are updated when the workbook is opened.

If UpdateLinks is omitted the user is prompted to specify how links will be updated.

UpdateLinks:=0 - External references (links) will not be updated when the workbook is opened.
UpdateLinks:=3 - External references (links) will be updated when the workbook is opened.

An example of code inserted for opening an excel file without updating links is below.

using code vba to insert the code for opening an excel file without updating links


Set wbOpen = Workbooks.Open(Filename:=strFilename, UpdateLinks:=0)

Note on Charting
If Microsoft Excel is opening a file in the WKS WK1 or WK3 format and the Updatelinks argument is 0 no charts are created; otherwise Microsoft Excel generates charts from the graphs attached to the file.

ReadOnly

True to open the workbook in read-only mode.

Password arguments

There are two Password arguments with different purposes. In both cases, if this argument is omitted and the workbook requires a password the user will be prompted for the password.

Password

The Password argument contains the password required to open a protected workbook.

WriteResPassword:

The WriteResPassword argument contains the password required to write to a write-reserved workbook.

IgnoreReadOnlyRecommended

Make IgnoreReadOnlyRecommended:=True to prevent Microsoft Excel from showing the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Arguments for opening a text file

Format

If Microsoft Excel opens a text file the Format argument specifies the delimiter character. If this argument is omitted the current delimiter is used.For more information about the values used by this parameter see the Remarks section.

Origin (Windows or Mac)

Origin may only have one of the following XlPlatform constants: xlMacintosh, xlWindows or xlMSDOS.If this argument is omitted the current operating system is used.

Delimiter

If the file is a text file and the Format argument is 6 this argument is a string that specifies the character to be used as the delimiter. For example use Chr(9) for tabs use for commas use ; for semicolons or use a custom character.Only the first character of the string is used.

Editable

If the file is an Excel template add argument Editable:=True to open the specified template for editing. The default value is False which opens a new workbook based on the specified template.

Notify

If the file cannot be opened in read/write mode this argument is True to add the file to the file notification list. Excel will open the file as read-only poll the file notification list and then notify the user when the file becomes available. If this argument is False or omitted no notification is requested and any attempts to open an unavailable file will fail.

Converter

The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter does not recognize the file all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

Add to Most Resently Used files list (AddToMru)

To add this workbook to the list of recently used files add argument AddToMru:=True

Local

True saves files against the language of Microsoft Excel (including control panel settings).False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically United States English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad

Can be one of the following constants: xlNormalLoad xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad and does not attempt recovery when initiated through the OM.

Application.AutomationSecurity

You can change the security mode Microsoft Excel uses when programmatically opening files. As an example msoAutomationSecurityForceDisable disables all macros in all files opened programmatically without showing any security alerts.

Workbook_Open event

The Workbook_Open event occurs when the workbook is opened.

Below code maximizes Microsoft Excel whenever the workbook is opened. You will place such code un the ThisWorkbook class module.


Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

Test if workbook is open (function)

You can test if a workbook is open by checking if the Name of the workbook is in the Workbooks collection, see Function WorkbookIsOpen below.


Function WorkbookIsOpen below.

Sub ExampleTestWorkbookIsOpen()
    Dim strwbname As String: strwbname = "Book1.xlsx"
    Dim booWorkbookIsOpen As Boolean
    booWorkbookIsOpen = WorkbookIsOpen(strwbname)
End Sub

Public Function WorkbookIsOpen(wbname As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(wbname)
    If Err = 0 Then
        WorkbookIsOpen = True
    Else
        WorkbookIsOpen = False
    End If
End Function