【Excel VBA】自動的にバックアップファイルを作成したい

ファイルを閉じるとき(右上の[×]ボタンクリック時。)、
指定のパスに保存する方法を教えてください。

現在のファイルの内容、シートもそのままで、xlsx、xlsmの状態もそのまま。
但し、そのままのファイル名の末尾に"_今日の日付_時間"（_yyyymmdd_hh:mm）を付けるだけ。
画面に何も問合せを行わず、ユーザは何も感じないように。

（可能であれば）
ファイルを開いたフォルダ直下の「backup」フォルダを自動的に宛先に出来るでしょうか？
「backup」フォルダが存在しなければ新規作成して保存したいです。
つまり、元のファイルがいつも通りの場所にあれば、そこの「backup」フォルダに、日時が付与されたファイル名で保存。
ファイルが別のフォルダに移動されてた場合は、そこに「backup」フォルダを作成して保存。
見た目上何も確認やエラーが出ないように出来れば幸いです。

質問者からの補足コメント

• 

  本質問と関係ないのですが、
  現在、お礼を送信しようとすると
  「△リクエストの有効期限が切れました。」と赤字アラートが出て、投稿できません。
  それぞれにコメントしたいのですが出来ない状況です。ご了承ください。
  明日以降再チャレンジしてみます。

    補足日時：2021/11/25 20:20

No.3 回答者： Qchan1962 回答日時： 2021/11/25 14:21

＃２　です

一応、アドバイスを、、、
私ならBookをそのままバックアップとして保存はしないと思います。
CSV形式などのテキストで必要なデータだけを保存します。
復元の時は、そのデータを読み込めば良いのでブックすべてでなくても良く無駄な容量を使わずに済みます。（処理速度の向上も図れます）

また、フォルダ内のバックアップファイルの保存期間などを設定して
過去のファイルを削除するようにすると思います（やっています）

この様な場合、元ファイルの設定自体を変える必要があるかも知れませんが、（以前の回答にCSVファイルで保存しては、と確か触れたような気がします）

私が良く作るシート構成
印刷、閲覧シート（印刷、PDF作成、配布資料に使う）
入力、操作シート（入力、情報修正、加工元データに使うなど）メイン
入出力データシート（データ保存や復元、情報ソース）複数シート
設定シートなど（ユーザー権限、使用期間、各種加工キーワードなど）
加工用シートなど（VBAでやるのでほぼ使わない）
何かの期会があれば、参考にされてください。

この回答へのお礼

ありがとうございます。アドバイス非常に助かります。
シート構成も非常に勉強になります。

書き出しデータは効率よく最小限で行った方が良いのも同意です。
私は最初の作成まではやりますが、その後の運用のどこまで付き合うか分からないので、極力特殊性を省いた形で考えてます。
(個人的にはデータサイズを非常に気にしますが、ユーザは1MBも2MBもそんな気にしない。HDDが空いてたら何でもOKなので^^;)

ここでの質問ではありませんが、全データはGoogleドライブに置かれています。
ファイルストリームというツールで、ローカルドライブの様にマウントしてますが、ブラウザから辿って開けてしまいます。
(8割以上のファイルはいくつかのクラウドシステムからスプレッドシートへのリンクが張られているため、ブラウザで開くのは禁止！と言えないのです)

お礼日時：2021/11/25 20:21

No.2 回答者： Qchan1962 回答日時： 2021/11/25 13:48

こんにちは

一連のご質問から、実行タイミングなどはご存知かと思います。
なさりたい事はこちらのサイトを参考にされると良いと思います。
https://valmore.work/about-file-system-object/

また、サンプルコードをお望みと思いますので、説明なしですが下記に
ざっくり書いてみました。ほぼ検証デバッグしていませんが、参考程度ならOKかな？

1点　ファイル名の:には問題があると思いますので　_で

Option Explicit
Sub sample()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim bckPath As String
Dim Bk_name As String
bckPath = ThisWorkbook.Path & "\Backup"
Bk_name = ThisWorkbook.Name
On Error Resume Next
If fso.FolderExists(bckPath) Then
Call fileRename(bckPath & "\" & Bk_name)
Else
fso.CreateFolder (bckPath)
Call fileRename(bckPath & "\" & Bk_name)
End If
End Sub
Sub fileRename(Fol_File As String)
Dim fso As Object, f As Object
Dim Bk_name As String
Dim ext As String
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
Fol_File, True
Set f = fso.GetFile(Fol_File)
ext = fso.GetExtensionName(ThisWorkbook.Path & ThisWorkbook.Name)
Bk_name = fso.GetBaseName(Fol_File)
f.Name = Bk_name & Format(Date, "_yyyymmdd") & Format(Time, "_hh_mm") & "." & ext
Set f = Nothing
End Sub

同名（同じ時間（分）内に実行された場合）、フォルダ内の元ファイルが保存されます。次回実行時なくなります。これは、エラーによりリネームされない為です。ファイル内データは　, True　により上書きされます

No.1 回答者： fujillin 回答日時： 2021/11/25 12:22

こんにちは

VBAであれば、ThisWorkbookモジュールでWorkbook.BeforeCloseイベントを利用すれば可能と思います。
ただし、「×」印のクリックとコマンド等からの終了などを区別するのは難しいです。
このイベント内に、ご質問のような処理を記述しておけばよいでしょう。

＞見た目上何も確認やエラーが出ないように出来れば幸いです。
確認表示等を出さなくすることも可能ですが、通常の SaveAs や Save メソッドで保存する場合はアラートが出されることはありませんので、こちらを利用すれば宜しいでしょう。
（Closeメソッドだと「変更を保存しますか？」の表示がでますけれど）
エラーが出ないようにするには、そのようなきちんとした（エラーのない）コードを組むしかないですね。

とは言え、保存処理を2回行うことになるので、通常の「閉じる」の場合に比べると、約2倍の時間がかかるのは避けられないので、ユーザがまったく気づかないかと言えば必ずしもそうではないかも知れません。

この回答へのお礼

ありがとうございます。

>（Closeメソッドだと「変更を保存しますか？」の表示がでますけれど）

細かいご指摘ありがとうございます。
Excel上で通常表示されるメッセージはそのままで良いので大丈夫です。


>保存処理を2回行うことになるので、通常の「閉じる」の場合に比べると、約2倍の時間がかかるのは避けられない

大丈夫です。UI上というか表示画面の流れ上、変化が無ければ十分です。
たまに、ちょっと止まったっぽい動作の変化は日常茶飯事ですので(笑

お礼日時：2021/11/25 12:26