SaveAs 命令不起作用，但 SaveCopyAs 起作用 [英] SaveAs command does not work, but SaveCopyAs does

问题描述

我很难让 SaveAs 命令正常工作.对于本地医院，有从模板文件创建的患者图表，其中输入患者数据，然后手动重命名(使用另存为)，然后复制到另一个位置作为备份.模板会被反复使用.

I am having a lot of difficulty getting a SaveAs command to work properly. For a local hospital, there are patient charts which are created from a template file, in which patient data is entered after which it is manually renamed (using save-As) and then copied to another location as a backup. The template is re-used over and over again.

我的代码的目标是自动化这个过程.因此，我想从模板文件开始保存到两个不同的位置.不应覆盖模板文件.在模板中，用户分别在单元格K1和N1中设置部门名称和床位号.这些字段确定该文件夹中的文件夹和文件名.

The goal of my code is this to automate this process. Therefore I want to save to two different locations, starting from a template file. The template file should not be overwritten. In the template, a user sets the department name and bed number in cell K1 and N1 , repectively. These fields determine the folder and filename within that folder.

当按下保存按钮时，我的代码开始运行.我使用 SaveCopyAs 保存备份文件，然后我想使用 SaveAs 保存到我的主文件夹.SaveAs 应该将此新文件设置为我的工作文件，因此不会覆盖我的模板.至少这是我相信的......

When the save button is pressed, my code starts to run. I use SaveCopyAs to save the backup file and after that I want to use SaveAs to save to my primary folder. SaveAs should set this new file to be my working file, therefore not overwriting my template. At least this is what I believe...

问题:运行 SaveAs 时，Excel 崩溃(没有任何明确的错误消息).奇怪的是(对我来说)当我用 SaveCopyAs 替换 SaveAs 时不会崩溃.

THE PROBLEM: When running SaveAs, Excel crashes (without any clear error message). The strange thing (to me) is that is does not crash when I replace SaveAs with SaveCopyAs.

问题:为什么 Excel 会在此时崩溃?有没有办法解决或避免这种行为?我找不到不改变我的模板的合适解决方案.非常欢迎任何帮助或建议.

THE QUESTION: Why does Excel crash at this point? Is there a way to fix or avoid this behaviour? I cannot find a suitable solution that does not alter my template. Any help or suggestions are more than welcome.

下面的代码放置在我的ThisWorkbook"文件夹中，每次单击保存"按钮时都会执行.

The code below is placed in my "ThisWorkbook" folder and is executed every time I click the "save"-button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC
    bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2.
    newFileName = department & "\" & bedNumber & ".xls"

    If IsEmpty(department) Then
        MsgBox "You haven't entered a department. Please try again."
    ElseIf IsEmpty(bedNumber) Then
        MsgBox "You haven't entered a bed or room number. Please try again."
    Else
        ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName
    End If


    ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work

    'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template!
End Sub

推荐答案

以及设置 Cancel = True 以防止默认保存行为，添加:

As well as setting Cancel = True to prevent the default save-behaviour, add:

Application.EnableEvents = False
ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work
Application.EnableEvents = True

以防止再次调用相同的过程(并且再次..).这可能就是它崩溃的原因.

to prevent the same procedure being called again (and again..). This is probably why it crashes.

这篇关于SaveAs 命令不起作用，但 SaveCopyAs 起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！