Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |