On May 7, 4:48 pm, "Tim" <tim j williams at gmail dot com wrote:
Are you turning off screenupdating and setting calculation to manual during
each import ?

Tim

"Klips" wrote in message

oups.com...



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 Befo=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- Hide quoted text -

- 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?