Private Sub test()
MsgBox ("工作薄數(shù)量為:" & Workbooks.Count)
Workbooks(1).Activate
Workbooks("第6次作業(yè)成績.xls").Activate
MsgBox ("當(dāng)前工作薄名:" + ThisWorkbook.name)
End Sub
注:Workbooks(1)表示第一個工作薄,訪問某個工作薄可以通過下表索引也可以通過名稱。ThisWorkbook表示當(dāng)前工作薄,執(zhí)行VBA代碼所在的工作薄,ActivateWorkBook表示活動工作薄,它們可能相同也可能不同。
Sub test()
For Each wb In Workbooks
Debug.Print wb.Name
Next
For index = 1 To Windows.Count
Debug.Print Windows(index).Parent.Name
Next
End Sub
注意:上面兩種方式輸出的結(jié)果的順序不同
Private Sub test()
ThisWorkbook.Charts.Delete
End Sub
Private Sub test()
ThisWorkbook.AutoUpdateSaveChanges = True
ThisWorkbook.AutoUpdateFrequency = 5 '以分鐘為單位
End Sub
注意:此時必須設(shè)置“共享工作薄”,并將工作薄保存成xlsm格式的共享工作薄。
Private Sub test()
Dim name, fullname As String
name = ThisWorkbook.name '工作薄名
fullname = ThisWorkbook.fullname '工作薄全名,包括磁盤路勁
End Sub
注:在工作薄為保存之前,上面兩個值相同。
Private Sub test()
If ThisWorkbook.HasVBProject = True Then
MsgBox ("包含宏項目")
Else
MsgBox ("不包含宏項目")
End If
End Sub
Private Sub test()
If ThisWorkbook.ReadOnly = True Then
MsgBox ("只讀方式打開")
Else
MsgBox ("非只讀方式打開")
End If
End Sub
Private Sub test()
Dim i As Integer
Dim str As String
str = ""
For i = 1 To ThisWorkbook.Sheets.Count ‘遍歷每個工作表
str = str & ThisWorkbook.Sheets(i).name & ";"
Next
MsgBox (str)
End Sub
Private Sub test()
ThisWorkbook.Password = "123456" '設(shè)置打開密碼
ThisWorkbook.WritePassword = "123" '設(shè)置寫密碼
End Sub
Private Sub test()
ThisWorkbook.PrintOut form:=1, to:=3
End Sub
Private Sub test()
ThisWorkbook.Protect Password = "123456", structure:=True, Windows:=True
ThisWorkbook.Unprotect '取消保護(hù)
End Sub
Private Sub test()
ThisWorkbook.Save '保存更改
ThisWorkbook.SaveAs Filename:=Application.GetSaveAsFilename, Password:="123456" ‘另存為
End Sub
Sub test()
Application.DisplayAlerts = False '不出現(xiàn)提示框
Set wk = Workbooks.Add '新建工作薄
wk.Sheets(1).[a1].value = 122
Dim name$
name = ThisWorkbook.Path & "\新建工作薄.xls"
wk.SaveAs Filename:=name, FileFormat:=xlWorkbookNormal '另存為
wk.Close '關(guān)閉新建的工作薄
Application.DisplayAlerts = True
Workbooks.Open name
Debug.Print ActiveWorkbook.Sheets(1).Range("A1").value '新打開的工作薄為激活的工作薄
Debug.Print Workbooks(2).Sheets(1).Range("A1").value '因?yàn)橐呀?jīng)存在一個工作薄,再打開工作薄時為工作薄2
Workbooks(2).Close
End Sub
在打開工作薄時候有時候會提示更新鏈接的消息框,如果不想顯示這些提示信息,可以在打開設(shè)置參數(shù)UpdateLinks值為0,例如:
WorksBooks.Open Filname:=”test.xls”,UpdateLinks:=0
UpdateLinks參數(shù)取值及含義
取值 | 含義 | 取值 | 含義 |
0 | 不更新任何引用 | 1 | 更新外部引用,但不更新遠(yuǎn)程引用 |
2 | 更新遠(yuǎn)程引用,但不更新外部引用 | 3 | 同時更新外部引用及遠(yuǎn)程引用 |
下面程序獲取當(dāng)前文件夾下面所有的.xls文件,并將文件名保存到數(shù)組arrayFile
Dim arrayFile(1 To 100) As String
Count = 1
Path = ThisWorkbook.Path '獲取當(dāng)前路徑
resultFile = ThisWorkbook.Name
myfile = Dir(Path & "\*.xls") '選中所有的.xls文件
Do
If myfile <> ThisWorkbook.Name Then
arrayFile(Count) = myfile
Count = Count + 1
End If
myfile = Dir '選中下一個文件
Loop While myfile <> ""
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("即將關(guān)閉工作薄")
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox ("保存更改")
End Sub
Private Sub Workbook_Open()
MsgBox ("您打開了工作?。? & ThisWorkbook.FullName)
End Sub
Private Sub Workbook_WindowResize(ByVal Wn As Window)
MsgBox ("改變工作薄大小")
End Sub