VBA基础，工作簿workbook相关的方法和属性_vba workbook

0 我觉得VBA和VB,VBS最大的区别就在A（application）上了

• 所以application里的 workbook  , worksheet   range ,cell等应该是VBA语言的核心，最复杂内容了

• 本身VBA里的VB语言部分，其实和很多语言语法结构也差不多
• 每种语言的差别
• 一在语法差别上。但差异应该不会太大
• 另外一个就在每种语言的对象系统上，包括各种对象，数据结构等等

 

1 工作簿相关方法---易报错总结

1.1  保存时的报错

• 保存save时，不能和已有文件名同名
• workbooks.saveas   fullname (不能和同目录下已有的工作簿同名)
• workbooks.savecopyas   fullname (不能和同目录下已有的工作簿同名)

1.2 创建时的报错

• 不允许直接 创建 .xlsm类的文件

1.3 打开文件时的报错

• 如果打开的文件不存在，会报错
• workbooks.open fullname 如果文件不存在会报错
• EXCEL如果save的格式和实际excel版本的格式不同，会警告文件格式不同，暂时可无视之

1.4 修改文件时的报错

• 如果读取和修改工作簿内容，但是工作簿没有打开，会报错 下标越界

 

2 VBA 和 Application--Excel下的命名系统 

2.1 workbooks 工作簿有fullname属性

fullname= path+ "\" + name

• ThisWorkbook.FullName     文件全名
• ThisWorkbook.Path             文件路径
• ThisWorkbook.Name           文件名
• ThisWorkbook.FullName  =   ThisWorkbook.Path  + "\"  +  ThisWorkbook.Name

 

2.2 但是 fso下的file 没有fullname 属性，因为 file.path 就是完整名字了

• file.FullName     错误，没这个fullname属性
• file.Path             文件路径,就是文件全名
• file.Name           文件名

 
Sub test_wb11()
'比较wb的名字  和 一般file的名字
 
 
Dim fso1 As Object
Dim fd1 As Object
 
Set fso1 = CreateObject("scripting.filesystemobject")
Set fd1 = fso1.GetFolder(ThisWorkbook.Path)
 
For Each i In fd1.Files
    If i Like "*.xlsm" Then
         Debug.Print i.Name
         Debug.Print i.Path    'workbook工作簿的名字不一样
         Debug.Print i.Path & "\" & i.Name    '这样做重复而多余
'         Debug.Print i.FullName   '会报错
    End If
Next
Debug.Print ""
 
For Each j In Workbooks
    Debug.Print j.Name
    Debug.Print j.Path
    Debug.Print j.FullName             'wb有fullname属性
    Debug.Print j.Path & "\" & j.Name  'wb工作簿的fullname=path+ "" + name 是有意义的
    
Next
 
End Sub

 

3 workbook 和 workbooks，对象和对象的集合

3.1 workbook是对象

• 工作簿是EXCEL的对象之一
• workbook 只能指代 每个单独的workbook
• 特殊workbook的用法
• thisworkbook
• activeworkbook

 

3.2 workbooks是EXCEL的属性，也表示excel下所有workbook的集合

• 工作簿的集合，其实是Application的属性 application.workbooks
• workbooks  (即代表  application.workbooks)
• 比如Workbooks.Add   等于  Application.Workbooks.Add

 

3.3 对象只属于对象集合，而不属于上级对象

• 正确   for each wb1 in application.workbooks
• 错误  for each wb1 in application
• 错误  for each wb1 in application.workbook

 

4 workbooks.add 方法  (Application.Workbooks.Add)

• 表达式.Add(Template)

• Workbooks.Add 方法 新建一个工作表。新工作表将成为活动工作表。

• 语法

• 表达式.Add(Template)

• 表达式   一个代表 Workbooks 对象的变量。

• 确定如何创建新工作簿。如果此参数为指定现有 Microsoft Excel 文件名的字符串，那么创建新工作簿将以该指定的文件作为模板。如果此参数为常量，新工作簿将包含一个指定类型的工作表。
• 模板类型为整数 int 比如1 就是工作簿，2是chart 表格
• 如果想创建多个表格，workbooks.add 本身好像不带数量参数
• 和循环嵌套使用，多次 workbooks.add

Sub test1_wb()
 
'表达式.Add(Template)
'确定如何创建新工作簿。如果此参数为指定现有 Microsoft Excel 文件名的字符串，那么创建新工作簿将以该指定的文件作为模板。如果此参数为常量，新工作簿将包含一个指定类型的工作表。
 
