VBA/Excel/Access/Word/Excel/ActiveWorkbook
Содержание
- 1 Closes the active workbook without saving changes
- 2 For Each-Next is useful for cycling through each member of a collection
- 3 Get current active workbook full name
- 4 Looping through a Collection: use the For Each- Next structure
- 5 Opening a New Window on a Workbook
- 6 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.
- 7 Previewing a workbook before saving it as a Web page
- 8 Printing a Worksheet
- 9 Programmatically Retrieving Link Source Information
- 10 Protects the structure and windows of the active workbook with the password 0llsecurd:
- 11 Saves the worksheet range "$A$1:$B$11" as a Web page without interactivity so that it can be viewed in any current browser:
- 12 Saving Worksheets as Web Pages
- 13 Setting Excel to Remove Personal Information from the File Properties When You Save
- 14 Setting Passwords and Read-Only Recommendation for a Workbook
- 15 Standard Workbook Properties
- 16 To set a "password to modify," set the WritePassword property of the Workbook object.
- 17 Working with the ActiveWorkbook Object
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>