A macro that runs a macro that opens files and saves them as a value - Runtime Error 1004

Question

I keep getting this runtime error 1004. I shortened my program a bit so that it wasn’t such software. I think this could be due to using Excel 2010 to save .xls files. Not sure.

• When Auto_Root.xls opens, it launches Sub auto_open (), which opens Panel.xls
• The panel opens and launches Sub Update (), which sequentially opens 7 files in different directories, all called Auto_Update.xls
• Auto_Update.xsl opens and launches Sub Flat, each of which opens several files sequentially and saves a flat copy of itself to another directory.

I opened each of the 7 Auto_Update.xls files and ran them myself, and they started without errors. When I run them all from Auto_Root, I get a 1004 runtime error. And CurrentWB.Save stands out in one of the files. I even replaced CurrentWB.Save as CurrentWB.SaveAs Filename: = TargetFile, FileFormat: = xlNormal and got the same runtime error.

The attached code that I have.

AutoRoot.xls! Automatic update

Sub auto_open() Application.CutCopyMode = False Dim PanelFilePath As String Dim PanelFileName As String Dim PanelLocation As String Dim PanelWB As 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 As Long Dim AutoUpdateTargetFile As String Dim AutoUpdateWB As Workbook For RowNumber = 1 To (Range("AutoUpdate.File").Rows.Count - 1) If (Range("AutoUpdate.File").Rows(RowNumber) <> "") Then 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 As Long 'Long Stores Variable Dim SheetNumber As Long Dim TargetFile As String 'String Stores File Path Dim BackupFile As String Dim CurrentWB As Workbook 'Workbook Stores Workbook For RowNumber = 1 To (Range("File").Rows.Count - 1) 'Loops through each file in the list and assigns a workbook variable. If (Range("File").Rows(RowNumber) <> "") Then 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 = to that long name. This becomes the name of the workbook. CurrentWB.RunAutoMacros Which:=xlAutoOpen 'Enables Macros in Workbook CurrentWB.SaveAs Filename:=TargetFile, FileFormat:=56 For SheetNumber = 1 To Sheets.Count 'Counts Worksheets in Workbook Sheets(SheetNumber).Select 'Selects All Worksheets in Workbook If (Sheets(SheetNumber).Name <> "What If") Then Sheets(SheetNumber).Unprotect ("UMC626") 'Unprotects Workbook Cells.Select 'Selects Data in Workbook Range("B2").Activate With Sheets(SheetNumber).UsedRange .Value = .Value End With 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 at the top of the 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 Another Account End Sub 

What I have done so far.

• Each individual AutoUpdate file works at startup.
• If Application.Run "Auto_Update.xls! Flat" is removed from Panel.xls! The update opens and closes all AutoUpdate.xls files without errors.
• If I Link Panel.xls! I update only 3 of 7 AutoUpdate files .... any 3. It works without errors.

I just can't get it to run all 7 without saying Runtime Error 1004.

I found that microsoft is working on code. Not sure how to implement it.

 Sub CopySheetTest() Dim iTemp As Integer Dim oBook As Workbook Dim iCounter As Integer ' Create a new blank workbook: iTemp = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Set oBook = Application.Workbooks.Add Application.SheetsInNewWorkbook = iTemp ' Add a defined name to the workbook ' that RefersTo a range: oBook.Names.Add Name:="tempRange", _ RefersTo:="=Sheet1!$A$1" ' Save the workbook: oBook.SaveAs "c:\test2.xls" ' Copy the sheet in a loop. Eventually, ' you get error 1004: Copy Method of ' Worksheet class failed. For iCounter = 1 To 275 oBook.Worksheets(1).Copy After:=oBook.Worksheets(1) 'Uncomment this code for the workaround: 'Save, close, and reopen after every 100 iterations: If iCounter Mod 100 = 0 Then 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

Answer 1

Based on the document from Microsoft below, this is a known issue.

Copying worksheet programmatically causes error 1004 at runtime in Excel

I'm not sure how many sheets this loop is in Flat, but it seems like a problem. In particular, the quote:

This problem may occur if you give the book a specific name and then copy the worksheet several times without saving and closing the book.

Due to the levels that you created using individual books, I would suggest starting with limiting the amount of your update routine. There are many projects for something like this, but I could start by passing the integer argument back and the fourth between Auto Open and Update. Thus, you can close and reopen Panel.xls several times and start where you left off.

Answer 2

It is not clear from your text, but your procedure is "Flat" inside the files that you open, and if this is called automatic macro opening? It looks like you want you to run only your macro from your original book, and not shoot the macro opening the books that you open automatically. If this is true, I do something similar in one of my books, where I have the “update” wizard that starts when the workbook is opened, however, as I update, the other open book also has a wizard update, and therefore It also worked. I resolved this by opening another workbook in a hidden excel instance, and in my automatic open macro I have a line of code that asks for the visible state of the workbook and does not fire if it is hidden. Thus, in the code below it is "And Me.Application.visible", which controls if the wizard is running

  'Check if the ODS code is populated or default xxx, if so invoke the upgrade wizard 'but only if the application is visible If (ActiveWorkbook.Names("Trust_ODS_Code").RefersToRange.Value = "xxx" _ Or Len(ActiveWorkbook.Names("Trust_ODS_Code").RefersToRange.Value) = 0) _ And Me.Application.visible = True Then 'run the upgrade wizard frmCSCWizardv8.Show End If 

This requires that you open your books in a separate excel instance. The code below is a piece of code that does this, hope you can understand it.

  Dim lRet Dim i As Integer, j As Integer Dim FoundSheet As Boolean 'Because the wizard opens the old DCS in a hidden instance of Excel, it is vital that we close this if 'anything goes wrong, so belt and braces, close it every time the user presses the button 'Switch off the error handling and the display alerts to avoid any error messages if the old dcs has 'never been opened and the hidden instance does not exist Application.DisplayAlerts = False On Error Resume Next book.Close SaveChanges:=False app.Quit Set app = Nothing Application.DisplayAlerts = True 'set error handling On Error GoTo Err_Clr 'populate the status bar Application.StatusBar = "Attempting to open File" 'Default method Uses Excel Open Dialog To Show the Files lRet = Application.GetOpenFilename("Excel files (*.xls;*.xlsx;*.xlsm;*.xlsb), *.xls;*.xlsx;*.xlsm;*.xlsb") 'If the user selects cancel update the status to tell them If lRet = False Then Me.lstOpenDCSStatus.AddItem "No file selected" 'if the user has selected a file try to open it Else 'This next section of code creates a new instance of excel to open the selected file with, as this allows us to 'open it in the background OldDCS = lRet Application.StatusBar = "Attempting to open File - " & lRet app.visible = False 'Visible is False by default, so this isn't necessary, but makes readability better Set book = app.Workbooks.Add(lRet) Application.StatusBar = "Opened File - " & lRet