Slow Text File Import

[Klips]

Hi all,

I'm having problems finding out why the performance of my text file
import routine takes a hit after the first import operation. I have a
series of text files with a set format that I'd like to import into
individual sheets in excel. I have written a procedure that takes care
of this. The first file that I import is extremely fast. As soon as I
import the next file, the speed drops to 1/10 of the first time. The
speed deteriorates as I import more files, but the drop is more
gradual.

Here is the import code:

Open textFile For Input As #1

'the text file is read line by line
Do While Not EOF(1)
Line Input #1, TextLine
If InStr(TextLine, "POINT") <> 0 Then
If CheckHeader(Right(TextLine, Len(TextLine) - 13), textFile)
= True Then
GoTo ExitHandler
Else
Worksheets("Template").Copy After:=Worksheets("Summary")
Worksheets(3).Name = txt_name.Value
Worksheets(CStr(txt_name.Value)).Visible = True
Sheets(CStr(txt_name.Value)).Activate
ActiveSheet.Move Before:=Worksheets("Template")

'the script deletes previous information and replaces it
with a new set
rowNum = ActiveSheet.Range("Data").Row
rowOrigin = rowNum
ActiveSheet.Range("A" & (rowOrigin + 1), "A" &
(ActiveSheet.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete
If Notes.Range("Tbl_Case_Hdr").Row -
Notes.Range("Tbl_Case_Ftr").Row = -2 Then
Summary.Range("A" & (rowOrigin + 1), "A" &
(Summary.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete
End If
End If
ElseIf InStr(TextLine, "element group") <> 0 Then
'whenever a new element is declared, the element and group ID
are read
TextPos = InStr(TextLine, "Element") + 8
TextLen = InStr(TextPos, TextLine, " ") - TextPos
elmID = CInt(Right(Left(TextLine, (TextPos + TextLen - 1)),
TextLen))
TextPos = InStr(TextLine, "group") + 5
TextLen = Len(TextLine) - TextPos
grpID = CInt(Right(TextLine, TextLen))
Else
If InStr(TextLine, "node 1") <> 0 Then
ActiveSheet.Cells(rowNum, 2) = grpID
ActiveSheet.Cells(rowNum, 3) = elmID
ActiveSheet.Cells(rowNum, 4) = grpID & "-" & elmID
values = SplitData(Right(TextLine, Len(TextLine) - 13))
ActiveSheet.Range("V" & rowNum, "AA" & rowNum) = values
If Notes.Range("Tbl_Case_Hdr").Row -
Notes.Range("Tbl_Case_Ftr").Row = -2 Then
Summary.Cells(rowNum, 2) = grpID
Summary.Cells(rowNum, 3) = elmID
Summary.Cells(rowNum, 4) = grpID & "-" & elmID
End If
ElseIf InStr(TextLine, "node 2") <> 0 Then
values = SplitData(Right(TextLine, Len(TextLine) - 13))
ActiveSheet.Range("AC" & rowNum, "AH" & rowNum).Value =
values
rowNum = rowNum + 1
End If
End If
'update the progress bar
prg_import.Value = Round((Loc(1) * 12800) / LOF(1), 0)
Loop

Close #1

Any help is appreciated. If someone wants to take a look at the
complete file with sample text files, just let me know.

Thanks

 

[Tim]

Are you turning off screenupdating and setting calculation to manual during
each import ?

Tim

 

[Klips]

Are you turning off screenupdating and setting calculation to manual during
each import ?

Tim














- Show quoted text -

When I open my file, I set enablecalculation to false for all the
sheets. The only sheet that this doesn't apply, I think, is the new
sheet that is created. I tried setting enablecalculation = false after
this sheet was created to see if there was an imporovement, but
nothing changed.

And yes, just above this i set screenupdating to false.

Should I do things differently?

 

[Jim Cone]

Have you considered opening the text file directly into Excel
and then formatting the file. Excel plops a text file into the
first column of a worksheet in tenths of a second...

Sub OpenTextFile()
Workbooks.Open "F:\Acme\Z2Compliance.txt"
End Sub

Also, have you declared all of your variables?
The code you posted doesn't show anything declared.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Hi all,
I'm having problems finding out why the performance of my text file
import routine takes a hit after the first import operation. I have a
series of text files with a set format that I'd like to import into
individual sheets in excel. I have written a procedure that takes care
of this. The first file that I import is extremely fast. As soon as I
import the next file, the speed drops to 1/10 of the first time. The
speed deteriorates as I import more files, but the drop is more
gradual.

Here is the import code:
-snip-Any help is appreciated. If someone wants to take a look at the
complete file with sample text files, just let me know.
Thanks

 

[Klips]

Have you considered opening the text file directly into Excel
and then formatting the file. Excel plops a text file into the
first column of a worksheet in tenths of a second...

Sub OpenTextFile()
Workbooks.Open "F:\Acme\Z2Compliance.txt"
End Sub

Also, have you declared all of your variables?
The code you posted doesn't show anything declared.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware


Hi all,
I'm having problems finding out why the performance of my text file
import routine takes a hit after the first import operation. I have a
series of text files with a set format that I'd like to import into
individual sheets in excel. I have written a procedure that takes care
of this. The first file that I import is extremely fast. As soon as I
import the next file, the speed drops to 1/10 of the first time. The
speed deteriorates as I import more files, but the drop is more
gradual.

Here is the import code:
-snip-Any help is appreciated. If someone wants to take a look at the
complete file with sample text files, just let me know.
Thanks

The problem is that I'm not sure if this performance issue has
anything to do with reading the data in. I feel like it is more a
problem of how content gets assigned to cells. I have put all the code
in a textfile at http://www.lapse.ca/code.txt in case you need to look
at it. I'm using Option Explicit, so variables seem to be all defined.
The code is executed by pushing a button on a userform.

Any help is appreciated.

 

[Klips]

The problem is that I'm not sure if this performance issue has
anything to do with reading the data in. I feel like it is more a
problem of how content gets assigned to cells. I have put all the code
in a textfile athttp://www.lapse.ca/code.txtin case you need to look
at it. I'm using Option Explicit, so variables seem to be all defined.
The code is executed by pushing a button on a userform.

Any help is appreciated.- Hide quoted text -

- Show quoted text -

Tim & Jim,

I read Tim's message again (and more carefully) and realized that my
method of using enablecalculation was in fact not preventing excel
from recalculating the entire workbook over and over again after each
cell input. As Tim had pointed out I needed to set the
Application.calculation parameter to manual in order for excel to stop
calculating the entire sheet until I was finished with my import
operation.

I'm sorry it took me a while to figure out. Thank you both for your
help.