VBA 创建备份

VBA To Create Backup

提问by santhosha

I have a question on the macro events here. Below is my code. This performs the below Operations'

我对这里的宏观事件有疑问。下面是我的代码。这将执行以下操作'

Private Sub SaveAsTabDelimited(ByVal sFileName As String)

  With ActiveWorkbook

    .SaveAs Filename:=sFileName, FileFormat:=xlText, CreateBackup:=True

  End With

End Sub

Public Sub test()

  SaveAsTabDelimited "C:\Users\te160772\Desktop\Toad Test\Testsanthu.txt"

  ActiveWorkbook.Close savechanges:=True

End Sub

1. It converts the excel file into tab delimited txt file
2. It creates a backup copy of the excel file I wanted this action to be performed each day inorder the excel file to upload to Oracle tables on daily basis. The reason behind converting excel spreadsheet to tab delimited txt file is to preserve the format (To prevent the removal of leading zeros while exporting it to oracle)

1. 它将excel文件转换为制表符分隔的txt文件
2. 它创建了我希望每天执行此操作的 excel 文件的备份副本，以便每天将 excel 文件上传到 Oracle 表。将 excel 电子表格转换为制表符分隔的 txt 文件的原因是保留格式（防止在将其导出到 oracle 时删除前导零）

But now I am trying to enhance this code so that the following actions should be performed without any glitch. I have tried to do this in all the possible ways .since I am very new to the programming I was not able to do this.

但是现在我正在尝试增强此代码，以便执行以下操作而不会出现任何故障。我试图以所有可能的方式做到这一点。因为我对编程非常陌生，所以我无法做到这一点。

1. The code should convert the excel file to tab delimited save on my desktop (Hope that my current piece of code is sufficient for this action)
2. It should create a backup copy in a folder called “Repository” (A folder in a desktop). Each change to my workbook should result a backup copy with the version history if possible(Date and Time stamp in the file name)
3. the biggest problem associated with my code is that upon close, three separate dialogue boxes appear - one asking me if i want to save, the other asking me if i want to keep what i have copied in the clipboard, another one is replace the existing text file saved in the folder is there a way of answering these dialogue boxes (or suppressing them) from within the macro, so i do not need to manually click on yes or no each time the macro is run?
4. I have attached my macro to a “shape” in excel but my priority is to run my macro upon closing of my workbook. Every time when any changes happen to my workbook and when I save the workbook, it should create a tab delimited Text files which should replace the existing Txt File without any confirmation dialogue boxes.

1. 代码应该将 excel 文件转换为制表符分隔保存在我的桌面上（希望我当前的代码段足以执行此操作）
2. 它应该在名为“Repository”（桌面中的文件夹）的文件夹中创建一个备份副本。如果可能，对我的工作簿的每次更改都应生成带有版本历史记录的备份副本（文件名中的日期和时间戳）
3. 与我的代码相关的最大问题是关闭时，会出现三个单独的对话框 - 一个问我是否要保存，另一个问我是否要保留我在剪贴板中复制的内容，另一个是替换现有的保存在文件夹中的文本文件有没有办法从宏中回答这些对话框（或抑制它们），所以我不需要在每次运行宏时手动单击是或否？
4. 我已将我的宏附加到 excel 中的“形状”，但我的首要任务是在关闭工作簿时运行我的宏。每次当我的工作簿发生任何更改并保存工作簿时，它应该创建一个制表符分隔的文本文件，该文件应该替换现有的 Txt 文件，而无需任何确认对话框。

Please help me on this. This is badly require for me

请帮我解决这个问题。这对我来说非常需要

With a million thanks

一百万感谢

回答by Siddharth Rout

The code should convert the excel file to tab delimited save on my desktop (Hope that my current piece of code is sufficient for this action)

代码应该将 excel 文件转换为制表符分隔保存在我的桌面上（希望我当前的代码段足以执行此操作）

Yes, either you can hardcode the path to the desktop or use this code to get the path to the desktop automatically

是的，您可以对桌面路径进行硬编码，也可以使用此代码自动获取桌面路径

Sub GetDesktopPath()
    Dim oWS As Object
    Dim DskTopPath As String

    Set oWS = CreateObject("WScript.Shell")

    DskTopPath = oWS.SpecialFolders("Desktop")

    Debug.Print DskTopPath

    Set oWS = Nothing
End Sub

---

---

It should create a backup copy in a folder called “Repository” (A folder in a desktop). Each change to my workbook should result a backup copy with the version history if possible(Date and Time stamp in the file name)

它应该在名为“Repository”（桌面中的文件夹）的文件夹中创建一个备份副本。如果可能，对我的工作簿的每次更改都应生成带有版本历史记录的备份副本（文件名中的日期和时间戳）

You can use the Workbook_BeforeSaveevent to create a copy of the existing workbook.

您可以使用该Workbook_BeforeSave事件来创建现有工作簿的副本。

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

End Sub

You may want to explore ThisWorkbook.SaveCopyAsSee Excel Help for more info.

您可能需要探索ThisWorkbook.SaveCopyAs查看 Excel 帮助以获取更多信息。

---

---

the biggest problem associated with my code is that upon close, three separate dialogue boxes appear - one asking me if i want to save, the other asking me if i want to keep what i have copied in the clipboard, another one is replace the existing text file saved in the folder is there a way of answering these dialogue boxes (or suppressing them) from within the macro, so i do not need to manually click on yes or no each time the macro is run?

与我的代码相关的最大问题是关闭时，会出现三个单独的对话框 - 一个问我是否要保存，另一个问我是否要保留我在剪贴板中复制的内容，另一个是替换现有的保存在文件夹中的文本文件有没有办法从宏中回答这些对话框（或抑制它们），所以我不需要在每次运行宏时手动单击是或否？

You can get rid of these alerts by simply sandwiching your code between

您可以通过简单地将代码夹在中间来摆脱这些警报

Application.DisplayAlerts = False
'~~> Your Code
Application.DisplayAlerts = True

If you are using clipboard a lot, then you might also want to clear it by using

如果您经常使用剪贴板，那么您可能还想使用

Application.CutCopyMode = False

---

---

I have attached my macro to a “shape” in excel but my priority is to run my macro upon closing of my workbook. Every time when any changes happen to my workbook and when I save the workbook, it should create a tab delimited Text files which should replace the existing Txt File without any confirmation dialogue boxes.

我已将我的宏附加到 excel 中的“形状”，但我的首要任务是在关闭工作簿时运行我的宏。每次当我的工作簿发生任何更改并保存工作簿时，它应该创建一个制表符分隔的文本文件，该文件应该替换现有的 Txt 文件，而无需任何确认对话框。

You can use the Workbook_BeforeCloseevent to run your relevant code

您可以使用该Workbook_BeforeClose事件来运行您的相关代码

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Regarding the alert, I have already explained that in the previous section.

关于警报，我已经在上一节中解释过了。

Hope this sets you in the right path.

希望这能让你走上正确的道路。