Flexible Separators and Delimiters

Now that you have seen the basic techniques in action, you will set up some code that allows you to choose the separator and delimiter characters to suit the situation. The following code defines them using constants, which can be varied. The code inserts the delimiters and separators as required:

Sub WriteStringsWithDelimiters() Dim sLine As String

Dim sFName As String 'Path and name of text file

Dim iFNumber As Integer 'File number

Dim lRow As Long 'Row number in worksheet

Const

sVS

As

String =

'Variable separator character

Const

sTD

As

String =

„««il

'Text delimiter character

Const

sDD

As

String =

'Date delimiter character

sFName = "C:\VBA_Prog_Ref\Chapter12\JanSalesStringsDelimited.txt"

'Get an unused file number iFNumber = FreeFile

'Create new file or overwrite existing file Open sFName For Output As #iFNumber lRow = 2

'Read data from worksheet With Sheetl

sLine =

= sDD &

Format(.Cells(lRow, 1),

"yyyy-mmm-dd")

& sDD & sVS

sLine =

= sLine

& sTD & .Cells(lRow, 2)

& sTD & sVS

sLine =

= sLine

& sTD & .Cells(lRow, 4)

& sTD & sVS

sLine =

= sLine

& Format(.Cells(lRow, 6;

1, "0.00")

End With

'Write data to file Print #iFNumber, sLine

'Address next row of worksheet lRow = lRow + 1

'Loop until an empty cell is found Loop Until IsEmpty(Sheet1.Cells(lRow, 1))

'Close the file Close #iFNumber

End Sub

The file produced by the code is shown in Figure 11-5.

. JanSalesStiliigsEJelimited.txt Notepad iia

File Edit Fortnat View Help

*200ö-Jan-01# "f200S-:ian-02*-#2006-Jati-06iT

#2006-11371-21* #2006-:an-22# #2005-11 a/i-23i* #2006-:ian-27# £2006-3 371-27* «OüÉ-jar-íSí #2006-Jan-2S* ■i2006-Jan-29T *2006-:ian-30*

"Roberts";"oranges"; 15. QQ "Roberts"; "oranges"; 15. 00 "smith";"Mangoes"; 20. 00 "Roberts"; "oranges"; 15. 00 "Roberts";"Apples"; 12. 50 "Pradesh"; "Pears";is. 00 "Roberts":"Apples";12.50 "smith";"Apples";12.50_ "smi th";"oranges";15.00 "Pradesh"; "oranges"; 15. 00 "Roberts "; 'Vangoes"; 20. 00 "Kee";"Pears";IB. 00 "Roberts":"Mangoes"; 20. 00 "smith"; "Mangoes"; 20. 00 "Kee":"Manooes";20. 00 "Kee";"Mangoes";20.00 "Roberts"; oranges"; 15. 00 "Kee"; "A.ppTes";12. 50 "Kee":"Mangoes":20.00 "Kee"; "oranges"; 15. 00

Figure 11-5

The following code is used to read the file. It uses the delimiter characters to decide the data type of each item and treat it appropriately:

Sub ReadStringsWithDelimiters()

Dim sLine As String

Dim sFName As String 'Path and name of text file Dim iFNumber As Integer 'File number Dim lRow As Long 'Row number in worksheet

Dim lColumn As Long 'Column number in worksheet

Dim vValues As Variant

Dim vValue As Variant

Dim iCount As Integer

Const sVS As String = ";"

'Variable separator character

Const sTD As String = """"

'Text delimiter character

Const sDD As String = "#"

'Date delimiter character

sFName = "C:\VBA_Prog_Ref\Chapter12\JanSalesStringsDelimited.txt" 'Get an unused file number iFNumber = FreeFile

'Prepare file for reading

Open sFName For Input As #iFNumber

Sheet2.Cells.Clear

'First row for data lRow = 2

'Read data from file Line Input #iFNumber, sLine

'Split values apart into array vValues = Split(sLine, sVS)

'First column for data lColumn = 1

'Process each value in array For Each vValue In vValues

'Determine value type using first character Select Case Left(vValue, 1)

'String Case sTD

Sheet2.Cells(lRow, lColumn) = Mid(vValue, 2, Len(vValue) - 2)

'Date Case sDD

Sheet2.Cells(lRow, lColumn) = _ DateValue(Mid(vValue, 2, Len(vValue) - 2))

'Other Case Else

Sheet2.Cells(lRow, lColumn) = vValue End Select lColumn = lColumn + 1 Next vValue

'Address next row of worksheet lRow = lRow + 1

'Loop until end of file Loop Until EOF(iFNumber)

'Close the file Close #iFNumber

End Sub

The For...Next loop of ReadStrings has been replaced by a For Each...Next loop, to show a slightly different approach. The Select Case structure determines the data type of each item by examining the first character. For strings and dates, it strips off the delimiters. For dates, it converts the character string to a VBA date type.

0 0

Post a comment