excel - Macro that runs a Macro that opens files and save them as value - Runtime Error 1004 -

i keep getting 1004 runtime error. have slimmed programing down it’s not programception. think may have using excel 2010 save .xls files. not sure.

1. when auto_root.xls opens runs sub auto_open() opens panel.xls
2. panel opens , runs sub update() sequentially opens 7 files in different directories called auto_update.xls
3. auto_update.xsl opens , runs sub flat each open number of files sequentially , saves flat copy of in directory.

i have opened each of 7 auto_update.xls files , have run them independently , run no errors. when run them auto_root runtime error 1004. , currentwb.save highlighted on 1 of files. replaced currentwb.save currentwb.saveas filename:=targetfile, fileformat:=xlnormal , recieved same runtime error.

attached code have.

autoroot.xls!auto update

sub auto_open() application.cutcopymode = false dim panelfilepath string dim panelfilename string dim panellocation string dim panelwb workbook     panelfilepath = "d:\umc\umc production files\automation files\"     panelfilename = "panel.xls"     panellocation = panelfilepath & dir$(panelfilepath & panelfilename)         set panelwb = workbooks.open(filename:=panellocation, updatelinks:=3)             panelwb.runautomacros which:=xlautoopen             application.run "panel.xls!update"             panelwb.close     call shell("d:\umc\umc production files\automation files\auto.bat", vbnormalfocus) application.quit end sub 

panel.xls!update

 sub update() dim rownumber long dim autoupdatetargetfile string dim autoupdatewb workbook rownumber = 1 (range("autoupdate.file").rows.count - 1)     if (range("autoupdate.file").rows(rownumber) <> "")         autoupdatetargetfile = range("sys.path") & range("client.path").rows(rownumber) & range("autoupdate.path ").rows(rownumber) & range("autoupdate.file").rows(rownumber)         set autoupdatewb = workbooks.open(filename:=autoupdatetargetfile, updatelinks:=3)             autoupdatewb.runautomacros which:=xlautoopen             application.run "auto_update.xls!flat"             autoupdatewb.close     end if     next rownumber end sub 

autoupdate.xls!flat

sub flat() dim rownumber long 'long stores variable dim sheetnumber long dim targetfile string 'string stores file path dim backupfile string dim currentwb workbook 'workbook stores workbook rownumber = 1 (range("file").rows.count - 1) 'loops through each file in list , assigns workbook variable.     if (range("file").rows(rownumber) <> "")         targetfile = range("sys.path") & range("path").rows(rownumber) & range("file").rows(rownumber) 'target file path         backupfile = range("report.path") & range("path").rows(rownumber) & range("subfolder") & range("file").rows(rownumber) 'backup file path set currentwb = workbooks.open(filename:=targetfile, updatelinks:=3) 'sets currentwb = long name. becomes name of workbook.     currentwb.runautomacros which:=xlautoopen 'enables macros in workbook     currentwb.saveas filename:=targetfile, fileformat:=56         sheetnumber = 1 sheets.count 'counts worksheets in workbook             sheets(sheetnumber).select 'selects worksheets in workbook             if (sheets(sheetnumber).name <> "what if")                 sheets(sheetnumber).unprotect ("umc626") 'unprotects workbook                 cells.select 'selects data in workbook                 range("b2").activate                 sheets(sheetnumber).usedrange                     .value = .value                 end                 sheets(sheetnumber).protect password:="umc626", drawingobjects:=true, contents:=true, scenarios:=true 'protects workbook             end if         next sheetnumber 'runs through iteration         sheets(1).select         range("a1").select 'saves each workbook @ top of page         currentwb.saveas filename:=backupfile, fileformat:=56, password:="", writerespassword:="", _         readonlyrecommended:=false, createbackup:=false 'saves workbook in flatten file location     currentwb.close 'closes workbook     end if 'ends loop next rownumber 'selects account end sub 

what have done far.

1. each individual autoupdate file works when ran on on.
2. if application.run"auto_update.xls!flat" removed panel.xls!update opens , closes of autoupdate.xls files no error.
3. if link panel.xls!update 3 of 7 autoupdate files.... 3. runs no errors.

i can't seem run 7 without saying runtime error 1004.

i found microsoft work around code. not sure how implement though.

sub copysheettest()     dim itemp integer     dim obook workbook     dim icounter integer      ' create new blank workbook:     itemp = application.sheetsinnewworkbook     application.sheetsinnewworkbook = 1     set obook = application.workbooks.add     application.sheetsinnewworkbook = itemp      ' add defined name workbook     ' refersto range:     obook.names.add name:="temprange", _         refersto:="=sheet1!$a$1"      ' save workbook:     obook.saveas "c:\test2.xls"      ' copy sheet in loop. eventually,     ' error 1004: copy method of     ' worksheet class failed.     icounter = 1 275         obook.worksheets(1).copy after:=obook.worksheets(1)         'uncomment code workaround:         'save, close, , reopen after every 100 iterations:         if icounter mod 100 = 0             obook.close savechanges:=true             set obook = nothing             set obook = application.workbooks.open("c:\test2.xls")         end if     next end sub 

http://support.microsoft.com/kb/210684/en-us

based on document microsoft linked below known issue.

copying worksheet programmatically causes run-time error 1004 in excel

i'm not sure how many sheets loop in flat appears issue. quote:

this problem can occur when give workbook defined name , copy worksheet several times without first saving , closing workbook

due levels have created using separate workbooks suggest starting limiting scope of update subroutine. there many designs might start passing integer argument , fourth between auto open , update. way can close , reopen panel.xls multiple times , start left off.