Excel-VBA操作文件四大方法之一(Excel-vba is one of the four methods to operate files)

在我们日常使用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代码。具体方法就是选择“文件——打开”,然后选择打开文本文件,就会出现文本导入向导,一步一步执行完,直到文本打开后,停止录制。

以下是录制宏得到的代码:

Sub Macro1()

‘ Macro1 Macro

Workbooks.OpenText Filename:=”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

End Sub

在实际编程中只要做相应的修改就可以使用了。

3、打开其他文件

利用Excel对象还可以打开XML文件和一些数据库(如Access)文件,对应XML文件,需要Excel2003以上的版本。

OpenXML方法的语法如下:

Workbooks.OpenXML(Filename, Stylesheets, LoadOption)

FileName String 类型,必需。要打开的文件名。

Stylesheets Variant 类型,可选。单个值或值的数组,用于指定要应用哪些 XSL 转换 (XSLT) 样式表处理指令。

LoadOption Variant 类型,转换。指定 Excel 打开 XML 数据文件的方式。可为 XlXmlLoadOption 常量之一。

XlXmlLoadOption 可为以下 XlXmlLoadOption 常量之一:

xlXmlLoadImportToList 将 XML 数据文件的内容置于 XML 列表中。

xlXmlLoadMapXml 在“XML 结构”任务窗格中显示 XML 数据文件的架构。

xlXmlLoadOpenXml 打开 XML 数据文件。文件的内容将展开。

xlXmlLoadPromptUser 提示用户选择打开文件的方式。

示例

下面的代码打开了 XML 数据文件“customers.xml”并在 XML 列表中显示了此文件的内容。

Sub UseOpenXML()

Application.Workbooks.OpenXML _

Filename:=”customers.xml”, _

LoadOption:=xlXmlLoadImportToList

End Sub

OpenDatabase 方法语法如下:

Workbooks.OpenDatabase(FileName, CommandText, CommandType, BackgroundQuery, ImportDataAs)

FileName String 类型,必需。连接字符串。

CommandText Variant 类型,可选。查询的命令文本。

CommandType Variant 类型,可选。查询的命令类型。以下是可用的命令类型:Default、SQL 和 Table。

BackgroundQuery Variant 类型,可选。查询的背景。

ImportDataAs Variant 类型,可选。确定查询的格式。

示例

本示例中,Excel 打开了“northwind.mdb”文件。

Sub OpenDatabase()

Workbooks.OpenDatabase FileName:=”C:\northwind.mdb”

End Sub

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帮助,使用都比较简单。

示例

本示例新建一个工作簿,提示用户输入文件名,然后保存该工作簿。

Set NewBook = Workbooks.Add

Do

fName = Application.GetSaveAsFilename

Loop Until fName <> False

NewBook.SaveAs Filename:=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”).Close SaveChanges:=False

本示例关闭所有打开的工作簿。如果某个打开的工作簿有改变,Microsoft Excel 将显示询问是否保存更改的对话框和相应提示。

Workbooks.Close

6、综合实例

假如F盘有一个Excel文件test.xls,现在有另一个Excel文件要访问test.xls的数据,我们来看用VBA代码如何操作。代码如下: Public Sub test()

Application.ScreenUpdating = False

Workbooks.Open “f:\test.xls”

ThisWorkbook.Sheets(1).Range(“b1”) = ActiveWorkbook.Sheets(1).Range(“a2”)

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

首先关闭屏幕刷新,是为了防止test.xls在打开时被看见(有时候还是看的见)。打开后,见test.xls的Sheet1的单元格A2中的值赋给当前工作簿的Sheet1的单元格B2,然后关闭test.xls。

当要打开的工作簿不确定的时候,可以通过调用打开对话框来让用户自己选择。

可改为如下:

Public Sub test()

Application.ScreenUpdating = False

Dim Filename as String

Filename = Application.GetOpenFileName

Workbooks.Open Filename

ThisWorkbook.Sheets(1).Range(“b1”) = ActiveWorkbook.Sheets(1).Range(“a2”)

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