Workbooks.Add 1
Workbooks.Add (2)
 
For i = 1 To 3
    Workbooks.Add
Next
 
End Sub

 

• workbooks.add  会新建一个新工作薄
• 但是需要用   workbook.saveas   fullname  保存才行
• 因为每次新建的工作簿刚好是激活的，所以用 activeworkbook.saveas  fullname

Public Sub test_wb1()
 
Debug.Print ThisWorkbook.Path
Debug.Print ThisWorkbook.FullName
Debug.Print ThisWorkbook.Name
 
 
Application.Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Administrator\Desktop\t2.xls"
 
Workbooks.Add
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "t3.xls"
 
 
End Sub

 

5 workbook的保存 save  saveas savecopyas

5.1 语法

5.1.1 文件保存

FN+F1帮助系统

Workbook.Save 方法 

• 保存对指定工作簿所做的更改。
• 语法
• 表达式.Save
• 表达式   一个代表 Workbook 对象的变量。
   

5.1.2 文件另存为 （这种方法另存为的文件不会关闭）

在另一不同文件中保存对工作簿所做的更改。（对新建的wb文件，是第一次保存）

workbook.saveas
表达式   一个代表 Workbook 对象的变量。

表达式.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

 

5.1.3 文件另存为 savecopyas  (另外为的文件会自动关闭)

• Workbook.SaveCopyAs 方法 
• 将指定工作簿的副本保存到文件，但不修改内存中的打开工作簿。
• 语法
• 表达式.SaveCopyAs(Filename)
• 表达式   一个代表 Workbook 对象的变量。
   

 

5.2 文件保存的3种方式，新建文件要用saveas

• save
• 只是保存文件内容，文件如果是新建的，未保存文件本身。已经保存路径的文件自动保存。
• saveas   fullname
• savecopyas   fullname
• 将文件的副本另外为一个新文件，文件本身如果是新建的，仍然没保存本身。

Public Sub test_wb2()
 
Application.Workbooks.Add
ActiveCell = 1
ActiveWorkbook.Save
 
Workbooks.Add
ActiveCell = 11
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "t2.xls"
 
Workbooks.Add
ActiveCell = 111
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & "t3.xls"
 
End Sub

 

 

5.3 文件保存时的报错

• 如果保存的文件名重复，会报错

 

5.4 文件保存时，文件类型的报错

• 如果选择保存的是 .xlsm  会报错
• 可能是因为安全等级的原因，不让直接创建xlsm这种后缀名文件
•  

 

5.5 文件保存的格式如果和实际EXCEL的版本不一致，会在打开时被警告

但实际上无所谓，比如保存为  .xls 文件，而实际EXCEL最新格式是 .xlsx,两者不一致所以警告

 

5.6 保存时， 参数可以写在括号内，或者是跟在 方法 后面写

Sub test_wb4()
 
