如何修复wb.SaveAs函数上的错误1004 [英] How to fix error 1004 on a wb.SaveAs function

问题描述

这是我的第一个大型VBA项目.我正在自动从多个来源提交多个报告.上周，我完成了我需要使用的两个报表的实现工作，并且wb.SaveAs函数正在运行.现在，我添加了第三个源，wb.SaveAs不再起作用.

This is my first big VBA project for my work. I am automating the filing of multiple reports from multiple sources. Last week I had finished working on implementing two of the reports I need to use and the wb.SaveAs function was working. Now that I have added a third source, the wb.SaveAs doesn't work anymore.

我试图引用映射的网络驱动器，我试图直接引用，并且试图将路径放入变量中.这些都不起作用.我搜索了该站点和其他站点，但是我发现的类似问题的答案不适用.

I have tried to reference the mapped network drive, I've tried direct reference and I've tried to put the path in a variable. None of these worked. I searched this site and others, but the answer I found to a similar problem didn't apply.

'This is only the code for the loading and saving part
Dim wbTime As Workbook
Dim wsTime As Worksheet
Dim wbRP As Workbook
Dim wsRPDic As Worksheet
Dim wbTdB As Workbook
Dim wsTdB As Worksheet
Dim wbMots As Workbook
Dim wsMots As Worksheet


'Setting all the workbooks and worksheets to be used
Set wbTime = Workbooks("Timesheets.xls")
Set wsTime = wbTime.Worksheets("TimeSheets")
Set wbRP = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Rapport Productivit? Prod 2019.xlsx")
Set wsRPDic = wbRP.Worksheets("Dictionary")
Set wbTdB = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Suivis, Rapport et TdB\Tableau de Bord.xlsx")
Set wsTdB = wbTdB.Worksheets("Tableau de Bord Complet - 2019")
Set wbMots = Workbooks.Open("\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Rapport de productivit?.xls")
Set wsMots = wbMots.Worksheets("Sheet2")


'Save and close section. This is at the very end of the code and both wb.SaveAs don't work now
wbTime.SaveAs Filename:="\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Heures_" & Replace(curWeek, "/", "-") & ".xls", FileFormat:="xlExcel8"
wbMots.SaveAs Filename:="\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Rapport de Productivit?_" & Replace(curWeek, "/", "-") & ".xls", FileFormat:="xlExcel8"
wbRP.Save
wbTdB.Save
wbTime.Close
wbMots.Close
wbRP.Close
wbTdB.Close

我试图将两个报告保存在一个完整的文件夹中，并用报告的星期将其重命名.相反，我得到了错误代码:1004-由应用程序或对象定义的错误

I am trying to save in a completed folder two reports and rename them with the week of the report. Instead I get the error code : 1004 - Error defined by the application or by the object

推荐答案

失败的 Workbook.SaveAs 调用通常表示路径或文件名存在问题.

A failing Workbook.SaveAs call usually means there's a problem with the path or file name.

将 SaveAs 成员调用与进行该成员调用所需的片段分离.

Separate the SaveAs member call from the gathering of the pieces you need to make that member call.

即，将 Filename 自变量表达式拉入新的局部变量:

Namely, pull the Filename argument expression into a new local variable:

Dim newFilename As String
newFilename = "\\BUR-SERV\Data\xxx\xxx\xxx xxx\Calculs-Analyses\Raw Reports\Done\Rapport de Productivit?_" & Replace(curWeek, "/", "-") & ".xls"
Debug.Print newFilename
Stop

现在运行此命令，当执行停止时，您应该会看到为 Filename 参数传递的实际字符串值(Ctrl + G弹出调试/即时窗格).看起来像是有效路径吗?文件名?将其复制到剪贴板，返回Excel并按F12/SaveAs，然后尝试保存具有该名称的文件.

Now run this, and when execution stops you should be seeing the actual string value you were passing for a Filename argument (Ctrl+G to bring up the debug/immediate pane). Does that look like a valid path & filename? Copy it to the clipboard, go back to Excel and hit F12/SaveAs and try to save a file with that name.

您应该会收到一条详细的错误消息，告诉您它到底有什么问题.

You should be getting a detailed error message telling you exactly what's wrong with it.

?字符非常可疑(在文件名中是非法的)，并且 \ xxx \ xxx \ xxx 占位符看起来像实际路径可能很深-验证总长度不超过255个字符.

The ? character is highly suspicious (it's illegal in filenames), and the \xxx\xxx\xxx placeholders look like the actual path might be rather deep - verify that the total length is under 255 characters.

这篇关于如何修复wb.SaveAs函数上的错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！