VBA/Excel/Access/Word/Excel/Workbooks

Activate Workbook

   <source lang="vb">

    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
</source>
   
  

Activating a Window

   <source lang="vb">

Sub active()

   Workbooks("your.xls").Windows(1).Activate

End Sub

</source>
   
  

Add Workbooks and save it as a new file

   <source lang="vb">

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

</source>
   
  

Arranging and Resizing Windows

   <source lang="vb">

Sub arrange()

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

End Sub

</source>
   
  

Closing All Open Workbooks

   <source lang="vb">

Sub close()

   Workbooks.Close

End Sub

</source>
   
  

Copying or Moving a Worksheet

   <source lang="vb">

Sub copy()

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

End Sub

</source>
   
  

Creating a Named Range from Workbooks

   <source lang="vb">

Sub names()

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

End Sub

</source>
   
  

Creating a New Blank Workbook

   <source lang="vb">

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

   Workbooks.Add

End Sub

</source>
   
  

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

   <source lang="vb">

Sub workbook()

   Dim myWorkbook As Workbooks
   Set myWorkbook = ActiveWorkbook
   With myWorkbook
       "actions here
   End With

End Sub

</source>
   
  

Deleting a Named Range

   <source lang="vb">

Sub name()

   Workbooks("your.xls").Names("myRange").Delete

End Sub

</source>
   
  

Files in the Same Directory

   <source lang="vb">

    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
</source>
   
  

Getting a Filename from a Path

   <source lang="vb">

Sub open2()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub move()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub format()

   Workbooks.Open Filename:="z:\transfer\Data13.txt", Format:=6, Delimiter:="!"

End Sub

</source>
   
  

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

   <source lang="vb">

Sub pass()

   Workbooks.Open Filename:="D:\Planning\Plan.xls", Password:="s@cur1ng!"

End Sub

</source>
   
  

Overwriting an Existing Workbook

   <source lang="vb">

Sub add()

    Set myWorkbook1 = Workbooks.Add
    Application.DisplayAlerts = False
    myWorkbook1.SaveAs Filename:="C:\Data\SalesData1.xlsx"
    Application.DisplayAlerts = True

End Sub

</source>
   
  

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:

   <source lang="vb">

Sub printOut()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub active()

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

End Sub

</source>
   
  

Sharing a Workbook

   <source lang="vb">

Sub m()

   With Workbooks("your.xls")
       If MultiUserEditing = False Then
           .SaveAs Filename:=.FullName, AccessMode:=xlShared
       End If
   End With
</source>
   
  

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

   <source lang="vb">

Sub Sheets()

   Dim mySiNW As Integer
   mySiNW = Application.SheetsInNewWorkbook
   Application.SheetsInNewWorkbook = 12
   Workbooks.Add
   Application.SheetsInNewWorkbook = mySiNW

End Sub

</source>
   
  

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

   <source lang="vb">

Sub add()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub open()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub readOnly

   Workbooks("Strategy.xls").ReadOnlyRecommended = True

End Sub

</source>
   
  

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

   <source lang="vb">

Sub activateWorkbookByName()

   Workbooks("Book2").Activate

End Sub

</source>
   
  

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

   <source lang="vb">

    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
</source>
   
  

To select a Workbook object from the Workbooks Collection object

   <source lang="vb">

Sub activateWorkbook()

   Workbooks(2).Activate

End Sub

</source>
   
  

Use For...Next loop through Workbooks

   <source lang="vb">

    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
</source>
   
  

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

   <source lang="vb">

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

Values for the UpdateLinks Argument

   <source lang="vb">

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

</source>
   
  

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.

   <source lang="vb">

Sub saveWorkbook()

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

End Sub

</source>
   
  

Working with Worksheets to add a worksheet

   <source lang="vb">

Sub add()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub cellValueReference()

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

End Sub

</source>