Workbooks.Add
ActiveWorkbook.ActiveSheet.Range("a2") = a2   '错误例子，因为a2是变量，值为""
ActiveWorkbook.ActiveSheet.Range("a3") = "a3"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "t4.xlsx"
ActiveWorkbook.SaveCopyAs (ThisWorkbook.Path & "\" & "t5.xlsx")
 
End Sub

 

 

6 workbooks.open   工作簿必须先打开才可以操作内容

6.1 打开工作簿

• Workbooks.Open 方法，打开一个工作簿。
• 语法
• 表达式.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
• 表达式   一个代表 Workbooks 对象的变量。
   

6.2 workbooks.open ()

• workbooks.open  参数
• workbooks.open (参数)     '现在发现参数大于1个就不行

Public Sub test_wb3()
 
path1 = ThisWorkbook.Path
 
'Application.DisplayAlerts = False  '这个声明的生命周期也应该在sub内，有啥必要非在sub结尾前关闭掉？多此一举把
 
Workbooks.Add
'参数只有1个时，括号写法和非括号写法都可以
ActiveWorkbook.SaveAs path1 & "\" & "t3.xlsx"
ActiveWorkbook.SaveAs (path1 & "\" & "t3.xlsx")
ActiveWorkbook.Close
Workbooks.Add
ActiveWorkbook.SaveAs path1 & "\" & "t2.xlsx", Password:=123
ActiveWorkbook.SaveAs Filename:=path1 & "\" & "t2.xlsx", Password:=123
'参数超过1个时，不能用括号写法，会报错
'ActiveWorkbook.SaveAs (path1 & "\" & "t2.xlsx", Password:=123)
'ActiveWorkbook.SaveAs (Filename:=path1 & "\" & "t2.xlsx", Password:=123)
ActiveWorkbook.Close
 
 
Workbooks.Open (path1 & "\" & "t3.xlsx")
Workbooks("t3.xlsx").Close
 
Workbooks.Open path1 & "\" & "t2.xlsx", Password:=123
Workbooks("t2.xlsx").ActiveSheet.Range("a1") = "aaa"
Workbooks("t2.xlsx").Save
Workbooks("t2.xlsx").Close
 
End Sub

 

6.3 前台打开和后台打开

• 工作簿的打开：工作簿如果没打开，操作其中内容，会提示  下标越界
• 前台打开
• 后台打开

 

6.4 如果想修改 未打开的工作簿里的内容--会报错：下标越界

 

 

6.5 新建工作簿，先打开工作簿，写内容，保存，再关闭工作簿

"修改---保存---关闭" 就会尽量少出现弹窗

• 读取和修改工作簿内容前，必须先打开工作簿
• 可以每打开一个工作簿就关闭这个，避免蹦出很多工作簿

Public Sub test_wb3()
 
path1 = ThisWorkbook.Path
 
 
Workbooks.Add
ActiveWorkbook.SaveAs path1 & "\" & "t2.xls"
ActiveWorkbook.Close
Workbooks.Add
ActiveWorkbook.SaveAs path1 & "\" & "t3.xls"
ActiveWorkbook.Close
 
 
Workbooks("test1.xlsm").ActiveSheet.Range("a1") = "abc"
 
Workbooks.Open path1 & "\" & "t2.xls"
Workbooks("t2.xls").ActiveSheet.Range("a1") = "abc123111"
Workbooks("t2.xls").Close
 
Workbooks.Open path1 & "\" & "t3.xls"
Workbooks("t3.xls").ActiveSheet.Range("a1") = "abcde12345111"
Workbooks("t3.xls").Close
 
 
End Sub

 

7  workbooks.open   打开文件和输入密码

• 文件新建保持时可以加密码
• 文件打开时需要输入密码才可以打开
• workbooks.saveas  (finename:=)path & "\" & "t1.xls"   ,password:="123"
• workbooks.open finename:=path & "\" & "t1.xls"   ,password:="123"

 

Public Sub test_wb3()
 
path1 = ThisWorkbook.Path
 
Workbooks.Add
ActiveWorkbook.SaveAs path1 & "\" & "t2.xls", Password:=123
ActiveWorkbook.Close
Workbooks.Add
ActiveWorkbook.SaveAs path1 & "\" & "t3.xls", Password:=123
ActiveWorkbook.Close
 
Workbooks("test1.xlsm").ActiveSheet.Range("a1") = "abc"
 
Workbooks.Open path1 & "\" & "t2.xls", Password:=123
Workbooks("t2.xls").ActiveSheet.Range("a1") = "abc123111"
Workbooks("t2.xls").Close
 
Workbooks.Open path1 & "\" & "t3.xls", Password:=123
Workbooks("t3.xls").ActiveSheet.Range("a1") = "abcde12345111"
Workbooks("t3.xls").Close
 
End Sub

 

8  重复打开工作簿----现在版本的EXCEL只要 修改内容先保存，重复打开也没事

"修改---保存---关闭" 就会尽量少出现弹窗

• 现在版本的EXCEL，可以重复打开已经打开的EXCEL文件
• 如果现在已经打开的 文件，有修改未保存的内容，会提示你，再次打开会丢失这些未保存内容---解决办法就是先save
• 如果希望自动化程度更高，去掉一些弹窗，就加上这句话
• Application.DisplayAlerts=false

 

Public Sub test_wb3()
 
path1 = ThisWorkbook.Path
 
'Application.DisplayAlerts = False  '这个声明的生命周期也应该在sub内，有啥必要非在sub结尾前关闭掉？多此一举把
 
Workbooks.Open path1 & "\" & "t2.xlsx", Password:=123
Workbooks("t2.xlsx").ActiveSheet.Range("a1") = "aaa"
Workbooks("t2.xlsx").Save
 
Workbooks.Open path1 & "\" & "t2.xlsx", Password:=123
Workbooks("t2.xlsx").ActiveSheet.Range("a2") = "bbb"
Workbooks("t2.xlsx").Save
 
Workbooks.Open path1 & "\" & "t2.xlsx", Password:=123
Workbooks("t2.xlsx").ActiveSheet.Range("a3") = "cccc"
Workbooks("t2.xlsx").Save
 
End Sub

 

9  先判断一个工作簿是否打开？ （打开了一定常驻在内存里了）

• 因为工作簿如何已经打开了

• 取内存中的工作簿，然后比较工作簿名字即可，找一个工作簿是否打开

• 现在版本的EXCEL已经不要求，文件不可重复打开了，所以这种检查不是很有必要。

Sub test_wb5()
'这样是取内存中的工作簿，已经打开的工作簿才能统计到
 
For Each i In Application.Workbooks
    Debug.Print i.Name
    If i.Name = "" Then
       Debug.Print "xx工作簿已经打开"
    End If
Next
 
For j = 1 To Application.Workbooks.Count
    Debug.Print Workbooks(j).Name
Next
 
End Sub
 

 

10  取某个文件夹下的工作簿数量

10.1 方法1：用fso方法，取folder和files ,其实还只是类 "xlsm" 之类的文件数量

不支持 file.open 打开

Sub test_wb6()
'取某个文件夹下的工作簿数量
 
Dim fso1 As Object
Dim fd1 As Object
 
Set fso1 = CreateObject("scripting.filesystemobject")
Set fd1 = fso1.GetFolder(ThisWorkbook.Path)
 
For Each i In fd1.Files
    If i Like "*.xlsm" Then
         Debug.Print i.Name
'         i.Open   '不支持这么打开
    End If
Next
End Sub

但是可以用这种方法打开， workbooks.open

Sub test_wb6()
'取某个文件夹下的工作簿数量
 
Dim fso1 As Object
Dim fd1 As Object
 
Set fso1 = CreateObject("scripting.filesystemobject")
Set fd1 = fso1.GetFolder(ThisWorkbook.Path)
 
For Each i In fd1.Files
    If i Like "*.xlsm" Then
 
         Workbooks.Open i.Path
         Workbooks(i.Path).ActiveSheet.Range("c1") = 666
 
    End If
Next
End Sub

 

 

10.2 方法，用dir的方法遍历文件

Sub test_wb7()
 
'dir不返回对象，返回字符串
fd1_name = Dir(ThisWorkbook.Path, vbDirectory)
Debug.Print fd1_name
 
f1_name = Dir(ThisWorkbook.Path & "\" & "*.xlsm")   '通配符查某类型文件
Debug.Print f1_name  '只查找一个文件
'每次继续执行一次dir 指针会往下移动一次
 
'没有条件变化的do loop ，是无限死循环
'Do While Not f1_name = ""
'   Debug.Print f1_name
'Loop
 
Do While Not f1_name = ""
   Debug.Print f1_name
   f1_name = Dir
Loop
 
End Sub

 

 

11 关闭文件  workbooks().close  或 activeworkbook.close

• 基本语法
• Workbook.Close 方法，关闭对象。
• 语法
• 表达式.Close(SaveChanges, Filename, RouteWorkbook)
• 表达式   一个代表 Workbook 对象的变量。

 

• 具体举例
• workbooks().close
• activeworkbook.close
• thisworkbook.close
• workbooks().close savechange:=true

 

Sub test_wb20()
 
 
Workbooks.Add
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "t2.xlsx"
Workbooks("t2.xlsx").ActiveSheet.Range("c5") = 9999
Workbooks("t2.xlsx").Close
 
Workbooks.Open ThisWorkbook.Path & "\" & "t2.xlsx"
Workbooks("t2.xlsx").ActiveSheet.Range("c6") = 100000
Workbooks("t2.xlsx").Close savechanges:=True
 
 
End Sub

和上面代码的区别

• 是close的时候可以保存为一个新的文件--新工作簿，修改
• 原文件夹本身，不做修改
• 可见t2里只有9999，而t3里有9999和 10000

Sub test_wb21()
 
 
Workbooks.Add
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "t2.xlsx"
Workbooks("t2.xlsx").ActiveSheet.Range("c5") = 9999
Workbooks("t2.xlsx").Close
 
Workbooks.Open ThisWorkbook.Path & "\" & "t2.xlsx"
Workbooks("t2.xlsx").ActiveSheet.Range("c6") = 100000
Workbooks("t2.xlsx").Close savechanges:=True, Filename:=ThisWorkbook.Path & "\" & "t3.xlsx"
 
 
End Sub