- 浏览: 16162 次
最新评论
VBA 对 文件和文件夹的操作
2010年06月24日
我们在使用Excel VBA进行处理数据时,或多或少会涉及到如何操作文件和文件夹。本节将重点讲述如何新建、打开、删除、复制、移动和重命名文件和文件夹操作等。
对于文件和文件夹操作,我们一般通过下面方法:
。VB命令
。EXCEL对象
。引用其他动态库对象
。API函数
在这里,我们尽可能通过不同的方法来展示如何操作文件和文件夹。注意,这里所涉及的文件一般以Excel为主。
对于如何运用文件之间的处理,如,文本文件、WORD、ACCESS和PPT与EXCEL之间的互访与查询,我们将在下节中讲解。
在本节开始之前,我们需要预备的知识点:
1、如何引用动态工程库。
打开VBE-工具-引用
选择Microsoft Scripting Runtime动态库
下面我们将会频繁用到Scripting.FileSystemObject对象来操作文件和文件夹。
另,此scrrun.dll动态库还包含了Scripting.Dictionary字典对象。
2、前期绑定和后期绑定
我们知道,VB是面向对象化编程,MS提供很多的DLL动态链接库,通过这些对象,我们可以轻松地完成任务。我们可以通过前期绑定或后期绑定来引用DLL库。
1)前期绑定。如同我们在上面用手动引用动态工程库方式,在编译代码前,我们就完成了的绑定。绑定之后,写入下面代码,创建和引用对象:
Sub BandObject()
Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject
DIM FSO NEW Scripting.FileSystemObject End Sub
2)后期绑定。使用CreateObject函数,绑定某一个对象。此时,我们只有在程序运行时,绑定才有效,如,
Sub CrtObject()
Dim ObjFso As Object
Set ObjFso = CreateObject("Scripting.FileSystemObject")
End Sub
3、小结:
1)、前期和后期绑定区别在于定义方式和创建方式不同。
2)、前期绑定的优势在于,可以使用自动列出成员方式,查看对象的方法和属性;而后期绑定无法使用。
3)、小心后期绑定的写法。不是所有的后期绑定都是和前期绑定的对象写法一致。如,对象库:Microsoft Shell Controls And Automation
前期绑定:
Dim oShell As Shell32.Shell
Set oShell = New Shell32.Shell
后期绑定:
Dim oShell As Object
Set oShell = CreateObject("Shell.Application")
一、文件操作
1、新建Excel文件 Excel对象:Add方法:
Sub AddWorkBook() Dim wb As Workbook Set wb = Workbooks.Add End Sub Sub AddFile() Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs ThisWorkbook.Path & "\Temp.xls" wb.Close Set wb = Nothing End Sub 2、打开文件
1)、EXCEL对象:Open方法
直接打开一个工作簿。
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument)
Sub OpenWorkbook() Dim wb As Workbook Dim strWb As String strWb = ThisWorkbook.Path & "\Temp.xls" Set wb = Workbooks.Open(strWb) End Sub Sub OpenWorkbook2() Dim wb As Workbook Dim strWb As String strWb = ThisWorkbook.Path & "\Temp.xls" Set wb = Workbooks.Open(strWb, UpdateLinks:=False) End Sub 2)、Excel对象:OpenText
Sub OpenText() Dim strFile As String Dim i As Long strFile = ThisWorkbook.Path With Application.FileSearch Application.DefaultWebOptions.LoadPictures = False .LookIn = strFile .Filename = "*.html" .Execute If .Execute() > 0 Then For i = 1 To .FoundFiles.Count Workbooks.OpenText .FoundFiles(i) Next End If Application.DefaultWebOptions.LoadPictures = True End With End Sub
3)、Office对象:FileDialog
通过浏览方式打开文件
Sub OpenFile_FileDialog() Dim fd As FileDialog Dim FFs As FileDialogFilters Dim vaItem As Variant Dim myWb As Workbook Set fd = Application.FileDialog(msoFileDialogOpen) With fd Set FFs = .Filters With FFs .Clear .Add "Excel文件", "*.xls;*.xla" End With .AllowMultiSelect = True If .Show = -1 Then For Each vaItem In .SelectedItems Set myWb = Workbooks.Open(vaItem) Next vaItem End If End With End Sub
4)、API函数方式
打开所有类型的文件
Const SW_SHOW = 5 Private Declare Function ShellExecute Lib "shell32.dll" Alias _ "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ ByVal lpFile As String, ByVal lpParameters As String, _ ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Sub OpenFiles() Dim varFName As Variant Dim fn As Variant 'Excel档由Excel开,其它文档由ShellExecute函数开 varFName = Application.GetOpenFilename(, , "开启文档", MultiSelect:=True) If IsArray(varFName) Then For Each fn In varFName If LCase(Right(fn, 3)) "xls" Then ShellExecute 0, "open", fn, "", "", SW_SHOW Else Workbooks.Open (fn) End If Next End If End Sub 1、保存文件
1)、Excel对象:Save
Sub SaveWorkbook()
ThisWorkbook.Save End Sub
2)、Excel对象:SaveAs
Sub SaveAsWorkbook()
Dim strFileName As String
strFileName = ThisWorkbook.Path & "\test.xls"
On Error Resume Next
ThisWorkbook.SaveAs strFileName
End Sub
3)、Excel对象:SaveCopyAs
Sub SaveCopyAsWorkbook()
Dim strFileName As String
strFileName = ThisWorkbook.Path & "\test.xls"
On Error Resume Next
ThisWorkbook.SaveCopyAs strFileName
End Sub
2、判断文件夹是否存在
1)、VB命令:Dir()
Sub FileExist_Dir() Dim strFile As String strFile = ThisWorkbook.Path & "\test.xls" If Dir(strFile) = "" Then MsgBox strFile & " does not Exists" Else MsgBox strFile & " Exist" End If End Sub
2)、FileSystemObject对象:FileExists方法
Sub FileExist_Fso() Dim fso As FileSystemObject Dim strFile As String strFile = ThisWorkbook.Path & "\test.xls" Set fso = New FileSystemObject If fso.FileExists(strFile) Then MsgBox strFile & " Exist" Else MsgBox strFile & " does not Exists" End If End Sub 1、建立文件的桌面快捷方式
WScript 对象:CreateShortCut方法
Sub DesktopShortCut() Dim WSHShell As Object Dim MyShortcut As Object Dim DesktopPath As String Set WSHShell = CreateObject("WScript.Shell") DesktopPath = WSHShell.SpecialFolders("Desktop") Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\" & _ ThisWorkbook.Name & ".lnk") With MyShortcut .TargetPath = ThisWorkbook.FullName .Save End With Set WSHShell = Nothing MsgBox "已经在桌面生成快捷方式." End Sub
2、移动文件
1)、FileSystemObject对象:MoveFilet
Sub MoveFile_fso() Dim fso As New FileSystemObject Dim strSourceFile As String Dim strDestination As String strSourceFile = ThisWorkbook.Path & "\Temp.xls" strDestination = ThisWorkbook.Path & "\MoveFile\Temp.xls" If Not fso.FileExists(strSourceFile) Then MsgBox "File does not Exists.", vbCritical Else fso.MoveFile strSourceFile, strDestination MsgBox "File Move to " & strDestination End If Set fso = Nothing End Sub
2)、Office对象:Name
Sub MoveFile() Dim fso As New FileSystemObject Dim strSourceFile As String Dim strDestination As String On Error GoTo ErrHandle strSourceFile = ThisWorkbook.Path & "\Temp.xls" strDestination = ThisWorkbook.Path & "\MoveFile\Temp.xls" dir(strSourceFile)=""? Name strSourceFile As strDestination Exit Sub ErrHandle: MsgBox Err.Description, vbCritical End Sub 1、复制文件
1)、Office对象:FileCopy
Sub CopyFile() Dim strSfile As String Dim strDfile As String strSfile = ThisWorkbook.Path & "\Temp.xls" strDfile = ThisWorkbook.Path & "\Temp\Temp.xls" FileCopy strSfile, strDfile End Sub
2)、FileSystemObject对象:CopyFile
Sub CopyFile_fso() Dim strSfile As String Dim strDfile As String Dim fso As New FileSystemObject strSfile = ThisWorkbook.Path & "\Temp.xls" strDfile = ThisWorkbook.Path & "\Temp\Temp.xls" fso.CopyFile strSfile, strDfile Set fso = Nothing End Sub
2、关闭文件
Excel对象:Close方法
Sub CloseWorkbook()
ThisWorkbook.Close False End Sub 1、文件重命名
Office对象:Name Public oldNames() As String, newNames() As String Sub ReNameFiles() Dim i As Integer, iCount As Integer Dim Oldname As String, Newname As String Dim strExName As String, strPath As String strExName = ".jpg" strPath = ThisWorkbook.Path & "\Rename Pic\" With Application.FileSearch .NewSearch .LookIn = strPath .SearchSubFolders = False .Filename = "*" & strExName .MatchTextExactly = True .FileType = msoFileTypeAllFiles On Error GoTo ErrH If .Execute() > 0 Then iCount = .FoundFiles.Count MsgBox "There were " & iCount & " file(s) found.", 0 + 64, "系统" ReDim oldNames(iCount) ReDim newNames(iCount) For i = 1 To iCount Newname = i & strExName newNames(i) = CStr(strPath & "\" & Newname) oldNames(i) = CStr(.FoundFiles(i)) Name CStr(oldNames(i)) As newNames(i) Next i Else MsgBox "There were no files found." End If Application.OnUndo "撤销重命名", "UnChangePicName" End With Exit Sub ErrH: MsgBox Err.Description, vbCritical End Sub Sub UnChangePicName() '撤销重命名图片 Dim i As Integer For i = 1 To UBound(newNames) Name newNames(i) As oldNames(i) Next i Application.OnRepeat "重做重命名", "my_Repeat" End Sub Sub my_Repeat() '恢复重命名图片 Dim i As Integer For i = 1 To UBound(newNames) Name oldNames(i) As newNames(i) Next i Application.OnUndo "撤销重命名", "UnChangePicName" End Sub
删除文件
1)、VB语句:Kill
Sub DeleteFile() Dim strFile As String strFile = ThisWorkbook.Path & "\Temp.xls" Kill strFile End Sub Sub DeleteFile2() Dim strFile As String strFile = ThisWorkbook.Path & "\Temp.xls" If Dir(strFile) = "" Then MsgBox strFile & " does not Exists", vbCritical Else Kill strFile End If End Sub
2)、FileSystemObject对象:DeleteFile方法
Sub DeleteFile_Fso() Dim fso As FileSystemObject Dim strFile As String strFile = ThisWorkbook.Path & "\test.xls" Set fso = New FileSystemObject If fso.FileExists(strFile) Then fso.DeleteFile strFile Else MsgBox strFile & " does not Exists" End If Set fso = Nothing End Sub
10、文件自杀
VB语句:Kill Sub KillMe() Application.DisplayAlerts = False ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ThisWorkbook.Close False End Sub
二.文件夹操作
1、新建文件夹
Sub MkDirFolder() Dim strfolder As String strfolder = ThisWorkbook.Path & "\Temp" On Error GoTo ErrHandle MkDir strfolder MsgBox "Create New Folder: " & strfolder, vbInformation On Error GoTo 0 Exit Sub ErrHandle: MsgBox "Folder already Exists.", vbInformation End Sub Sub MakeFolder_fso() Dim fso As New FileSystemObject Dim strfolder As String strfolder = ThisWorkbook.Path & "\Temp" If Not fso.FolderExists(strfolder) Then fso.CreateFolder strfolder MsgBox "Create a Temp folder.", vbInformation Else MsgBox "Folder already Exists.", vbInformation End If Set fso = Nothing End Sub 2、打开文件夹
1)、Shell
Sub ShellFolder() Shell "explorer.exe E:\inbox\", 1 End Sub
2)、引用Microsoft Shell Controls And Automation动态库
Sub OpenFolder() Dim strFolder As String Dim oShell As Shell32.Shell Set oShell = New Shell32.Shell strFolder = "E:\inbox\" oShell.Explore strFolder End Sub
1)后期绑定方式,选择文件夹
Sub SelectFolder() Dim Shapp As Object Dim Path1 As Object Set Shapp = CreateObject("Shell.Application") Set Path1 = Shapp.BrowseForFolder(0, "请选择文件夹", 0, 0) If Path1 Is Nothing Then Exit Sub MsgBox Path1.Self.Path End Sub
2、复制文件夹
FileSystemObject对象:CopyFolder
Sub CopyFile_fso() Dim fso As New FileSystemObject Dim strSfolder As String Dim strDfolder As String strSfolder = ThisWorkbook.Path & "\Temp" strDfolder = ThisWorkbook.Path & "\MoveFile\" fso.CopyFolder strSfolder, strDfolder Set fso = Nothing End Sub
1、移动文件夹
FileSystemObject对象:MoveFolder
Sub MoveFolder_fso() Dim fso As New FileSystemObject Dim strSfolder As String Dim strDfolder As String strSfolder = ThisWorkbook.Path & "\Temp" strDfolder = ThisWorkbook.Path & "\MoveFile\" If Not fso.FolderExists(strSfolder) Then MsgBox " Folder does not Exists.", vbCritical Else fso.MoveFolder strSfolder, strDfolder MsgBox "Folder Move to " & strDfolder End If Set fso = Nothing End Sub
2、删除文件夹
VB语句:RmDir
Sub DeleteFolder() Dim strFolder As String strFolder = ThisWorkbook.Path & "\Temp" On Error GoTo ErrHandle RmDir strFolder MsgBox "Delete Folder: " & strFolder, vbInformation On Error GoTo 0 Exit Sub ErrHandle: MsgBox "Folder does not Exists.", vbCritical End Sub Shell语句
Sub DeleteFolder2() KillFolder ThisWorkbook.Path & "\Temp" End Sub Sub KillFolder(MyFolderPath As String) Shell "cmd.exe /c rmdir /s/q " & Chr(34) & MyFolderPath & Chr(34) End Sub
FileSystemObject对象:DeleteFolder
Sub DeleteFolder_fso() Dim strFolder As String Dim fso As New FileSystemObject strFolder = ThisWorkbook.Path & "\Temp" If fso.FolderExists(strFolder) Then fso.DeleteFolder strFolder Else MsgBox "Folder does not Exists.", vbCritical End If Set fso = Nothing End Sub 1、获取父文件夹名
FileSystemObject对象:ParentFolder
Sub ParentFolderName_fso() Dim fso As New FileSystemObject Dim strPath As String strPath = ThisWorkbook.Path & "\Temp" MsgBox "Path: " & strPath & vbCrLf & vbCrLf & _ "Paren Path: " & fso.GetFolder(strPath).ParentFolder.Name End Sub
VBA :Split函数
Sub ParentFolderName() Dim arr As Variant Dim strPath As String strPath = ThisWorkbook.Path & "\Temp" arr = Split(strPath, "\") MsgBox "Path: " & strPath & vbCrLf & vbCrLf & _ "Paren Path: " & arr(UBound(arr) - 1) End Sub
2、文件夹重命名
FileSystemObject对象:Folder.name
Dim OldFolder As String, NewFolder As String Sub ReNameFolder_fso() Dim fso As New FileSystemObject Dim oFolder As Folder Dim strOldFolder As String Dim strNewFolder As String strOldFolder = ThisWorkbook.Path & "\Temp" strNewFolder = "New Temp" If Not fso.FolderExists(strOldFolder) Then MsgBox "Folder does not Exist.", vbCritical Else Set oFolder = fso.GetFolder(strOldFolder) oFolder.Name = strNewFolder End If End Sub
VB语句:Name Sub ReNameFolder() OldFolder = ThisWorkbook.Path & "\Temp" NewFolder = ThisWorkbook.Path & "\New Temp" Name OldFolder As NewFolder End Sub Sub UnChangeReNameFolder() Name NewFolder As OldFolder End Sub
发表评论
-
鼠标键盘模拟 【转】
2012-01-20 02:08 1141鼠标键盘模拟 【转】 2011年06月07日 指定hwn ... -
关于VB中的 comct132.ocx问题!
2012-01-20 02:08 927关于VB中的 comct132.ocx问题! 2010年11 ... -
快播3在线安装程序变身快播下载器 vb 源代码以及调用方法
2012-01-20 02:08 1529快播3在线安装程序变身快播下载器 vb 源代码以及调用方法 ... -
vb+Flex的关于FlashPlayer的检测
2012-01-20 02:07 649vb+Flex的关于FlashPlayer的 ... -
懂得赞美的女人最受欢迎
2012-01-19 09:58 804懂得赞美的女人最受欢迎 2010年11月17日 ... -
精彩赞美词分享
2012-01-19 09:58 1277精彩赞美词分享 2011年0 ... -
赞美女人的词语
2012-01-19 09:58 592赞美女人的词语 2011年05月02日 漂亮的叫美女, ... -
赞美女人的词语
2012-01-19 09:58 918赞美女人的词语 2011年1 ... -
关于网络
2012-01-17 02:38 586关于网络 2011年04月20日 ... -
vs2008使用点滴
2012-01-17 02:37 1100vs2008使用点滴 2011年02 ... -
【转】C# 相对路径
2012-01-17 02:30 989【转】C# 相对路径 2010年12月22日 一、获取当 ... -
flex -google map API
2012-01-15 22:14 648flex -google map API 2010年02月2 ... -
用flex开发 google map应用程式
2012-01-15 22:14 650用flex开发 google map应用程式 2009年09 ... -
什么是WAP?
2012-01-15 22:14 671什么是WAP? 2009年08月30 ... -
AS3 框架 不断更新
2012-01-15 22:14 661AS3 框架 不断更新 2011 ... -
Flex之表单验证
2012-01-15 22:14 903Flex之表单验证 2010年04 ...
相关推荐
利用excel中的vba可以对电脑中的文件及文件夹做一些常用的操作。 包括复制、重命名、删除等,其中一些简单的示例总结如下。 希望对一些经常需要批量处理文件的朋友有所帮助,也希望感兴趣的朋友多多指教!
VBA的针对文件夹文件的常用操作,日文注释,对日行业的话看起来可能比较顺利
Excel VBA_文本文件 文件夹操作实例
ExcelVBA_文本文件和文件夹操作实例集锦.doc
VBA文件及文件夹操作[参照].pdf
VBA做的小工具,将指定文件夹下文件放入匹配名字的文件夹内,文件属于复制移动
本资源实现用VBA进行文件操作和自动添加sheet和超链接,包括:打开文件,写入文件,创建文件等
自己学习VBA编程时,利用excel内VBA宏编写的简单命令,涉及一些基础的操作,亲测可用,初学者可以借鉴,也能利用其解决一些简单的办公问题。
数据有效性验证 与文件操作_读取固定三层目录中文件名文件夹初始路径写死
本资源实现了用VBA(宏)代价遍历文件夹中的csv文件,修改代码也可遍历其他格式的excel文件,并实现了将遍历的结果整合到一个新的excel文件中。
Excel VBA 操作 拆分当前Excel表中 / 所选取文件夹所有子文件中 所有sheet保存到对应文件夹路径中拆分表内 操作快捷,简单, 无需修改就可以使用
利用文件对象模型FSO操作文件夹.zip源码EXCEL VBA宏编程Excel VBA实用技巧范例下载利用文件对象模型FSO操作文件夹.zip源码EXCEL VBA宏编程Excel VBA实用技巧范例下载利用文件对象模型FSO操作文件夹.zip源码EXCEL VBA...
具体向读者讲述了vba语言基础,vba程序设计网络教学,xml与asp技术,利用vba操纵文件和文件夹,利用vba控制其它应用程序,自定义集合和类模块,调试vba过程和处理错误等等,希望对网友们有帮助。
VBA源码,可以看源码
对菜单项和工具栏项增加状态栏帮... 在右键菜单中增加条目 第七章 使用事件 了解AutoCAD中的事件 编写事件处理器的方法 处理应用程序级事件 处理文档级事件 处理对象级事件 第八章 在三维空间下工作 指定三...
利用excelvba实现对文件夹下的dwg文件进行文字替换、块插入等批量操作,可以看源码
然后找到学生文件所在的文件夹,进入后ctrl+A全选学生的word操作文件,就会自动逐个批改学生的操作题。并学生的文件名与其对应分数显示在批改文件中的1、2列。 建议让学生操作完毕后将其提交文件以自己的序号+名字或...
Excel-VBA实用技巧范例-利用文件对象模型FSO操作文件夹.zip
包括管理Excel VBA常见对象(Alrolication对象、Workbook对象、Worksheet对象、Range对象、Chart对象、Shape对象、窗体控件对象以及对象事件)...VBA工程、操作文件和文件夹、操作其他Office应用程序和Windows应用程序等...
文件夹里建立了很多子文件夹,想要批量把子文件夹的名称提取出来,手动操作挺麻烦的,因此做了这个小程序。 把此excel文件放到想要提取的文件夹里,打开,点击“生成项目名称”这个按钮,然后会自动把文件夹里的所有...