VBA/Excel/Access/Word/Excel/Workbooks

Activate Workbook

 
     Sub ActivateWorkbook1()
         Dim sFullName As String
         Dim sFileName As String
         Dim myWorkbook As Workbook
         sFullName = "C:\Data\SalesData1.xlsx"
         sFileName = sGetFileName(sFullName)
         If bIsWorkbookOpen(sFileName) Then
         Set myWorkbook = Workbooks(sFileName)
         myWorkbook.Activate
         Else
         Set myWorkbook = Workbooks.Open(FileName:=sFullName)
         End If
     End Sub

Activating a Window

 
Sub active()
    Workbooks("your.xls").Windows(1).Activate
End Sub

Add Workbooks and save it as a new file

 
Sub add()
     Workbooks.Add
     ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData1.xlsx"
     Workbooks.Add
     ActiveWorkbook.SaveAs Filename:="C:\Data\SalesData2.xlsx"
     Workbooks("SalesData1.xlsx").Activate
End Sub

Arranging and Resizing Windows

 
Sub arrange()
    Workbooks("your.xls").Windows.Arrange ArrangeStyle:=xlArrangeStyleVertical, _
        ActiveWorkbook:=True, SyncVertical:=True
End Sub

Closing All Open Workbooks

 
Sub close()
    Workbooks.Close
End Sub

Copying or Moving a Worksheet

 
Sub copy()
    Workbooks("your.xls").Sheets("Costs").Copy, _
        After:=Sheets(Sheets.Count)
End Sub

Creating a Named Range from Workbooks

 
Sub names()
    Workbooks("your.xls").Names.Add Name:="myRange", _
            RefersTo:="=Materials!$A$1:$G$22"
End Sub

Creating a New Blank Workbook

 
"To create a blank workbook: as if you"d clicked the New button
Sub add()
    Workbooks.Add
End Sub

Declare a Workbook object variable and assign the ActiveWorkbook object to it, so that subsequent code can work with the object variable:

 
Sub workbook()
    Dim myWorkbook As Workbooks
    Set myWorkbook = ActiveWorkbook
    With myWorkbook
        "actions here
    End With
End Sub

Deleting a Named Range

 
Sub name()
    Workbooks("your.xls").Names("myRange").Delete
End Sub

Files in the Same Directory

 
     Sub ActivateWorkbook2()
         Dim sPath As String
         Dim sFileName As String
         Dim sFullName As String
         Dim myWorkbook As Workbook
         sFileName = "SalesData1.xlsx"
         If bIsWorkbookOpen(sFileName) Then
         Set myWorkbook = Workbooks(sFileName)
         myWorkbook.Activate
         Else
         sPath = ThisWorkbook.Path
         sFullName = sPath & "\" & sFileName
         Set myWorkbook = Workbooks.Open(FileName:=sFullName)
         End If
     End Sub

Getting a Filename from a Path

 
Sub open2()
     Set myWorkbook = Workbooks.Open(FileName:="C:\Data\SalesData1.xlsx")
     MsgBox myWorkbook.Name
End Sub

Moves the worksheet named Homes from the workbook named Planning.xls to the workbook named Building Schedule.xls, inserting the worksheet before the first existing worksheet in the workbook

 
Sub move()
    Workbooks("Planning.xls").Sheets("Homes").Move , _
       Before:=Workbooks("Building Schedule.xls").Sheets(1)
End Sub

Opens the text file named Data13.txt in the folder z:\transfer using an exclamation point (!) as the delimiter character

 
Sub format()
    Workbooks.Open Filename:="z:\transfer\Data13.txt", Format:=6, Delimiter:="!"
End Sub

Opens the workbook named Plan.xls stored in the D:\Planning folder, providing the password for opening the workbook

 
Sub pass()
    Workbooks.Open Filename:="D:\Planning\Plan.xls", Password:="s@cur1ng!"
End Sub

Overwriting an Existing Workbook

 
Sub add()
     Set myWorkbook1 = Workbooks.Add
     Application.DisplayAlerts = False
     myWorkbook1.SaveAs Filename:="C:\Data\SalesData1.xlsx"
     Application.DisplayAlerts = True
End Sub

