运行打开文件并将其保存为值的宏的宏 - 运行时错误1004
我不断收到此1004运行时错误。我已经减少了一些编程,所以它不像Programception。我认为这可能与使用Excel 2010保存.xls文件有关。不确定。运行打开文件并将其保存为值的宏的宏 - 运行时错误1004
- 当Auto_Root.xls打开它运行子auto_open(),它会打开 Panel.xls
- 面板打开并运行子更新()的顺序在不同的目录中打开7个文件 全称为Auto_Update.xls
- Auto_Update.xsl打开并运行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
我迄今所做的。
- 每个单独的自动更新文件都在其运行时运行。
- 如果将Application.Run“Auto_Update.xls!Flat”从Panel.xls中删除!更新它会打开并关闭所有AutoUpdate.xls文件,而不会出错。
- 如果我将Panel.xls!Update更新为7个自动更新文件中的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
是一个已知的问题。
Copying worksheet programmatically causes run-time error 1004 in Excel
我不知道有多少张这个循环中,扁平,但似乎这是问题。具体报价:当你给工作簿定义名称关闭工作簿
由于您已创建水平
可能会出现此问题,然后多次复制工作表而不先保存和使用单独的工作簿,我会建议从限制你的更新子程序的范围开始。对于类似的东西有很多设计,但我可能会开始在Auto Open和Update之间传递一个整数参数。这样,您可以多次关闭并重新打开Panel.xls,并从您离开的地方开始。
我该怎么做? – 2014-12-15 19:27:57
使用语法“Update(loopCount As Integer)”将参数添加到Update中,然后在您的Update函数中使用三次变量(因为这似乎是限制),然后将其传递回自动打开。您需要在Auto Open中添加一个循环,然后检查loopCount并确保所有文件都已更新。这样,每个文件都会打开并关闭相当多的次数以避免运行时错误。有关传递参数的更多信息可以在这里找到:http://msdn.microsoft.com/en-us/library/aa263527%28v=vs.60%29.aspx – 2014-12-15 21:57:46
如果我运行它一次而不是三次才将其传回? – 2014-12-16 17:41:04
它不清楚从你的文字,而是你的程序“扁平”您正在打开里面的文件,如果是在它被自动打开宏叫什么名字? 听起来像你只想从原始工作簿中运行宏,而不是在打开的工作簿的自动打开宏中触发宏。 如果确实如此,我在其中一个工作簿中执行类似操作,在工作簿打开时会出现一个“升级”向导,但由于我正在升级,所以打开的另一个工作簿也有升级向导,以及用于启动的。我通过在一个隐藏的excel实例中打开另一个工作簿来解决这个问题,并且在我的自动打开宏中,我有一行代码查询工作簿的可见状态,并且在隐藏时不会触发。因此,在下面的代码其“和Me.Application.visible”如果运行向导
'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
控制这就要求你在一个单独的Excel实例中打开工作簿。下面的代码是代码,做这个片段,希望这是enopugh为您提供了从微软低于此链接的文档的想法
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
哪一行产生错误? – 2014-12-05 19:31:36
您是否已正确定义'文件'和'路径'的NAMED范围,并将一个单元命名为'Sys.Path'和'Report.Path'和'SubFolder'?另外,你有什么值'Sys.Path'(和其他信息?) – 2014-12-05 19:54:15
该工作簿是否具有只读属性集? – Sorceri 2014-12-05 20:55:41