VBA/Excel/Access/Word/Excel/ActiveWorkbook — различия между версиями

Материал из VB Эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 12:47, 26 мая 2010

Closes the active workbook without saving changes

 
Sub close()
    ActiveWorkbook.Close SaveChanges:=False
End Sub



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

 
Sub Test()
    For Each Item In ActiveWorkbook.Sheets
        Debug.Print Item.name
    Next Item
End Sub



Get current active workbook full name

 
Sub test()
    MsgBox ActiveWorkbook.FullName
End Sub



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

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



Opening a New Window on a Workbook

 
Sub new()
    ActiveWorkbook.Windows(1).NewWindow
End Sub



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.

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



Previewing a workbook before saving it as a Web page

 
Sub pre()
    ActiveWorkbook.WebPagePreview
End Sub



Printing a Worksheet

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



Programmatically Retrieving Link Source Information

 
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



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

 
Sub protect()
    ActiveWorkbook.Protect Password:="pass", Structure:=True, Windows:=True
End Sub



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

 
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



Saving Worksheets as Web Pages

 
Sub webPage()
ActiveWorkbook.SaveAs _
     Filename:=ActiveWorkbook.Path & "\myXclfile.htm", _
     FileFormat:=xlHtml
End Sub



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

 
Sub remove()
    ActiveWorkbook.RemovePersonalInformation = True
End Sub



Setting Passwords and Read-Only Recommendation for a Workbook

 
Sub pass()
    ActiveWorkbook.Password = "pass"
End Sub



Standard Workbook Properties

 
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



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

 
Sub passWrite()
    ActiveWorkbook.WritePassword = "pass"
End Sub



Working with the ActiveWorkbook Object

 
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