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