Excel - open file > 65536 rows

[pglover]

Help please

I want to open a (text) file in Text File Wizard format so that I can specify column width and data format. Problem is I want this inside a macro that will feed the file over more than one sheet (the data is more than 65536 lines).

I have the macro that will read text files of >65536 lines, but it reads in as text and fails (memory problem due to the format of the file - works OK if read with the text file wizard).

Will happily post the macro if required.

Many thanks

Penny

 

[fredericofonseca]

You can do all the formating manually and overcome the memory problem.

similar to

Sub aa()
Dim aaa As String
aaa = "01/01/2002 - Long variable with loads of info"
Sheet1.Cells(1, 1).Value = Format(Mid(aaa, 1, 10), "mm/dd/yyyy&quot
Sheet1.Cells(2, 1).Value = Mid(aaa, 11, 100)
End Sub



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[pglover]

The memory problem is caused by the macro trying to read in > 65536 lines of text. I have a macro to do everything I need to once it has been opened by Excel.

Regards

Penny

 

[xlbo]

have a go with this:

sub LargeFileImport()

Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = InputBox("Please enter the Text File's name, e.g. S:\Revenue\Straus\BATCHOCCMUM121002.txt&quot
If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = &quot;Importing Row &quot; & _
Counter & &quot; of text file &quot; & FileName
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = &quot;=&quot; Then
ActiveCell.Value = &quot;'&quot; & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.row = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.StatusBar = False

End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[fredericofonseca]

pglover

Your problem is on how you are trying to read the file.

If you put your code here as requested we will be able to help you.

But the main thing is that you DO NOT load all records before processing them.

You load and process one at the time and you won't have memory problems.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[pglover]

That's the macro I have - it fails at

ActiveCell.Value = ResultStr

It also pulls the text into the cells without splitting across columns/rows

Regards

Penny

 

[fredericofonseca]

Penny,

1- If it is failing with a memory error at
ActiveCell.Value = ResultStr
then you may have a &quot;bad&quot; file. Try and display the lenght of &quot;ResultStr&quot; before the code. It is possible that the .txtfile is just been treated as a BIG one line record.

2- In order to split the cells accross the columns you use xlbl code in conjunction with mine.
E.G. For each line read from the file you call a subfunction to assign substrings to each cell on the active row.

E.G. on xlbo code
replace
If Left(ResultStr, 1) = &quot;=&quot; Then
ActiveCell.Value = &quot;'&quot; & ResultStr
Else
ActiveCell.Value = ResultStr
End If
with

call fill_cols(ActiveSheet.Index, ActiveCell.row, ResultStr)

And add the following sub which is where all formating and record split takes place

Sub fill_cols(sheetindex As Integer, cell As Integer, text As String)

Worksheets(sheetindex).Cells(cell, 1).Value = Format(Mid(text, 1, 10), &quot;mm/dd/yyyy&quot
Worksheets(sheetindex).Cells(cell, 2).Value = Mid(text, 11, 100)

End Sub



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com