File Operations in Excel VBA: Using Workbooks and Worksheets Objects

During daily use of Microsoft Excel, we don’t only access data on active Excel document, but also access data on other types of documents, which may be Excel documents, text documents or database files. Considering many friends have trouble on how to operate data with VBA, this serial article will give systematical introductions on four methods to access data using VBA in Microsoft Excel. The first is to use Workbooks and Worksheets object. It’s very convenient to use Excel’s Workbooks Collection and Workbook object to access external data.

1. Opening an Excel Documents
Here we use Workbooks.Open method to open an Excel Document.

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

FileName is necessary; it’s the file name of the workbook to open. For the other 14 optional arguments, Password is used a little commonly.

Example:

Workbooks.Open “F:\MyExcel.xls”

This code could open MyExcel.xls from the Local Disk (F:).

2. Opening Text Files
You could use Workbooks.Open method to open a text file also, however, OpenText method is recommended. The complete syntax is as below.

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

The OpenText method could load and split text data into columns and store them in a single worksheet in a workbook.
However, in actual situation, it’s unnecessary to set complicated arguments. You’d better record a macro to load text data into Excel. Here are the simple steps.

a. Click Office Button and select Open.

b. Select Text Files (*.prn;*.txt;*.scv) as the filter and choose the text file. Click Open to continue.

c. The Text Import Wizard is ready and in just 3 steps you could finish it.
Determine fixed width of text data:

d. Set filed width (column breaks):

e. Select each column and set the data format:

But how to record macros in Microsoft Excel 2007? Please review this recipe: Record macros in Excel. Be sure to change some parameters in macro code to fit different usages.

3. Opening other Types of Files

It is possible to read XML or Access database files using Excel’s objects. (Note: you have to install Microsoft Excel 2003 or higher to deal with XML files). The OpenXML syntax is here:

Workbooks.OpenXML(Filename, Stylesheets, LoadOption)

FileName (String type) is the necessary argument.
Stylesheets (Variant type) specifies the XLS to convert XSLT.
LoadOption (Variant type) determines the mode to read XML. LoadOption could be one of the constants of XlXmlLoadOption.
XlXmlLoadOption could be one of the following contstant: xlXmlLoadImportToList, xlXmlLoadMapXml, xlXmlLoadOpenXml or xlXmlLoadPromptUser.

Here’s an example of VBA code to open “MyXML.xml” and display the contents in XML list.

Sub UseOpenXML()
Application.Workbooks.OpenXML _
Filename:=”MyXML.xml”, _
LoadOption:=xlXmlLoadImportToList
End Sub

The OpenDatabase syntax is:

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

FileName (String type) is the necessary argument to stand for connection string.
CommandText (Variant type) is the command text for query and it’s optional.
CommandType (Variant type) is the query type. It’s also optional. CommandType may be Default, SQL or Table.
BackgroundQuery (Variant type) is the query background.
ImportDataAs (Variant type) defines the query format.

An example to open MyAccess.mdb database file in Excel.

Sub OpenDatabase()
Workbooks.OpenDatabase FileName:=”C:\northwind.mdb”
End Sub

4. Saving Files

To save a file, you could use the Save or SaveAS method in Workbook object.

The syntax of Save method is simple:

OneWorkbook.Save ‘OneWorkbook is a workbook object.

Example:

ActiveWorkbook.Save (Save the active Workbook)

To savesas a workbook ,you should use SaveAs method and specify the new file name.

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

An example to create a workbook and prompt user to enter name and save it.

Set NewBook = Workbooks.Add
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName

Here, Application.GetSaveAsFilename could call the standard “Save As” dialog box and get the file name; Application.GetOpenFileName could call the standard “Open” dialog box.

5. Closing Files
To close a file, you can use the Close method of the Worbooks collection or Workbook object. Close method of the Worbooks collection can close all the active workbooks, Close method of the Workbook object can aloes specific workbook.

An example of the Colse method of Workbook object:

OneWorkboook.Close(SaveChanges, Filename, RouteWorkbook)

Here, the argument of SaveChanges is to prompt user to save it or not, usually it’s set as False to avoid the dialog box.

An example to close Book1.xls and discard all the changes:

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

Another example to close all active workboos and prompt user to save changes or not:

Workbooks.Close

6. Summary
Using Workbooks and Worksheets Objects to access data is the most simple and convenient way for beginners of Excel VBA. This method is also the top choice to only read data from Excel spreadsheets. So, the next post is on how to do file operations using VBA (Visual Basic for Applications)’s build-in functions.