в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:45Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Workbooks - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия