VBA/Excel/Access/Word/Excel/ActiveWorkbook

Closes the active workbook without saving changes

   <source lang="vb">

Sub close()

   ActiveWorkbook.Close SaveChanges:=False

End Sub

</source>
   
  

For Each-Next is useful for cycling through each member of a collection

   <source lang="vb">

Sub Test()

   For Each Item In ActiveWorkbook.Sheets
       Debug.Print Item.name
   Next Item

End Sub

</source>
   
  

Get current active workbook full name

   <source lang="vb">

Sub test()

   MsgBox ActiveWorkbook.FullName

End Sub

</source>
   
  

Looping through a Collection: use the For Each- Next structure

   <source lang="vb">

Sub DeleteRow1()

   Dim myWorksheet As Worksheet
   For Each myWorksheet In ActiveWorkbook.Worksheets
       myWorksheet.Rows(1).delete
   Next myWorksheet

End Sub

</source>
   
  

Opening a New Window on a Workbook

   <source lang="vb">

Sub new()

   ActiveWorkbook.Windows(1).NewWindow

End Sub

</source>
   
  

Opens a workbook named MyWorkbook.xls located in the same directory as the active workbook. An error is generated if the file cannot be found.

   <source lang="vb">

Sub filePath()

   Dim filePath As String
   filePath = ActiveWorkbook.Path
   Workbooks.Open (filePath & "\" & "MyWorkbook.xls")

End Sub

</source>
   
  

Previewing a workbook before saving it as a Web page

   <source lang="vb">

Sub pre()

   ActiveWorkbook.WebPagePreview

End Sub

</source>
   
  

Printing a Worksheet

   <source lang="vb">

Sub print()

   ActiveWorkbook.Sheets(1).Printout Copies:=2, Collate:=True

End Sub

</source>
   
  

Programmatically Retrieving Link Source Information

   <source lang="vb">

Sub PrintSimpleLinkInfo()

   Dim avLinks As Variant
   Dim nIndex As Integer
   Dim wb As Workbook
   Set wb = ActiveWorkbook
   avLinks = wb.LinkSources(xlExcelLinks)
   If Not IsEmpty(avLinks) Then
       For nIndex = 1 To UBound(avLinks)
           Debug.Print "Link found to "" & avLinks(nIndex) & """
       Next nIndex
   Else
       Debug.Print "The workbook "" & wb.name & "" doesn"t have any links."
   End If

End Sub

</source>
   
  

Protects the structure and windows of the active workbook with the password 0llsecurd:

   <source lang="vb">

Sub protect()

   ActiveWorkbook.Protect Password:="pass", Structure:=True, Windows:=True

End Sub

</source>
   
  

Saves the worksheet range "$A$1:$B$11" as a Web page without interactivity so that it can be viewed in any current browser:

   <source lang="vb">

Public Sub SaveRangeWeb()

   ActiveWorkbook.PublishObjects.Add _
       SourceType:=xlSourceRange, _
       Filename:=ActiveWorkbook.Path & "\Sample1.htm", _
       Sheet:=ActiveSheet.name, _
       Source:="$A$1:$B$11", _
       HtmlType:=xlHtmlStatic
   ActiveWorkbook.PublishObjects(1).Publish (True)

End Sub

</source>
   
  

Saving Worksheets as Web Pages

   <source lang="vb">

Sub webPage() ActiveWorkbook.SaveAs _

    Filename:=ActiveWorkbook.Path & "\myXclfile.htm", _
    FileFormat:=xlHtml

End Sub

</source>
   
  

Setting Excel to Remove Personal Information from the File Properties When You Save

   <source lang="vb">

Sub remove()

   ActiveWorkbook.RemovePersonalInformation = True

End Sub

</source>
   
  

Setting Passwords and Read-Only Recommendation for a Workbook

   <source lang="vb">

Sub pass()

   ActiveWorkbook.Password = "pass"

End Sub

</source>
   
  

Standard Workbook Properties

   <source lang="vb">

Sub TestPrintGeneralWBInfo()

   Dim wb As Workbook
   Set wb = ActiveWorkbook
   Debug.Print "Name: " & wb.name
   Debug.Print "Full Name: " & wb.FullName
   Debug.Print "Code Name: " & wb.CodeName
   Debug.Print "Path: " & wb.Path
   If wb.ReadOnly Then
       Debug.Print "The workbook has been opened as read-only."
   Else
       Debug.Print "The workbook is read-write."
   End If
   If wb.Saved Then
       Debug.Print "The workbook does not need to be saved."
   Else
       Debug.Print "The workbook should be saved."
   End If

End Sub

</source>
   
  

To set a "password to modify," set the WritePassword property of the Workbook object.

   <source lang="vb">

Sub passWrite()

   ActiveWorkbook.WritePassword = "pass"

End Sub

</source>
   
  

Working with the ActiveWorkbook Object

   <source lang="vb">

Sub active()

   If ActiveWorkbook Is Nothing Then
       MsgBox "open a workbook and click in it before running this macro." _
           & vbCr & vbCr & "This macro will now end.", _
           vbOKOnly + vbExclamation, "No Workbook Is Open"
       End
   End If

End Sub

</source>