PDA

View Full Version : optimize macro



inajica
11-26-2012, 05:42 PM
I have a long macro that I would like it to be optimized. I run this macro every day. The length of the macro is about four hours. But after fifteen days it is still running after ten hours--75 percent completed. I have to format and reinstall windows 7 home to be able to complete the macro in four hours again. This is my system specs :

Intel Core i5-2430M 2.40hGhz
memory 6gb
Hard drive solid state drive ocz vertex 4

Paul_Hossler
11-26-2012, 06:01 PM
Well, if you're opening and closing 30,000 workbooks, I'd expect it to take awhile


Sub Ref1()
Dim csvdir As String
Dim xlsxdir As String
Dim windir As String
Application.ScreenUpdating = False
xlsxdir = "C:\Users\Eric\Desktop\single\cash1"
windir = "C:\Users\Eric\Desktop\single\dates\Apr1"
ChDir windir
ChDir xlsxdir

For i = 1 To 30000
Workbooks.Open Filename:=xlsxdir & "\" & i & ".xlsx"
Call afind1
ChDir windir
Call striplettersandnumbers_loopworkbooks
Call convert1
ActiveWorkbook.SaveAs Filename:=windir & "\Apr1_" & i + 0 & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveWorkbook.Close
Next i
Application.ScreenUpdating = True
End Sub


If it's taking that long, you should probably consider changing your approach and algorithms.

Paul

zyad
12-11-2012, 04:45 AM
That is really great, You just solved my problem, :kiss