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.
|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 |
The file name of the workbook to be opened.
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.
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.|
True to open the workbook in read-only mode.
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.
The Password argument contains the password required to open a protected workbook.
The WriteResPassword argument contains the password required to write to a write-reserved workbook.
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
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.
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.
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.
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.
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
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).
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.
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.
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.
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