i curious if provide suggestions on how can make excel macro more stable.
the macro prompts user path folder containing files scan. macro iterates every file in folder.
it opens excel file, scans column d word fail, copies row of data data sheet in excel file macro programmed.
for part macro runs run time errors or 'excel has stopped working' errors. can scan through 5000+ files @ time , macro takes while run.
any suggestions appreciated. thanks!
sub findfail() dim pathinput string 'path file dim path string 'path file after being validated dim filenames string 'path test file dim book workbook 'file being tested dim sheet worksheet 'sheet writting data dim sh worksheet 'worksheet being tested dim databook workbook 'where data recorded dim row long 'row start writting data in dim numtests long 'number of files tested dim j long 'counter number of files tested dim long 'row being tested dim lastrow long 'last row used dim starttime double 'time when program started dim minselapsed double 'time took program end application.screenupdating = false j = 0 = 1 row = 2 set databook = activeworkbook set sheet = worksheets("data") sheet.range("a2:i1000").clear starttime = timer '-----prompt path----- pathinput = inputbox(prompt:="enter path files. must have \ after folder name.", _ title:="single report", _ default:="c:\folder\") if pathinput = "c:\folder\" or pathinput = vbnullstring 'check make sure path inputed msgbox ("please enter valid file path , try again.") exit sub else path = pathinput 'path = "c:\temp\212458481\" ' path file location filenames = dir(path & "*.xls") 'for xl2007 & "*.xls?" on windows '-----begin testing----- while filenames <> "" 'loop until filename blank set book = workbooks.open(path & filenames) set sh = book.worksheets(1) lastrow = sh.usedrange.rows(sh.usedrange.rows.count).row if sh.cells(lastrow, 2).value - sh.cells(1, 2).value >= 0.08333333 while sh.range("d" & i).value <> "" 'loop untile there no rows left test if sh.range("d" & i).value = "fail" 'record values if test result false sheet.range("a" & row).value = book.name sheet.range("b" & row).value = format(sh.range("b" & i).value - sh.range("b1").value, "h:mm:ss") sheet.range("c" & row).value = sh.range("a" & i).value sheet.range("d" & row).value = format(sh.range("b" & i).value, "h:mm:ss") sheet.range("e" & row).value = sh.range("c" & i).value sheet.range("f" & row).value = sh.range("d" & i).value sheet.range("g" & row).value = sh.range("e" & i).value sheet.range("h" & row).value = sh.range("f" & i).value sheet.range("i" & row).value = sh.range("g" & i).value row = row + 1 exit end if = + 1 loop j = j + 1 databook.sheets("summary").cells(2, 1).value = j end if book.close savechanges:=false filenames = dir() = 1 loop numtests = j worksheets("summary").cells(2, "a").value = numtests minselapsed = timer - starttime worksheets("summary").cells(2, "b").value = format(minselapsed / 86400, "hh:mm:ss") end if end sub
without same dataset we, can not definitively supply answer can recommend below related error seeing.
try freeing/destroying references book , sh.
you have loop sets them:-
do while filenames <> "" 'loop until filename blank set book = workbooks.open(path & filenames) set sh = book.worksheets(1) however end of loop not clear them, ideally should below:-
set sh = nothing set book = nothing loop this better way handle resources , should improve memory usage.
as poor example, without code saying, sh equals this, equals this instead, equals this instead, equals this instead, etc...
you end previous reference subsequently overwritten being sort of orphaned object holding space in memory.
Comments
Post a Comment