EXCEL row limit

  aph 07:53 07 Jul 03
Locked

Hi,
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

Louise

  VoG II 08:02 07 Jul 03

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

  VoG II 23:33 08 Jul 03

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 03

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 03

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)

Wend

Wend


EndMacro:
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 03

This line

EndMacro: On Error GoTo 0

should be on 2 lines:

EndMacro:

On Error GoTo 0

  aph 10:49 13 Jul 03

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
L

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now