Using the CSVFile Class 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, followed by a dot, you'll see a list of properties and methods for the object.

Microsoft Visual Basic - cs

vclass

Is

[M o d il Ici (Code)l

hsqi

Mg File Edit View [its

e,t

t De hug Rim Tools Add-I

s

Window Help

. ff X

1 j-d

-)

<>

» 1« a M, 1 $ Off tar Ä-1 @>

i5, Col 13

p

iW iW

1

%% & ç

Project - VBProject

1 (General) ^J [Ê

N|)oitARanj|e

J

- ^ VBProject (csvdass.xls)

I Sub'ExpoetARange( )

- 1 Microsoft Eccel Ob,ectc

HJ 5heetl (5heetl)

Dim CSVFile As Mem CSVF:

leClass

fi ThBW.rhto.l,

CSVFile.|

à-S Modulée

End s-tib sS Export

Modulel

ShD Class Modules

a» import ^

ImportRange

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

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

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")

CSVFile(i).Export CSVFileName:="File" & i & ".csv" Next i End Sub

Chapter 30

0 0

Post a comment