7、总结

利用Excel对象的方法进行文件操作是最简单,也是最方便的,适合初学者。对于Excel文件格式,如果我们仅仅是读取其表格中的内容,这种方法也是首选。对于文本文件的操作,使用第二种方法比较方便,若要将文本转换成表格,那么使用此方法也是合适的。

如何在d:\test.xls已打开的情况下不让以下的语句再去创建一个test.xls的实例(虽然它标识为只读),

并给出文件已打开的提示.

先做一个判断!判断该工作簿是否打开了?

例如:

Set xlexcel = CreateObject(“excel.application”)

dim wb as object

set wb = xlexcel.workbooks(“test.xls”)

if wb is nothing then

msgbox “工作簿未打开!”

xlexcel.Workbooks.Open “d:\test.xls” xlexcel.Visible = True

end if

原文地址

————————

In our daily use of Excel, we not only use the data of the current excel file, but also often need to access other data files. These data files may be Excel files, text files or database files. Friends often ask how to operate these data files in VBA code? This paper systematically introduces the method of using VBA to operate data files in Excel. This paper mainly introduces four common methods:

1. Use Excel objects to process files;

2. Using VBA file processing statements to process files;

3. Use filesystembobject object to process files;

4. Use API functions to process files.

Of course, for database files, you can also use ADO + SQL. However, some predecessors in the forum have introduced this method in detail, so this article will not repeat it.

1、 Using Excel objects to process files

It is the most convenient and simple to use the method of Excel object to operate the file.

We mainly use the methods of workbooks collection and Workbook object to manipulate files.

1. Open excel file

We can open an excel Workbook with the workbooks. Open method.

Workbooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Where filename is a required parameter, indicating the name of the workbook to be opened. If no path is specified, it represents the current path. The other 14 are optional parameters. Except for password parameters, others are rarely used. For specific meaning, please refer to the help of VBA.

Example:

Workbooks.Open “F:\test.xls”

You can open the test.xls file of disk F.

2. Open text file

You can also open a text file using the open method, but the opentext method is recommended. This method is to load a text file and process it as a workbook containing a single worksheet, and then put the data of the text file in this worksheet. The complete syntax is as follows:

Workbooks.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

For the specific meaning of the above parameters, please refer to the help of VBA, which will not be repeated here. In practical programming, it is generally unnecessary to deal with these complex parameters. You can get the VBA code to open a text file by recording macros. The specific method is to select “file – Open”, then select open text file, and the text Import Wizard will appear. Step by step, until the text is opened, stop recording.

The following is the code from recording the macro:

Sub Macro1()

‘ Macro1 Macro

Workbooks.OpenText Filename:=”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

End Sub

In actual programming, it can be used as long as corresponding modifications are made.

3. Open another file

Using Excel objects, you can also open XML files and some database (such as access) files. The corresponding XML files require versions above Excel 2003.

The syntax of the openxml method is as follows:

Workbooks.OpenXML(Filename, Stylesheets, LoadOption)

Filename string type, required. The file name to open.

Stylesheets variant type, optional. A single value or array of values that specifies which XSL transformation (XSLT) stylesheet processing instructions to apply.

Loadoption variant type, conversion. Specifies how excel opens the XML data file. Can be one of the xmlxmlloadoption constants.

XlXmlLoadOption 可为以下 XlXmlLoadOption 常量之一:

Xlxmlloadimporttolist places the contents of the XML data file in the XML list.

Xmlloadmapxml displays the schema of the XML data file in the XML structure task pane.

Xmlloadopenxml opens the XML data file. The contents of the file expand.

Xlxmlloadpromptuser prompts the user to choose how to open the file.

Example

The following code opens the XML data file “customers. XML” and displays the contents of this file in the XML list.

Sub UseOpenXML()

Application.Workbooks.OpenXML _

Filename:=”customers.xml”, _

LoadOption:=xlXmlLoadImportToList

End Sub

The opendatabase method syntax is as follows:

Workbooks.OpenDatabase(FileName, CommandText, CommandType, BackgroundQuery, ImportDataAs)

