Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The example presented in this section defines an object class called CSVFileClass . This class has two properties and two methods :

• Properties:

  • ExportRange : (Read/write) A worksheet range to be exported as a CSV file.

  • ImportRange : (Read/write) The range into which a CSV file will be imported.

• Methods:

  • Import : Imports the CSV file represented by the CSVFileName argument into the range represented by the ImportRange property.

  • Export : Exports the range represented by the ExportRange property to a CSV file represented by the CSVFileName argument.

Class module “level variables for the CSVFileClass

A class module must maintain its own private variables that mirror the property settings for the class. The CSVFileClass class module uses two variables to keep track of the two property settings. These variables are declared at the top of the class module:

Private RangeToExport As Range Private ImportToCell As Range

RangeToExport is a Range object that represents the range to be exported. ImportToCell is a Range object that represents the upper-left cell of the range into which the file will be imported. These variables are assigned values by the Property Get and Property Let procedures listed in the next section.

Property procedures for the CSVFileClass

The property procedures for the CSVFileClass class module follow. The Property Get procedures return the value of a variable, and the Property Let procedures set the value of a variable.

Property Get ExportRange() As Range Set ExportRange = RangeToExport End Property Property Let ExportRange(rng As Range) Set RangeToExport = rng End Property Property Get ImportRange() As Range Set ImportRange = ImportToCell End Property Property Let ImportRange(rng As Range) Set ImportToCell = rng End Property

Method procedures for the CSVFileClass

The CSVFileClass class module contains two procedures that represent the two methods. These are listed and discussed in the sections that follow.

THE EXPORT PROCEDURE

The Export procedure is called when the Export method is executed. It takes one argument: the full name of the file receiving the exported range. The procedure provides some basic error handling. For example, it ensures that the ExportRange property has been set by checking the RangeToExport variable. The procedure sets up an error handler to trap other errors.

Sub Export(CSVFileName) ' Exports a range to CSV file If RangeToExport Is Nothing Then MsgBox "ExportRange not specified" Exit Sub End If On Error GoTo ErrHandle Application.ScreenUpdating = False Set ExpBook = Workbooks.Add(xlWorksheet) RangeToExport.Copy Application.DisplayAlerts = False With ExpBook .Sheets(1).Paste .SaveAs FileName:=CSVFileName, FileFormat:=xlCSV .Close SaveChanges:=False End With Application.CutCopyMode = False Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub ErrHandle: ExpBook.Close SaveChanges:=False Application.CutCopyMode = False Application.ScreenUpdating = True Application.DisplayAlerts = True MsgBox "Error " & Err & vbCrLf & vbCrLf & Error(Err), _ vbCritical, "Export Method Error" End Sub

The Export procedure works by copying the range specified by the RangeToExport variable to a new temporary workbook, saving the workbook as a CSV text file, and closing the file. Because screen updating is turned off, the user does not see this happening. If an error occurs - for example, an invalid filename is specified - the procedure jumps to the ErrHandle section and displays a message box that contains the error number and description.

THE IMPORT PROCEDURE

The Import procedure imports a CSV file specified by the CSVFileName argument and copies its contents to a range specified by the ImportToCell variable, which maintains the ImportRange property. The file is then closed. Again, screen updating is turned off, so the user does not see the file being opened. Like the Export procedure, the Import procedure incorporates some basic error handling.

Sub Import(CSVFileName) ' Imports a CSV file to a range If ImportToCell Is Nothing Then MsgBox "ImportRange not specified" Exit Sub End If If CSVFileName = "" Then MsgBox "Import FileName not specified" Exit Sub End If On Error GoTo ErrHandle Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open CSVFileName Set CSVFile = ActiveWorkbook ActiveSheet.UsedRange.Copy Destination:=ImportToCell CSVFile.Close SaveChanges:=False Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub ErrHandle: CSVFile.Close SaveChanges:=False Application.ScreenUpdating = True Application.DisplayAlerts = True MsgBox "Error " & Err & vbCrLf & vbCrLf & Error(Err), _ vbCritical, "Import Method Error" End Sub

Using the CSVFileClass object

To create an instance of a CSVFileClass object in your code, start by declaring a variable as type CSVFileClass . Here's an example:

Dim CSVFile As New CSVFileClass

You might prefer to declare the object variable first and then create the object when needed. This requires a Dim statement and a Set statement:

Dim CSVFile As CSVFileClass ' other code may go here Set CSVFile = New CSVFileClass

The advantage of using both a Dim and a Set statement is that the object isn't actually created until the Set statement is executed. You might want to use this technique to save memory by not creating an object if it's not needed. For example, your code might contain logic that determines whether the object is actually created. In addition, using the Set command enables you to create multiple instances of an object.

After creating an instance of the object, you can write other instructions to access the properties and methods defined in the class module.

As you can see in Figure 29-2, the VBE Auto List Members feature works just like any other object. After you type the variable name and a dot, you see a list of properties and methods for the object.

Figure 29-2: The Auto List Members feature displays the available properties and methods.

The following procedure demonstrates how to save the current range selection to a CSV file named temp.csv , which is stored in the same directory as the current workbook:

Sub ExportARange() Dim CSVFile As New CSVFileClass With CSVFile .ExportRange = ActiveWindow.RangeSelection .Export CSVFileName:=ThisWorkbook.Path & "\temp.csv" End With End Sub

Using the With-End With structure isn't mandatory. For example, the procedure could be written as follows :

Sub ExportARange() Dim CSVFile As New CSVFileClass CSVFile.ExportRange = ActiveWindow.RangeSelection CSVFile.Export CSVFileName:=ThisWorkbook.Path & "\temp.csv" End Sub

The following procedure demonstrates how to import a CSV file, beginning at the active cell:

Sub ImportAFile() Dim CSVFile As New CSVFileClass With CSVFile On Error Resume Next .ImportRange = ActiveCell .Import CSVFileName:=ThisWorkbook.Path & "\temp.csv" End With If Err <> 0 Then _ MsgBox "Cannot import " & ThisWorkbook.Path & "\temp.csv" End Sub

Your code can work with more than one instance of an object. The following code, for example, creates an array of three CSVFileClass objects:

Sub Export3Files() Dim CSVFile(1 To 3) As New CSVFileClass CSVFile(1).ExportRange = Range("A1:A20") CSVFile(2).ExportRange = Range("B1:B20") CSVFile(3).ExportRange = Range("C1:C20") For i = 1 To 3 CSVFile(i).Export CSVFileName:="File" & i & ".csv" Next i End Sub