Reading Data Strings

To read this data, you need to split the strings to locate the values as shown in the following code. Code that is specific to ReadFile is highlighted:

Sub ReadStrings()

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 'Hold split values Dim iCount As Integer 'Counter sFName = "C:\VBA_Prog_Ref\Chapter12\JanSalesStrings.txt"

'Get an unused file number iFNumber = FreeFile

'Prepare file for reading

Open sFName For Input As #iFNumber


'First row for data lRow = 2

'Read data from file Line Input #iFNumber, sLine

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

With Sheet2

'First column for data lColumn = 1

'Process each value in array

For iCount = LBound(vValues) To UBound(vValues)

'Write value to worksheet .Cells(lRow, lColumn) = vValues(iCount)

'Increase column count lColumn = lColumn + 1

Next iCount

End With

'Address next row of worksheet lRow = lRow + 1

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

'Close the file Close #iFNumber

End Sub

Line Input reads an entire line of the file into sLine. The Split function breaks the text apart using the delimiter character, creating an array of values in vValues. The For...Next loop processes each value in the array, writing the value into the worksheet.

0 0

Post a comment