好久没有写文章了,整理电脑时,偶尔发现以前总结的VBA学习笔记,与大家共同分享:
1、遍历某个文件夹下所有文件(不包括子文件夹);
Dim strDir As String
Dim strFileName As String
strDir = "D:/123"
strFileName = Dir(strDir & "/*.*", vbNormal)
Do While strFileName <> ""
Debug.Print strDir & "/" & strFileName
strFileName = Dir
Loop
2、遍历某个文件夹下所有文件(包括子文件夹);
Private Function getFilesFromFolder(ByVal strFolder As String) As Long
Dim fs, filelist, folderlist, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set filelist = fs.GetFolder(strFolder).Files
For Each f In filelist
Debug.Print f.Path
Next
Set folderlist = fs.GetFolder(strFolder).subfolders
If Not folderlist Is Nothing Then
For Each f In folderlist
getFilesFromFolder f.Path
Next
End If
End Function
3、文件的读取;
Dim strline As String
Dim strFile As String
Dim intFileNo As Integer
intFileNo = FreeFile
strFile = "D:/1.txt"
Open strFile For Input As #intFileNo
Do While Not EOF(intFileNo)
Line Input #intFileNo, strline
Debug.Print strline
Loop
Close #intFileNo
4、文件的写入;
Dim strline As String
Dim strFile As String
Dim intFileNo As Integer
intFileNo = FreeFile
strFile = "D:/1.txt"
Open strFile For Output As #intFileNo
Print #intFileNo, "line ======"
Close #intFileNo
5、打开和关闭一个Excel文件;
Dim strFile As String
Dim strWorkBook As String
strFile = "D:/1.xls"
'open a Excel file 打开一个Excel文件
Workbooks.Open strFile
strWorkBook = ActiveWorkbook.Name 'get the name of the current file 取得当前Excel文件名
'此处添加对excel的相关操作
' close the excel file without saving 关闭文件不保存
Workbooks(strWorkBook).Close False
6、遍历Excel文件的sheet页签;
Dim strFile As String
Dim strWorkBook As String
Dim sh
strFile = "D:/1.xls"
'open a Excel file 打开一个Excel文件
Workbooks.Open strFile
strWorkBook = ActiveWorkbook.Name 'get the name of the current file 取得当前Excel文件名
'此处添加对excel的相关操作 -- 打印Excel文件的sheet页签名称
For Each sh In Sheets
Debug.Print sh.Name
Next
' close the excel file without saving 关闭文件不保存
Workbooks(strWorkBook).Close False
7、读取Excel文件中单元格的内容;
Dim strFile As String
Dim strWorkBook As String
Dim sh
strFile = "D:/1.xls"
'open a Excel file 打开一个Excel文件
Workbooks.Open strFile
strWorkBook = ActiveWorkbook.Name 'get the name of the current file 取得当前Excel文件名
'此处添加对excel的相关操作 -- 打印Excel文件中单元格的内容
Set sh = Sheets(1) '或者 Set sh = Sheets("Sheet1")
For i = 1 To 100
Debug.Print sh.Cells(i, 1)
Next
' close the excel file without saving 关闭文件不保存
Workbooks(strWorkBook).Close False
8、激活一个sheet页签或者激活一个excel
Sheets(1).Activate '或者 Sheets("Sheet1").Activate
Workbooks(1).Activate '或者 Workbooks("WorkBook").Activate
9、使用OpenDialog选择一个文件;
Dim OpenFileName As Variant
OpenFileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel,*.xls", MultiSelect:=False)
If OpenFileName = False Then
Exit Sub
End If
Debug.Print OpenFileName
10、使用OpenDialog选择多个文件
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = -1 Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set fl = fs.GetFolder(.SelectedItems(1)).Files
For Each f In fl
Debug.Print f.Path
Next
End If
End With
注意:此处的FileDialog方法Excel2000不支持。
11、如何选择一个路径;
Dim ShellApp As Object
Dim oFolder As Object
Set ShellApp = CreateObject("Shell.Application")
Set oFolder = ShellApp.BrowseForFolder(0, "文件夹选择", 1)
If Not oFolder Is Nothing Then
Debug.Print oFolder.self.Path
End If
12、修改文件属性
Dim fs, filelist, folderlist, f, objFile
Dim oFolder As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set filelist = fs.GetFolder(currentFolder).Files
For Each f In filelist
Set objFile = fs.GetFile(f.Path)
objFile.Attributes = 0 '普通文件
objFile.Attributes = 1 '只读文件
objFile.Attributes = 2 '隐藏文件
objFile.Attributes = 4 '系统文件
objFile.Attributes = 8 '磁盘驱动器卷标
objFile.Attributes = 16 '文件夹或者目录
objFile.Attributes = 32 '上次备份后已更改的文件
objFile.Attributes = 64 '链接或者快捷方式
objFile.Attributes = 128 '压缩文件
Next
以上内容仅供参考,如有纰漏,敬请指正,欢迎与大家交流。