VBA/Excel/Access/Word/Excel/Workbooks

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

Содержание

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