It's been three years and I don't even remember writing this.
But I must have saved it for some reason. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


'The following code allows the importation of delimited text files,
'that exceed 256 columns, directly into a Excel spreadsheet.

'The code was written using the MSKB article # 120596
'"XL: Importing Text Files Larger Than 16384 Rows" as a base.
'It was modified by using a Byte array to check the number of
'delimiters in each file string and to split the string at the
'256the column if there are more than 255 delimiters.
'The second portion of the string is added to a second worksheet.
'Each row that is split is noted by bold font.
'If the string exceeds 512 chunks (columns), then the code will have to be modified.
'The Excel "Text to Columns"'utility can be used to parse all rows on both sheets.

'Code modified by Jim Cone on May 11, 2003.
Sub LargeFileImport_revised()
Dim ResultStr2 As String
Dim ResultStr As String
Dim GetUserData As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim strSeparator As Byte
Dim StringHolder() As Byte

'Ask user for the character that separates the data.
GetUserData = InputBox(vbCr & "Enter the separator character. " & vbCr & _
"One character only.", " Large Text File Import", _
" A space will work, ""tab"" will not")
If Len(GetUserData) = 0 Or Len(GetUserData) 1 Then
Exit Sub
Else
strSeparator = Asc(GetUserData)
End If

'Ask User for File's Name
GetUserData = Application.GetOpenFilename(Title:=" Large Text File Import")
'Check for no entry
If Len(GetUserData) = 0 Or GetUserData = False Then Exit Sub
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open GetUserData For Input As #FileNum

Application.ScreenUpdating = False
Worksheets.Add befo=Sheets(1), Count:=2
On Error Resume Next 'Duplicate sheet names are not allowed.
Worksheets(1).Name = "Columns 1 to 256"
Worksheets(2).Name = "Columns 257 and up"
On Error GoTo 0
Worksheets(1).Activate

Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & GetUserData
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Use a Byte array to hold the string
StringHolder() = ResultStr
For i = 0 To UBound(StringHolder) Step 2
If StringHolder(i) = strSeparator Then
N = N + 1
If N 255 Then
TooLong = True
Exit For
End If
End If
Next 'i

'If more than 256 chunks (columns)
If TooLong Then
i = i \ 2
ResultStr2 = Right$(ResultStr, Len(ResultStr) - InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare))
ResultStr = Left$(ResultStr, WorksheetFunction.Max(InStr(i, ResultStr, Chr$(strSeparator), vbTextCompare) - 1, 0))
'Add first portion of string to the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
Cells(Counter, 1).Font.Bold = True

'Add balance of string to the second worksheet.
If Left(ResultStr2, 1) = "=" Then
Worksheets(2).Cells(Counter, 1).Value = "'" & ResultStr2
Else
Worksheets(2).Cells(Counter, 1).Value = ResultStr2
End If
TooLong = False
Else
'Store entire string on the first worksheet.
If Left(ResultStr, 1) = "=" Then
Cells(Counter, 1).Value = "'" & ResultStr
Else
Cells(Counter, 1).Value = ResultStr
End If
End If

'Refresh variables
N = 0
Erase StringHolder()
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
Application.StatusBar = False
End Sub
'------------------------------------------


"bluelagoon_HP"

wrote in message
i have large data file i need to load into excel
15000 rows by 300 columns
i know there is limitation on 256 columns, however i would like to know how
to split the colums at will, say 150 in 1st sheet and 150 in 2nd sheet

i know there is a macro for that on ms support, i got it, but it's not user
friendly,
there are no inputs for rows to be split etc...

so the question is: is there a user friendly add on or a macro that i can
install that allows splitting one file by rows into different sheets, for
easy loading into excel ?
thanks!