Importing more than columns of data

It's not uncommon to need to import a text file that exceeds Excel's 256-column capacity. If you attempt to open such a file with the File ^ Open command, Excel simply ignores any data past column 256 (and doesn't even warn you about it).

Listing 27-5 is a variation of the ImportRange procedure presented earlier in this chapter. It reads a text file and then imports the data into a new workbook. If the data contains more than 256 columns of data, additional sheets are added to the workbook.

Listing 27-5: Reading a Text File and Importing the Data into a New Workbook

Sub ImportLongLines()

' Imports a text file with >256 columns of data

Dim ImpRange As Range Dim r As Long, c As Integer Dim CurrLine As Long

Dim Data As String, Char As String, Txt As String

Dim i As Integer

Dim CurrSheet As Worksheet

Create a new workbook with one sheet Workbooks.Add xlWorksheet

Open ThisWorkbook.Path & "\longfile.txt" For Input As #1

Set ImpRange = ActiveWorkbook.Sheets(1).Range("A1") Application.ScreenUpdating = False

Read the first line, and insert new sheets if necessary CurrLine = CurrLine + 1 Line Input #1, Data For i = 1 To Len(Data)

Char = Mid(Data, i, 1) Are we out of columns? If c <> 0 And c Mod 256 = 0 Then With ActiveWorkbook.Sheets

Set CurrSheet = .Add(after:=.Sheets(.Count)) End With

Set ImpRange = CurrSheet.Range("A1") c = 0 End If

ImpRange.Offset(r, c) = Txt c = c + 1 Txt = ""

Else

Skip quote characters If Char <> Chr(34) Then _

ImpRange.Offset(r, c) = Txt

Listing 27-5 (Continued)

Read the remaining data c = 0

CurrLine = 1

Set ImpRange = ActiveWorkbook.Sheets(1).Range("A1") r = r + 1

Do Until EOF(1)

Set ImpRange = ActiveWorkbook.Sheets(1).Range("A1") CurrLine = CurrLine + 1 Line Input #1, Data

Application.StatusBar = "Processing line " & CurrLine For i = 1 To Len(Data)

Char = Mid(Data, i, 1) Are we out of columns? If c <> 0 And c Mod 256 = 0 Then c = 0

Set ImpRange = ImpRange.Parent.Next.Range("A1") End If

ImpRange.Offset(r, c) = Txt c = c + 1 Txt = ""

Else

Skip quote characters If Char <> Chr(34) Then _

Set ImpRange = ActiveWorkbook.Sheets(1).Range("A1") r = r + 1

Loop

Application.ScreenUpdating = True Application.StatusBar = False End Sub

This procedure consists of two parts. The first part reads the first row of data and adds new sheets, if necessary. The second part reads the remaining data in the text file. The code assumes that the first row is typical of the remaining data and that it has the maximum number of columns.

This example is available on the companion CD-ROM,along with a text file that contains 100 rows, each with 600 columns of data.

0 0

Post a comment