使用ActiveWorkbook.SaveAs时，不会执行Workbook_BeforeSave中的命令 [英] Commands in Workbook_BeforeSave are not executed when ActiveWorkbook.SaveAs is used

问题描述

初步备注：我已经在论坛中找到了有关此问题的文章，但没有真正的解决方法。对于我所面临的条件，解决方案的建议是不可行的。我想知道的是：这是微软的错误还是
不是？如果有的话，是否有真正的可用解决方案？

Preliminary Remark: I have found articles about this problem in forums already but there is no true fix to it. The proposals for workarounds are not feasible for the conditions I am facing. What I want to find out is: Is this a Microsoft bug or not? If so, is there a true fix for it available?

问题：我的Excel工作表中有一个Before_Save例程。简而言之，它的功能只是隐藏工作簿中的一些工作表并选择一个特定的条目表。这将确保一旦工作簿再次打开（例如由另一个用户），他将在条目表中找到
。当我通过常规菜单选项保存（或"另存为"）时，代码工作正常。但是，如果使用ActiveWorkbook.SaveAs，则不会执行Before_Save块中的重要命令。我可以调试代码
并看到输入了Before_Save块。甚至执行MsgBox命令。但是，例如，隐藏工作表不会被执行。也没有出现错误。当这些命令发生时，它只是没有做任何事情。

The issue: I have a Before_Save routine in my Excel sheet. In a nutshell, its function is only to hide some sheets in the workbook and select a spefic entry sheet. This shall ensure that once the workbook is opened again (e.g. by another user), he will find himself in the entry sheet. The code works fine when I save (or "save as") via the regular menue options. However in case the ActiveWorkbook.SaveAs is used, the significant commands in the Before_Save block are not executed. I can debug the code and see that the Before_Save block is entered. Even MsgBox commands are executed. But the commands for e.g. hiding a worksheet are not executed. There is also no error appearing. It is just not doing anything when these commands are occuring.

我已经尝试了很多方法来克服这个问题：通过创建一个类将代码从Workbook级别移动到App级别，尝试将其移动到一个单独的模块，尝试再次保存工作簿等。所有这些都不会改变命令未执行的
事实。我认为我需要对问题进行真正的解决。

I have already tried a number of things to overcome this like: Moving the code from the Workbook level to App level by creating a class, trying to move it into a seperate module, try to save the workbook for a second time etc. All this does not change the fact that the commands are not executed. I think that I need a true fix to the problem.

问题：这现在是微软的官方错误吗？或者这是设计？如果"按设计"，这种行为的原因是什么？如果是"一个错误"，我们可以期待一个真正的解决方案吗？谁可以被解决（@ Microsoft）回答这个问题？

Question: Is this now an official bug by Microsoft? Or is this by design? If "by design", what is the reason for this behaviour? If "a bug", can we expect a true fix for it? Who could be addressed (@ Microsoft) to answer this?

欢迎任何帮助或建议。

谢谢

彼得

推荐答案

简短测试表明下面的代码适用于XL2010。创建一个新文件并自己尝试。

A short test shows that the code below works with XL2010. Make a new file and try it by yourself.

Andreas。

Private Sub Workbook_BeforeSave（ByVal SaveAsUI As Boolean ，取消为布尔值）

  Dim i As Integer

 对于i = 3 To Sheets.Count

   表格（i）.Visible = xlVeryHidden

 下一个

 表格（1）。选择

结束子B1


私人子工作簿_打开（）

  Dim i As Integer

 对于i = 1到3

    Sheets.Add

 下一个

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim i As Integer
  For i = 3 To Sheets.Count
    Sheets(i).Visible = xlVeryHidden
  Next
  Sheets(1).Select
End Sub

Private Sub Workbook_Open()
  Dim i As Integer
  For i = 1 To 3
    Sheets.Add
  Next
End Sub

这篇关于使用ActiveWorkbook.SaveAs时，不会执行Workbook_BeforeSave中的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！