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

Question

I keep getting this 1004 runtime error. I have slimmed my programing down some so it’s not so Programception. I think it may have to do with using Excel 2010 to save .xls files. Not sure.

When Auto_Root.xls opens it runs Sub auto_open() which opens Panel.xls
Panel opens and runs Sub Update() which sequentially opens 7 files in different directories all called Auto_Update.xls
Auto_Update.xsl opens and runs Sub Flat which each open a number of files sequentially and saves a flat copy of themselves in another directory.

I have opened each of the 7 Auto_Update.xls files and have run them independently and they run with no errors. When I run them all from Auto_Root I get a runtime error 1004. And CurrentWB.Save is highlighted on one of the files. I even replaced CurrentWB.Save as CurrentWB.SaveAs Filename:=TargetFile, FileFormat:=xlNormal and recieved the same runtime error.

Attached is the code I have.

AutoRoot.xls!Auto 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 when ran on its on.
If Application.Run"Auto_Update.xls!Flat" is removed from Panel.xls!Update it opens and closes all of the AutoUpdate.xls files with no error.
If I link Panel.xls!Update to only 3 of the 7 AutoUpdate files.... any 3. It runs with no errors.

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

I found a microsoft work around code. Not sure how to implement it though.

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

Have you properly defined the NAMED ranges for 'File' and 'Path' and named ONE cell as 'Sys.Path' and 'Report.Path' and 'SubFolder'? Also, what values do you have in 'Sys.Path' (and other info??) — Wayne G. Dunn, Dec 05 '14 at 19:54
When you open the file, that makes it the 'ActiveWorkbook'... do you have any code or changes that would cause an issue? I see you may have macros. — Wayne G. Dunn, Dec 05 '14 at 20:58
If the file you are opening (and then saving) is 'Read Only', then you need to reset the flag. You can use Windows Explorer to select the file (or multiple files), then right-click and select 'Properties'. You will either see 'Read Only' checked or gray. Toggle so it is cleared, then save the changes. Tou also should be able to reset the flag using VBA if you have permissions. — Wayne G. Dunn, Dec 05 '14 at 21:42
If you don't declare variables, you leave it up to the usage to try and set the type. Also, you should always use 'Option Explicit' and compile to make sure that everything is the way YOU want it.And declare your variables with a type that matches the object - don't use 'Variant' or 'Object' unless they are needed. Not only does it help you with your own code, but if someone else looks at it and there is no variable defined, they scratch their head and wonder if it's outside the proc, or defined improperly. — Wayne G. Dunn, Dec 05 '14 at 22:32
You don't *need* to, but I suggest it (in line with prior comments). Since this thread is getting long, you may want to update your question with the details you provided later (i.e. Error code, line #, read only, etc), then delete the non-essential comments. — Wayne G. Dunn, Dec 05 '14 at 23:01
As a test, I would change the line of code 'Workbooks.Open ...' so it does nothing except open the file. Do NOT run any macros; do NOT update links; etc. Disable the macro in that workbook. Does that prevent the error? — Wayne G. Dunn, Dec 10 '14 at 22:21
In the `Update` Sub you are trying to `ActiveWorkbook.Close` close the active workbook. Logically, to me, that's wrong? — , Dec 11 '14 at 08:21

score 7 · Accepted Answer · answered Dec 14 '14 at 03:54

7

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

Copying worksheet programmatically causes run-time error 1004 in Excel

I'm not sure how many sheets this loop in Flat but it appears that is the issue. Specifically the quote:

This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook

Due to the levels that you have created using separate workbooks I would suggest starting with limiting the scope of your Update subroutine. There are many designs for something like that but I might start with passing an integer argument back and fourth between Auto Open and Update. That way you can close and reopen Panel.xls multiple times and start exactly where you left off.

answered Dec 14 '14 at 03:54

Dave Excel

351
2
5

1

To add the argument into Update using the syntax "Update(loopCount As Integer)" then using the variable in your Update function three times (because that appears to be the limit) then pass it back to Auto Open. You will want to add a loop into Auto Open to then check the loopCount and make sure all files have been updated. That way, each file gets opened and closed a fair number of times to avoid the run-time error. More information on passing arguments can be found here: http://msdn.microsoft.com/en-us/library/aa263527%28v=vs.60%29.aspx – Dave Excel Dec 15 '14 at 21:57
Would it make any difference if I ran it once instead of three times before passing it back? – Michael Downey Dec 16 '14 at 17:41
1

Worth testing but you may run into the same problem in Auto Open that you hit in Update. Being able to count it within Auto Open should be a simpler design so I imagine testing will be easier. – Dave Excel Dec 17 '14 at 02:09

score 2 · Answer 2 · answered Dec 11 '14 at 13:46

Its not clear from your text, but is your procedure "Flat" inside the files you are opening and if so is it being called by the auto open macro? It sounds like you want to only be running your macro from your original workbook, and not firing the ones in the auto open macro of the workbooks you open. If this is indeed the case, I do something similar in one of my workbooks, where I have an "upgrade" wizard that fires when the work book is opened, however because I am upgrading, the other workbook I open, also has the upgrade wizard, and so that used to fire as well. I resolved this by opening the other workbook in a hidden instance of excel, and within my auto open macro, I have a line of code that queries the visible state of the workbook, and does not fire if it is hidden. So in the below code its the "And Me.Application.visible" that controls if the wizard is run

  '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 workbooks in a separate excel instance. The below code is the snippet of code that does this, hope this is enopugh for you to get the idea

      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

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

2 Answers2