The code below does two things.

The first part generates a comma separated textfile with 50 rows and 600
columns
The second procedure reads the file and places the data in sheet1 to sheet3
The workbook has 4 sheets called "main", "sheet1", "sheet2", "sheet3"
each sheet will be populated with 255 columns, so as is, you could change
the builder to 765 (255x3) , add more sheets if you want more columns

the code reads a line in from the csv, drops it into column A of sheet
'main' then copies blocks off 255 rows to to each sheet in turn.

I think the code is relatively simple to follow.
when you copy the code to a standard module, run the procedure called
'RunTest' ..this will build the demo file & then populatet he workbook. Clear
the workbook. You can run the procedure called 'runMain' which allows you to
navigate in explorer to your file...


Option Explicit
Const textFile As String = "c:\temp\textdemo.txt"
Sub RunTest()
BuildTestFile
FetchData textFile
End Sub
Sub runMain()
Dim fn As String
fn = Application.GetOpenFilename()
If fn < "" Then
FetchData fn
End If
End Sub
Sub BuildTestFile()
' set 50 rows
Const length As Long = 50
Dim text As String
Dim col As Long
Dim rw As Long
Dim ff As Long
ff = FreeFile
Open textFile For Output As ff
For rw = 1 To length
text = ""
For col = 1 To 600
text = text & "," & rw & "_" & col
Next
text = Mid(text, 2)
Print #ff, text
Next
Close
End Sub

Sub FetchData(sFilename As String)

Dim text As String
Dim col As Long
Dim rw As Long
Dim ff As Long
Dim data As Variant
Dim sheetnumber As Long
Dim depth As Long
ff = FreeFile
Open sFilename For Input As ff

Do Until EOF(ff)
Line Input #ff, text
rw = rw + 1
data = Split(text, ",")
depth = UBound(data, 1)
Range("A:A").Clear
Worksheets("main").Range("A1").Resize(depth) =
WorksheetFunction.Transpose(data)
For sheetnumber = 1 To Int(depth / 255) + 1
Worksheets("sheet" & sheetnumber).Range("A1").Offset(0, rw -
1).Resize(255).Value = _
Range("A1").Offset(255 * (sheetnumber - 1)).Resize(255).Value
Next

Loop
Close
End Sub








"Verity" wrote:

Hi,

I have a CSV file that has approximately than 520 fields (yes, I know, it's
terrible) that I need to import into Excel. I have found a macro that will
let me import up to 510 fields but it cuts off the last 10. That macro can
be found he http://support.microsoft.com/default...b;en-us;272729

I have only very limited VB skills and I was wondering if one of you kind
people could have a look at the code and tell me where I can change it to let
it import over 3 worksheets instead of 2?

Thanks very much in advance.
Cheers,
Verity