EXCEL row limit

  aph 07:53 07 Jul 2003

I am trying to get a series of values from a text file into EXCEL.
The text file is just a series of comma seperated numbers but it is very long (nearly 1000 items)

i was told to rename the text file to a CSV and then open it with EXCEL
When I do this EXCEL says
"not all values loaded"

and I believe this is because there are only 255 columns available in EXCEL.
Question is , can i get EXCEL to open my file as a series of records as opposed to one single record with lots of fields


  VoG II 08:02 07 Jul 2003

You need to insert some blank lines in your comma delimited file, say every 50 values.

  VoG II 23:33 08 Jul 2003

Did this work?

If not a macro might solve it. Please don't leave these threads unanswered. I've offered suggestions on a number of your posts and no response, positive or negative. It would help future searchers of the forum database to know what worked.

  aph 15:55 12 Jul 2003

Sorry VoG,
I had some problems with my internet connection and have only just managed to get back online.

I won't be able to insert blanks into the files as this will affect the ability to chart it.

I have tried to transpose the row to a column but because it doesn't get fully loaded the data is not there anyway!

I have just found out how to easily get to my threads and check them as resolved so I will do that asap

thanks for helping..

  VoG II 16:33 12 Jul 2003

Here is a macro that will place each value in a successive row in Column A on the current Excel sheet. Start Excel, ALT+F11 to open the Visual Basic Editor. Insert/Module. Then copy the following (down to and including End Sub) and paste into your module.

Sub ImportTextFile()

Dim RowNdx As Integer, ColNdx As Integer, TempVal As Variant

Dim WholeLine As String, FName As String, Sep As String

Dim Pos As Integer, NextPos As Integer

FName = "myfile.txt"

Sep = ","

ColNdx = 1

RowNdx = 1

Application.ScreenUpdating = False

On Error GoTo EndMacro:

Open FName For Input Access Read As #1

While Not EOF(1)

Line Input #1, WholeLine

If Right(WholeLine, 1) <> Sep Then

WholeLine = WholeLine & Sep

End If

Pos = 1

NextPos = InStr(Pos, WholeLine, Sep)

While NextPos >= 1

TempVal = Mid(WholeLine, Pos, NextPos - Pos)

Cells(RowNdx, ColNdx).Value = TempVal

Pos = NextPos + 1

RowNdx = RowNdx + 1

NextPos = InStr(Pos, WholeLine, Sep)



On Error GoTo 0

Application.ScreenUpdating = True

Close #1

End Sub


Change this line to the name of your CSV file

FName = "myfile.txt"

ALT+F11 to return to your worksheet. Save the file!

Tools/Macro/Macros, select ImportTextFile and click on Run.

Good luck. I've tested it but not to 1000 numbers!

  VoG II 16:35 12 Jul 2003

This line

EndMacro: On Error GoTo 0

should be on 2 lines:


On Error GoTo 0

  aph 10:49 13 Jul 2003

Thank You VoG
It works and I have now learnt something new. Will go book shopping Monday to learn more about this, for now feet up and relax

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?