在工作簿关闭时禁用 Excel VBA 中的剪贴板提示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5163265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Disable clipboard prompt in Excel VBA on workbook close
提问by Craig T
I have an Excel workbook, which using VBA code that opens another workbook, copies some data into the original, then closes the second workbook.
我有一个 Excel 工作簿，它使用 VBA 代码打开另一个工作簿，将一些数据复制到原始工作簿中，然后关闭第二个工作簿。
When I close the second workbook (using Application.Close), I get a prompt for:
当我关闭第二个工作簿（使用Application.Close）时，我收到提示：
Do you want to save the clipboard.
是否要保存剪贴板。
Is there a command in VBA which will bypass this prompt?
VBA 中是否有可以绕过此提示的命令？
回答by chris neilsen
I can offer two options
我可以提供两种选择
- Direct copy
- 直接复制
Based on your description I'm guessing you are doing something like
根据您的描述，我猜您正在做类似的事情
Set wb2 = Application.Workbooks.Open("YourFile.xls")
wb2.Sheets("YourSheet").[<YourRange>].Copy
ThisWorkbook.Sheets("SomeSheet").Paste
wb2.close
If this is the case, you don't need to copy via the clipboard. This method copies from source to destination directly. No data in clipboard = no prompt
如果是这种情况，则无需通过剪贴板进行复制。此方法直接从源复制到目标。剪贴板中没有数据 = 没有提示
Set wb2 = Application.Workbooks.Open("YourFile.xls")
wb2.Sheets("YourSheet").[<YourRange>].Copy ThisWorkbook.Sheets("SomeSheet").Cells(<YourCell")
wb2.close
- Suppress prompt
- 禁止提示
You can prevent all alert pop-ups by setting
您可以通过设置来阻止所有警报弹出窗口
Application.DisplayAlerts = False
[Edit]
[编辑]
- To copy values only: don't use copy/paste at all
- 仅复制值：根本不使用复制/粘贴
Dim rSrc As Range
Dim rDst As Range
Set rSrc = wb2.Sheets("YourSheet").Range("YourRange")
Set rDst = ThisWorkbook.Sheets("SomeSheet").Cells("YourCell").Resize(rSrc.Rows.Count, rSrc.Columns.Count)
rDst = rSrc.Value
回答by Sergiu
If I may add one more solution: you can simply cancel the clipboard with this command:
如果我可以再添加一个解决方案：您可以使用以下命令简单地取消剪贴板：
Application.CutCopyMode = False
回答by Chris Rae
I have hit this problem in the past - from the look of it if you don't actually need the clipboard at the point that you exit, so you can use the same simple solution I had. Just clear the clipboard. :)
我过去遇到过这个问题 - 从它的外观来看，如果您在退出时实际上并不需要剪贴板，那么您可以使用与我相同的简单解决方案。只需清除剪贴板。:)
ActiveCell.Copy
回答by Anil Chahal
If you don't want to save any changes and don't want that Save prompt while saving an Excel file using Macro then this piece of code may helpful for you
如果您不想保存任何更改并且不希望在使用宏保存 Excel 文件时出现保存提示，那么这段代码可能对您有所帮助
Sub Auto_Close()
ThisWorkbook.Saved = True
End Sub
Because the Savedproperty is set to True, Excel responds as though the workbook has already been saved and no changes have occurred since that last save, so no Save prompt.
由于该Saved属性设置为True，Excel 的响应就像工作簿已保存并且自上次保存以来未发生任何更改，因此没有保存提示。
回答by kid
There is a simple work around. The alert only comes up when you have a large amount of data in your clipboard. Just copy a random cell before you close the workbook and it won't show up anymore!
有一个简单的解决方法。只有当剪贴板中有大量数据时才会出现警报。只需在关闭工作簿之前复制一个随机单元格，它就不会再出现了！
回答by Raleigh Paenitz
Just clear the clipboard before closing.
只需在关闭前清除剪贴板。
Application.CutCopyMode=False
ActiveWindow.Close
回答by Alessio Mistretta
proposed solution edit works if you replace the row
如果您替换该行，建议的解决方案编辑有效
Set rDst = ThisWorkbook.Sheets("SomeSheet").Cells("YourCell").Resize(rSrc.Rows.Count, rSrc.Columns.Count)
with
和
Set rDst = ThisWorkbook.Sheets("SomeSheet").Range("YourRange").Resize(rSrc.Rows.Count, rSrc.Columns.Count)