Filename string type, required. Connection string.

Commandtext variant type, optional. The command text for the query.

Commandtype variant type, optional. The type of command to query. The following command types are available: default, SQL, and table.

Backgroundquery variant type, optional. The background of the query.

Importdataas variant type, optional. Determines the format of the query.

Example

In this example, excel opens the “Northwind. MDB” file.

Sub OpenDatabase()

Workbooks.OpenDatabase FileName:=”C:\northwind.mdb”

End Sub

4. Save file

The file is saved using the save or SaveAs methods of the workbook object.

The save method is simple to use, and the syntax is

expression.Save,expression是某个Workbook对象。

如:ActiveWorkbook.Save

The currently active workbook is saved.

If you are saving the workbook for the first time or want to save as, use the SaveAs method to specify a file name for the file.

Its syntax is:

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

Refer to VBA help for specific parameter meanings, which are relatively simple to use.

Example

This example creates a new workbook, prompts the user to enter a file name, and then saves the workbook.

Set NewBook = Workbooks.Add

Do

fName = Application.GetSaveAsFilename

Loop Until fName <> False

NewBook.SaveAs Filename:=fName

Application.getsaveasfilename calls up the standard save as dialog box, obtains the user file name, but does not really save any files, and then saves the files with code. In addition, application.getopenfilename can call up the standard “open” dialog box.

5. Close file

You can close the file using the workbooks collection or the close method of the workbook object. The former closes all open workbooks, and the latter closes specific workbooks.

The syntax of the close method of the workbook object is:

expression.Close(SaveChanges, Filename, RouteWorkbook)

The saveChanges parameter indicates whether to save changes. For many operations that do not need to be changed, it can be set to false to avoid the pop-up dialog box prompting to save changes.

Filename optional. Save your changes with this file name.

Routeworkbook optional. If the specified workbook does not need to be delivered to the next recipient (there is no delivery list or has been delivered), this parameter is ignored.

Example

This example closes book1.xls and discards all changes to this workbook.

Workbooks(“BOOK1.XLS”).Close SaveChanges:=False

This example closes all open workbooks. If an open workbook changes, Microsoft Excel will display a dialog box asking whether to save the changes and corresponding prompts.

Workbooks.Close

6. Comprehensive example

If there is an excel file test.xls in disk F, and now there is another excel file to access the data of test.xls, let’s see how to operate with VBA code. The code is as follows: public sub test ()

Application.ScreenUpdating = False

Workbooks.Open “f:\test.xls”

ThisWorkbook.Sheets(1).Range(“b1”) = ActiveWorkbook.Sheets(1).Range(“a2”)

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

First, turn off screen refresh to prevent test.xls from being seen when it is opened (sometimes). After opening, assign the value in cell A2 of Sheet1 of test.xls to cell B2 of Sheet1 of the current workbook, and then close test.xls.

When the workbook to be opened is uncertain, you can call the open dialog box to let users choose by themselves.

It can be changed as follows:

Public Sub test()

Application.ScreenUpdating = False

Dim Filename as String

Filename = Application.GetOpenFileName

Workbooks.Open Filename

ThisWorkbook.Sheets(1).Range(“b1”) = ActiveWorkbook.Sheets(1).Range(“a2”)

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

7. Summary

Using Excel object method for file operation is the simplest and most convenient, suitable for beginners. For Excel file format, this method is also the first choice if we only read the contents in the table. For the operation of text files, the second method is more convenient. If you want to convert text into tables, this method is also appropriate.

How to prevent the following statement from creating an instance of test.xls when D: \ test.xls has been opened (although it is marked as read-only),

And give a prompt that the file has been opened

Make a judgment first! Determine whether the workbook is open?

For example:

Set xlexcel = CreateObject(“excel.application”)

dim wb as object

set wb = xlexcel.workbooks(“test.xls”)

if wb is nothing then

Msgbox “workbook not open!”

xlexcel.Workbooks.Open “d:\test.xls” xlexcel.Visible = True

end if

Original address