Prints the first two pages of the worksheet named Summary in the workbook named Planning.xls to a file named Planning Summary.prn in the network folder \\server\to_print:

 
Sub printOut()
    Workbooks("Planning.xls").Sheets("Summary").printOut From:=1, To:=2, _
        PrintToFile:=True, PrToFileName:="\\server\to_print\Summary.prn"
End Sub

Returns the text in the active cell in the first window open on the workbook named Planning.xls:

 
Sub active()
    MsgBox Workbooks("your.xls").Windows(1).ActiveCell.Text
End Sub

Sharing a Workbook

 
Sub m()
    With Workbooks("your.xls")
        If MultiUserEditing = False Then
            .SaveAs Filename:=.FullName, AccessMode:=xlShared
        End If
    End With

Stores the current SheetsInNewWorkbook property, sets the SheetsInNewWorkbook property to 12, creates a new workbook (with those 12 worksheets), and then restores the SheetsInNewWorkbook setting to its previous value

 
Sub Sheets()
    Dim mySiNW As Integer
    mySiNW = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 12
    Workbooks.Add
    Application.SheetsInNewWorkbook = mySiNW
End Sub

The Add method allows you to specify a template for the new workbook.

 
Sub add()
     Set myWorkbook1 = Workbooks.Add(Template:="C:\Data\SalesData.xlsx")
End Sub

To add an existing workbook file to the Workbooks collection, you use the Open method

 
Sub open()
     Set myWorkbook1 = Workbooks.Open(Filename:="C:\Data\SalesData1.xlsx")
End Sub

To apply a read-only recommendation to a workbook, set its ReadOnlyRecommended property to True

 
Sub readOnly
    Workbooks("Strategy.xls").ReadOnlyRecommended = True
End Sub

To avoid confusion, you can select a workbook unambiguously- if you know the name of the desired Workbook object

 
Sub activateWorkbookByName()
    Workbooks("Book2").Activate
End Sub

To see if the workbook is already a member of the Workbooks collection

 
     Function bIsWorkbookOpen(wkbName As String) As Boolean
         Dim myWorkbook As Workbook
         On Error Resume Next
         Set myWorkbook = Workbooks(wkbName)
         If Not myWorkbook Is Nothing Then
         bIsWorkbookOpen = True
         End If
     End Function

To select a Workbook object from the Workbooks Collection object

 
Sub activateWorkbook()
    Workbooks(2).Activate
End Sub

Use For...Next loop through Workbooks

 
     Sub Array2()
         Dim avData As Variant, myWorkbook As Workbook
         Dim i As Integer
         avData = Array("North", "South", "East", "West")
         For i = LBound(avData) To UBound(avData)
             Set myWorkbook = Workbooks.Open(FileName:=avData(i) & ".xls")
             "Process data here
             myWorkbook.Close SaveChanges:=True
         Next i
     End Sub

Use the return value of the Add method to create an object variable that refers to the new workbook

 
     Sub NewWorkbooks()
           Dim myWorkbook1 As Workbook
           Dim myWorkbook2 As Workbook
           Set myWorkbook1 = Workbooks.Add
           Set myWorkbook2 = Workbooks.Add
           myWorkbook1.Activate
     End Sub

Values for the UpdateLinks Argument

 
Value         Effect 
(omitted)     Excel prompts the user to decide how to update links.
0             Excel doesn"t update links.
1             Excel updates external links but not remote links.
2             Excel updates remote links but not external links.
3             Excel updates all links.

Sub open()
    Workbooks.Open Filename:= "C:\Business\Expenses.xls", UpdateLinks:=0
End Sub

When you need to step through several objects in a collection, use a loop and a looping variable to represent the index of the object to be returned.

 
Sub saveWorkbook()
    For I = 1 To 3
          If Workbooks(I).Saved = True Then Workbooks(I).Close
    Next I
End Sub

Working with Worksheets to add a worksheet

 
Sub add()
    Dim mySheet As Worksheet
    Set mySheet = Workbooks(1).Sheets.Add(before:=Sheets(1))
    mySheet.Name = "Summary"
End Sub

Work your way through the entire object hierarchy to set one property

 
Sub cellValueReference()
    Workbooks("Book2").Sheets("Sheet2").Cells(4, "A").Value = "VBA is fun!"
End Sub