Macro was fast, now slow

[AlexSlivi]

I have a macro that was taking about 15 seconds to run that is now taking over half an hour (without making any changes)...it's a shared network/server but I've tried it at various times during the day.

This is one of the parts it is now taking forever in:

k = 1
Seek #1, 1
  Do While Not EOF(1)
    Line Input #1, Data
    Worksheets("Template").Cells(k, 1) = Mid(Data, 1, 155)
    Worksheets("Template").Cells(k, 2) = Mid(Data, 156, 19)
    Worksheets("Template").Cells(k, 3) = Mid(Data, 175, 61)
    k = k + 1
  Loop

(It has to read in about 20,000 lines)

Do you have any idea why this would be taking forever now when it didn't used to?

Thanks,
Alex

 

[Desu Nota from Columbus]

Try adding these to the beginning and end of the macro:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Your code here...

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Andrew Poulsom]

Do you have an formulas referencing those cells? If so try setting Calculation to Manual. Also, do you have any event code in the workbook? If so you may need to disable events. What's the purpose of this line?

Seek #1, 1

 

[wigi]

Most important, read in the text file in an array, and output it to the sheet in one operation. Constantly writing to a sheet takes time.

In the array, chop up the lines in 3 parts. Use Data > Text to columns to split to the other columns.

 

[AlexSlivi]

Okay, thank you all. Desu's thing worked but I will try your ideas too to see if I can speed it up even more!