Method procedures for the CSVFile Class

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 in Listing 29-5 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.

Listing 29-5: Exporting a Worksheet Range with a Class Module Method

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 in Listing 29-6 imports a CSV file specified by the CSV FileName 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.

Listing 29-6: Importing Text File Contents into a Range with a Class Module Method

Sub Import(CSVFileName) ' Imports a CSV file to a range If ImportToCell Is Nothing Then

MsgBox "ImportRange not specified" Exit Sub End If

Listing 29-6 (Continued)

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

0 0

Post a comment