运行宏的宏,打开文件并将其另存为值 - 运行时错误1004

我一直收到这个1004运行时错误.我已经缩减了我的编程,所以它不是那么Programception.我认为这可能与使用Excel 2010保存.xls文件有关.不确定.

当Auto_Root.xls打开时,它运行Sub auto_open(),打开Panel.xls

Panel打开并运行Sub Update(),它按顺序打开不同目录中的7个文件,所有目录都称为Auto_Update.xls

Auto_Update.xsl打开并运行Sub Flat,每个Sub Flat按顺序打开多个文件,并将自己的平面副本保存在另一个目录中.

我已经打开了7个Auto_Update.xls文件中的每一个并且已经独立运行它们并且它们运行时没有错误.当我从Auto_Root运行它们时,我得到一个运行时错误1004.并且其中一个文件突出显示CurrentWB.Save.我甚至将CurrentWB.Save替换为CurrentWB.SaveAs Filename:= TargetFile,FileFormat:= xlNormal并收到相同的运行时错误.

附上的是我的代码.

AutoRoot.xls!自动更新

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!更新

 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!平

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

到目前为止我做了什么.

每个单独的AutoUpdate文件在其上运行时都有效.

如果从Panel.xls中删除了Application.Run"Auto_Update.xls!Flat"!更新它会打开并关闭所有AutoUpdate.xls文件而不会出现任何错误.

如果我将Panel.xls!Update链接到7个AutoUpdate文件中的3个....任何3.它运行时没有错误.

我似乎无法让它运行所有7而不说运行时错误1004.

我找到了一个围绕代码的微软工作.不知道如何实现它.

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