ExcelVBA操作文件四大方法.docx
《ExcelVBA操作文件四大方法.docx》由会员分享，可在线阅读，更多相关《ExcelVBA操作文件四大方法.docx（59页珍藏版）》请在冰豆网上搜索。
ExcelVBA操作文件四大方法
Excel-VBA操作文件四大方法
Excel-VBA操作文件四大方法之一
在我们日常使用Excel的时候，不仅会用到当前Excel文件的数据，还经常需要访问其他的数据文件。
这些数据文件可能是Excel文件、文本文件或数据库文件等。
经常有朋友会问如何在vba代码里操作这些数据文件？
本文就系统地介绍一下在Excel中应用VBA操作数据文件的方法。
本文主要介绍四种常用的方法：
1、利用Excel对象来处理文件；
2、利用VBA文件处理语句来处理文件；
3、利用FileSystemObject对象来处理文件；
4、利用API函数来处理文件。
当然对于数据库文件，还可以利用ADO＋SQL的方法操作，不过论坛已经有前辈详细介绍过此类方法，本文就不再重复了。
一、利用Excel对象来处理文件
利用Excel对象自带的方法来操作文件是最方便，也是最简单的。
我们主要利用Workbooks集合和Workbook对象的方法来操作文件。
1、打开Excel文件
我们可以用Workbooks.Open方法打开一个Excel工作簿。
Workbooks.Open（FileName,UpdateLinks,ReadOnly,Format,Password,WriteResPassword,IgnoreReadOnlyRecommended,Origin,Delimiter,Editable,Notify,Converter,AddToMru,Local,CorruptLoad）
其中FileName是必选的参数，表示要打开的工作簿名，如果没有指定路径，则代表当前路径。
另外14个是可选参数，除了密码参数，其他的一般很少用。
具体的含义可以参看VBA的帮助。
例：
Workbooks.Open"F:
\test.xls"
可以打开F盘的test.xls文件。
2、打开文本文件
使用Open方法也可以打开文本文件，但建议使用OpenText方法。
此方法是载入一个文本文件，并将其作为包含单个工作表的工作簿进行分列处理，然后在此工作表中放入经过分列处理的文本文件数据。
完整语法如下：
Workbooks.OpenText（FileName,Origin,StartRow,DataType,TextQualifier,ConsecutiveDelimiter,Tab,Semicolon,Comma,Space,Other,OtherChar,FieldInfo,TextVisualLayout,DecimalSeparator,ThousandsSeparator,TrailingMinusNumbers,Local）
关于以上参数的具体含义可以参看VBA的帮助，这里就不重复了。
在实际的编程中，一般无需对这些复杂的参数进行处理。
可以通过录制宏来得到打开一个文本文件的VBA代码。
具体方法就是选择“文件——打开”，然后选择打开文本文件，就会出现文本导入向导，一步一步执行完，直到文本打开后，停止录制。
以下是录制宏得到的代码:
SubMacro1（）
'
'Macro1Macro
'宏由MCSYSTEM录制，时间:
2007-3-29
'
'
Workbooks.OpenTextFilename:
="F:
\CallWindowProc.txt",Origin:
=xlWindows,_
StartRow:
=1,DataType:
=xlDelimited,TextQualifier:
=xlDoubleQuote,_
ConsecutiveDelimiter:
=False,Tab:
=True,Semicolon:
=False,Comma:
=False_
Space:
=False,Other:
=False,FieldInfo:
=Array（1,1）,_
TrailingMinusNumbers:
=True
EndSub
在实际编程中只要做相应的修改就可以使用了。
3、打开其他文件
利用Excel对象还可以打开XML文件和一些数据库（如Access）文件，对应XML文件，需要Excel2003以上的版本。
OpenXML方法的语法如下：
Workbooks.OpenXML（Filename,Stylesheets,LoadOption）
FileNameString类型，必需。
要打开的文件名。
StylesheetsVariant类型，可选。
单个值或值的数组，用于指定要应用哪些XSL转换（XSLT）样式表处理指令。
LoadOptionVariant类型，转换。
指定Excel打开XML数据文件的方式。
可为XlXmlLoadOption常量之一。
XlXmlLoadOption可为以下XlXmlLoadOption常量之一：
xlXmlLoadImportToList将XML数据文件的内容置于XML列表中。
xlXmlLoadMapXml在“XML结构”任务窗格中显示XML数据文件的架构。
xlXmlLoadOpenXml打开XML数据文件。
文件的内容将展开。
xlXmlLoadPromptUser提示用户选择打开文件的方式。
示例
下面的代码打开了XML数据文件“customers.xml”并在XML列表中显示了此文件的内容。
SubUseOpenXML（）
Application.Workbooks.OpenXML_
Filename:
="customers.xml",_
LoadOption:
=xlXmlLoadImportToList
EndSub
OpenDatabase方法语法如下：
Workbooks.OpenDatabase（FileName,CommandText,CommandType,BackgroundQuery,ImportDataAs）
FileNameString类型，必需。
连接字符串。
CommandTextVariant类型，可选。
查询的命令文本。
CommandTypeVariant类型，可选。
查询的命令类型。
以下是可用的命令类型：
Default、SQL和Table。
BackgroundQueryVariant类型，可选。
查询的背景。
ImportDataAsVariant类型，可选。
确定查询的格式。
示例
本示例中，Excel打开了“northwind.mdb”文件。
SubOpenDatabase（）
Workbooks.OpenDatabaseFileName:
="C:
\northwind.mdb"
EndSub
4、保存文件
文件的保存使用Workbook对象的Save或SaveAs方法。
Save方法使用简单，语法为
expression.Save，expression是某个Workbook对象。
如：
ActiveWorkbook.Save
即保存当前活动工作簿。
如果是第一次保存工作簿或要另存为，请使用SaveAs方法为该文件指定文件名。
其语法为：
expression.SaveAs（FileName,FileFormat,Password,WriteResPassword,ReadOnlyRecommended,CreateBackup,AccessMode,ConflictResolution,AddToMru,TextCodepage,TextVisualLayout,Local）
具体参数含义可参看VBA帮助，使用都比较简单。
示例
本示例新建一个工作簿，提示用户输入文件名，然后保存该工作簿。
SetNewBook=Workbooks.Add
Do
fName=Application.GetSaveAsFilename
LoopUntilfName<>False
NewBook.SaveAsFilename:
=fName
Application.GetSaveAsFilename为调出标准的“另存为”对话框，获取用户文件名，但并不真正保存任何文件，然后使用代码保存文件。
还有Application.GetOpenFileName可以调出标准的“打开”对话框。
5、关闭文件
关闭文件可以使用Workbooks集合或Workbook对象的Close方法。
前者是关闭所有打开的工作簿，后者关闭特定的工作簿。
Workbook对象的Close方法语法为：
expression.Close（SaveChanges,Filename,RouteWorkbook）
SaveChanges参数表示是否保存更改，对许多不需要更改的操作，可设置为False以免弹出保存更改提示的对话框。
FileName可选。
以此文件名保存所做的更改。
RouteWorkbook可选。
如果指定工作簿不需要传送给下一个收件人（没有传送名单或已经传送），则忽略该参数。
示例
本示例关闭Book1.xls，并放弃所有对此工作簿的更改。
Workbooks（"BOOK1.XLS"）.CloseSaveChanges:
=False
本示例关闭所有打开的工作簿。
如果某个打开的工作簿有改变，MicrosoftExcel将显示询问是否保存更改的对话框和相应提示。
Workbooks.Close
6、综合实例
假如F盘有一个Excel文件test.xls，现在有另一个Excel文件要访问test.xls的数据，我们来看用VBA代码如何操作。
代码如下：
PublicSubtest（）
Application.ScreenUpdating=False
Workbooks.Open"f:
\test.xls"
ThisWorkbook.Sheets
（1）.Range（"b1"）=ActiveWorkbook.Sheets
（1）.Range（"a2"）
ActiveWorkbook.Close
Application.ScreenUpdating=True
EndSub
首先关闭屏幕刷新，是为了防止test.xls在打开时被看见（有时候还是看的见）。
打开后，见test.xls的Sheet1的单元格A2中的值赋给当前工作簿的Sheet1的单元格B2，然后关闭test.xls。
当要打开的工作簿不确定的时候，可以通过调用打开对话框来让用户自己选择。
可改为如下：
PublicSubtest（）
Application.ScreenUpdating=False
DimFilenameasString
Filename=Application.GetOpenFileName
Workbooks.OpenFilename
ThisWorkbook.Sheets
（1）.Range（"b1"）=ActiveWorkbook.Sheets
（1）.Range（"a2"）
ActiveWorkbook.Close
Application.ScreenUpdating=True
EndSub
7、总结
利用Excel对象的方法进行文件操作是最简单，也是最方便的，适合初学者。
对于Excel文件格式，如果我们仅仅是读取其表格中的内容，这种方法也是首选。
对于文本文件的操作，使用第二种方法比较方便，若要将文本转换成表格，那么使用此方法也是合适的。
如何在d:
\test.xls已打开的情况下不让以下的语句再去创建一个test.xls的实例（虽然它标识为只读）,
并给出文件已打开的提示.
先做一个判断！
判断该工作簿是否打开了？
例如：
Setxlexcel=CreateObject（"excel.application"）
dimwbasobject
setwb=xlexcel.workbooks（"test.xls"）
ifwbisnothingthen
msgbox"工作簿未打开！
"
xlexcel.Workbooks.Open"d:
\test.xls"
xlexcel.Visible=True
endif
Excel-VBA操作文件四大方法之二
二、利用VBA文件处理语句来处理文件
VBA包含了许多用于文件操作的语句和函数，可以满足绝大多数情况下的文件操作要求。
下面我们按照操作目的进行一一介绍。
（一）文件处理
1.Name语句
语法：
NameoldpathnameAsnewpathname
功能：
重命名一个文件、目录、或文件夹，移动一个文件。
说明：
在一个已打开的文件上使用Name，将会产生错误。
进行文件操作时，一定要注意错误处理。
示例：
OnErrorResumeNext'错误处理
Name"f:
\TEST.xls"As"f:
\TEST123.xls"'重命名
Name"f:
\TEST.xls"As"f:
\dll\TEST.xls"'移动文件
Name"f:
\TEST.xls"As"d:
\TEST123.xls"'跨驱动器移动并重命名文件
注意：
Name不能移动一个目录或文件夹。
2、FileCopy语句
语法：
FileCopysource,destination
功能：
复制一个文件。
说明：
如果对一个已打开的文件使用FileCopy语句，则会产生错误。
示例：
FileCopy"f:
\TEST.xls","e:
\TEST.xls"'从F盘复制TEST.xls到E盘
3、Kill语句
语法：
Killpathname
功能：
从磁盘中删除文件。
说明：
Kill支持多字符（*）和单字符（?
）的统配符来指定多重文件。
如果使用Kill来删除一个已打开的文件，则会产生错误。
示例：
Kill"f:
\TEST.xls"’删除F盘的TEST.xls文件
Kill"f:
\*.xls"'删除F盘所有xls文件
4、GetAttr函数
语法：
GetAttr（pathname）
功能：
获取一个文件、目录、或文件夹的属性。
返回一个Integer值。
返回值
由GetAttr返回的值，是下面这些属性值的总和：
常数值描述
vbNormal0常规
vbReadOnly1只读
vbHidden2隐藏
vbSystem4系统文件
vbDirectory16目录或文件夹
vbArchive32存档文件
vbalias64指定的文件名是别名。
只在Macintosh中可用。
说明：
若要判断是否设置了某个属性，在GetAttr函数与想要得知的属性值之间使用And运算符与逐位比较。
如果所得的结果不为零，则表示设置了这个属性值。
示例：
Debug.PrintGetAttr（"F:
\test.txt"）'若为存档文件，在立即窗口可看到值为32
Debug.PrintGetAttr（"F:
\test.txt"）'将属性—高级—可存档文件的勾去掉后，值为0
为判断一个文件是否只读，可用下法:
Debug.PrintGetAttr（"F:
\test.txt"）AndvbReadOnly
若值非零，说明时只读的。
5、SetAttr语句
语法：
SetAttrpathname,attributes
功能：
为一个文件设置属性。
说明：
如果想要给一个已打开的文件设置属性，则会产生运行时错误。
示例：
SetAttr"F:
\test.txt",vbHidden'设置隐藏属性。
SetAttr"F:
\test.txt",vbHidden+vbReadOnly'设置隐藏并只读。
6、FileLen函数
语法：
FileLen（pathname）
功能：
获取一个文件的长度，单位是字节。
说明：
当调用FileLen函数时，不需要打开文件，如果所指定的文件已经打开，则返回的值是这个文件在打开前的大小。
7、FileDateTime函数
语法：
FileDateTime（pathname）
功能：
获取一个文件被创建或最后修改后的日期和时间。
示例：
Debug.PrintFileDateTime（"F:
\TEST.xls"）'在立即窗口可看到2007-3-2919:
28:
27
（二）目录处理
1、CurDir函数
语法：
CurDir[（drive）]
功能：
返回当前的路径。
说明：
drive参数是可选的，它指定一个存在的驱动器。
如果没有指定驱动器，或drive是零长度字符串（""），则CurDir会返回当前驱动器的路径。
示例：
Debug.PrintCurDir'返回“C:
\DocumentsandSettings\yc\MyDocuments”。
Debug.PrintCurDir（"C"）'返回“C:
\DocumentsandSettings\yc\MyDocuments”。
Debug.PrintCurDir（"D"）'返回“D:
\”。
2、ChDir语句
语法：
ChDirpath
功能：
改变当前的目录或文件夹。
说明：
ChDir语句改变缺省目录位置，但不会改变缺省驱动器位置。
缺省驱动器一般是C。
示例：
ChDir"D:
\temp"
Debug.PrintCurDir'返回“C:
\DocumentsandSettings\yc\MyDocuments”。
Debug.PrintCurDir（"D"）'返回“D:
\temp”。
与上例比较，此时D盘的当前目录已经变为“D:
\temp”，但是缺省驱动器还是C。
3、ChDrive语句
语法：
ChDrivedrive
功能：
改变当前的驱动器。
说明：
如果使用零长度的字符串（""），则当前的驱动器将不会改变。
如果drive参数中有多个字符，则ChDrive只会使用首字母。
示例：
ChDrive"D"
ChDir"D:
\temp"
Debug.PrintCurDir'返回“D:
\temp”。
Debug.PrintCurDir（"D"）'返回“D:
\temp”。
与上例比较，用CurDir返回的是“D:
\temp”，当前驱动器已经变为D了。
4、Dir函数
语法：
Dir[（pathname[,attributes]）]
两个参数都是可选的，attributes表示文件属性。
功能：
返回一个文件名、目录名或文件夹名称，它必须与指定的模式或文件属性、或磁盘卷标相匹配。
说明：
在第一次调用Dir函数时，必须指定pathname，否则会产生错误。
如果也指定了文件属性，那么就必须包括pathname。
Dir会返回匹配pathname的第一个文件名。
若想得到其它匹配pathname的文件名，再一次调用Dir，且不要使用参数。
如果已没有合乎条件的文件，则Dir会返回一个零长度字符串（""）。
一旦返回值为零长度字符串，并要再次调用Dir时，就必须指定pathname，否则会产生错误。
不必访问到所有匹配当前pathname的文件名，就可以改变到一个新的pathname上。
但是，不能以递归方式来调用Dir函数。
以vbDirectory属性来调用Dir不能连续地返回子目录。
示例：
Debug.PrintDir（"F:
\TEST.xls"）’返回"TEST.xls"
Debug.PrintDir（"F:
\*.xls"）’返回按条件第一个找到的文件名。
Debug.PrintDir（"F:
\*.txt",vbReadOnly）’返回第一个只读的txt文件
以下过程可显示C盘根目录下的所有目录.
SubDirC（）
MyPath="c:
\"
MyName=dir（MyPath,vbDirectory）'找寻第一项。
DoWhileMyName<>""'开始循环。
'跳过当前的目录及上层目录。
IfMyName<>"."AndMyName<>".."Then
'使用位比较来确定MyName代表一目录。
If（GetAttr（MyPath&MyName）AndvbDirectory）=vbDirectoryThen
Debug.PrintMyName'如果它是一个目录，将其名称显示出来。
EndIf
EndIf
MyName=dir'查找下一个目录。
Loop
EndSub
以下过程利用递归可以查找目录和子目录下的所有文件。
PublicSubFindFile（mPathAsString,OptionalsFileAsString=""）
OnErrorResumeNext
DimsAsString,sDir（）AsString
DimiAsLong,dAsLong
IfRight（mPath,1）<>"\"Then
mPath=mPath&"\"
EndIf
'查找目录下的文件
s=dir（mPath&sFile,vbArchive+vbDirectory+vbHidden+vbNormal+vbReadOnly+vbSystem）
DoWhiles<>""
Debug.PrintmPath&s
s=dir
Loop
'查找目录下的子目录
s=dir（mPath,vbArchive+vbDirectory+vbHidden+vbNormal+vbReadOnly+vbSystem）
DoWhiles<>""
Ifs<>"."Ands<>".."Then
If（GetAttr（mPath&s）AndvbDirectory）=vbDirectoryThen
d=d+1
ReDimPreservesDir（d）
sDir（d）=mPath&s
EndIf
EndIf
s=dir
Loop
'开始递归
Fori=1Tod
FindFilesDir（d）&"\"
Next
EndSub
5、MkDir语句
语法：
MkDirpath
功能：
创建一个新的目录或文件夹。
说明：
path可以包含驱动器。
如果没有指定驱动器，则MkDir会在当前驱动器上创建新的目录或文件夹。
示例：
MkDir"MYDIR"'在当前目录建立新的目录或文件夹。
6、RmDir语句
语法：
RmDirpath
功能：
删除一个存在的目录或文件夹。
说明：
如果想要使用RmDir来删除一个含有文件的目录或文件夹，则会发生错误。
在试图删除目录或文件夹之前，先使用Kill语句来删除所有文件。
示例：
RmDir"MYDIR"'将MYDIR删除。
（三）处理文本文件
1、Open语句
语法：
OpenpathnameFormode[Accessaccess][
升级会员 