Opening


Workbooks.Open

Dim oWorkbook As Excel.Workbook

oWorkbook = Workbooks.Open (FileName:="", _ 
                            UpdateLink:=2, _
                            ReadOnly:=False, _
                            Format:=
                            Password:=
                            WriteResPassword:=
                            IgnoreReadOnlyRecommended:=
                            Origin:=xlPlatform.
                            Delimiter:=
                            Editable:=
                            Notify:=
                            Converter:=
                            AddToMru:=False, _
                            Local:=
                            CorruptLoad:=xlCorruptLoad.

FileName - The filename of the workbook to open
UpdateLinks - Specifies how links in the file are updated. 1 user specifies how links will be updated. 2 never update the links. 3 always update the links. If this argument is omitted, the user is (meant to be) prompted to make a choice.
ReadOnly - Opens the file as read-only
Format - If you are opening a text file this specifies the delimiter character. 1 tabs. 2 commas. 3 spaces. 4 semi-colons. 5 nothing. 6 custom character.
Password - The password required to open the workbook.
WriteResPassword - The password required to write to a write-reserved workbook
IgnoreReadOnlyRecommended - Lets you supress the read-only recommended prompt (assuming the workbook was saved with a Read-Only recommendation).
Origin - If you are opening a text file this indicates where is originated.
Delimiter - If you are opening a text file and the 'Format' argument is 6 then this is the custom delimiter character.
Editable - If the file is an Excel template, then true opens the specific template for editing. False opens a new workbook based on this template.
Notify - If the file cannot be opened in read/write mode, true will add the file to the file notification list.
Converter - The index of the first file converter to try when opening the file.
AddToMru - Adds the workbook to the list of recently used files.
Local - Saves the file either against the language of VBA or against the language of Excel. True is Excel language, false is VBA language.
CorruptLoad - The first attempt is normal. If Excel stops operating while opening the file, the second attempt is safe load. If Excel stops operating on the second attempt then the next attempt is data recovery.


Opening Workbooks

When a workbook is opened it automatically becomes the active workbook.

Workbooks.Open (??) 
Workbooks.OpenText (??)
Workbooks.Open "C:\temp\Temp.xls"
ActiveWorkbook.OpenText( ????? )

Dim objWorkbook As Workbook 
Set objWorkbook = Workbooks.Open(FileName:="C:\Temp.xls")

objWorkbook.Name =
objWorkbook.Path =
objWorkbook.FullName =

Opening


Workbooks.Open (??) 
Workbooks.OpenText (??)
Workbooks.Open "C:\temp\Temp.xls"
ActiveWorkbook.OpenText( ????? )

Dim wbk As Workbook 
Set wbk = Workbooks.Open(FileName:="----.xls")


Running a macro automatically when it opens

In the (Microsoft Excel Objects > ThisWorkbook) folder, select Workbook in the top left drop-down and select Open from the top right drop-down.
This event is the default event used when you select "Workbook"

Private Sub Workbook_Open() 
   Call Msgbox("Welcome .")
End Sub


Checking if a workbook exists


Private Function BET_WorkbookExists(sWbkName As String) As Boolean 
   If Dir(sWbkName) <> "" Then
      BET_WorkbookExists = True
   Else
      BET_WorkbookExists = False
   End If
End Function


Checking if a workbook is currently open

This function returns true if the workbook is currently open
Tries to assign a object variable to the workbook
If the assignment was successful then the workbook must be open

Private Function BET_WorkbookIsOpen(sWbkName As String) As Boolean 
   Dim wbk As Workbook
   On Error Resume Next
   Set wbk = Workbooks(sWbkName)
   If Not wbk Is Nothing Then
      BET_WorkbookIsOpen = True
   Else
      BET_WorkbookIsOpen = False
   End If
End Function

Running a macro automatically when it opens

In the (Microsoft Excel Objects > ThisWorkbook) folder, select Workbook in the top left drop-down and select Open from the top right drop-down.
This event is the default event used when you select "Workbook"

Private Sub Workbook_Open() 
   Call Msgbox("Welcome .")
End Sub


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext