VBA練習問題
VBA100本ノック 84本目:ブックの自動バックアップ

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-10

VBA100本ノック 84本目:ブックの自動バックアップ


ブックが保存される時に自動的にバックアップを作成し30世代だけ残す問題です。


ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータはご自身でご用意ください。


出題

出題ツイートへのリンク

#VBA100本ノック 84本目
ブックが閉じられる時に自動的にバックアップを作成してください。
Thisworkbookパスの下の"BACKUP"フォルダに作成。
ブック名_yymmddhhmmss.xlsm
最新の30世代だけを残し、それより古いバックアップは削除してください。
※当該ファイル以外は存在しません。


訂正です。
誤:閉じられる
正:保存される
イベントBeforeSaveに実装してください。


再度訂正です。
要件としては、万一のためのバックアップを30世代確保することです。
イベントは、BeforeSave、AfterSaveどちらでも構いません。


VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。


他の人の回答および解説を見て、書いたVBAを見直してみましょう。


頂いた回答

解説

出題が右往左往しましたが、とにかく自動でバックアップを作成して30世代確保します。
1秒単位なので数が多くなるので、連続して保存した時はバックアップを取らないようにSavedで判定しています。
BeforeSaveなら保存前の制御が可能です。AfterSaveなら保存されたかの確認ができます。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If ThisWorkbook.Saved Then Exit Sub
  Call VBA100_84_01(Me)
End Sub

Sub VBA100_84_01(ByVal wb As Workbook)
  Dim sPath As String: sPath = wb.Path & "\BACKUP"
  Dim fso As New Scripting.FileSystemObject
  Dim oFile As Scripting.File
  If Not fso.FolderExists(sPath) Then fso.CreateFolder (sPath)
  
  Dim ary
  For Each oFile In fso.GetFolder(sPath).Files
    Call insertArray(ary, oFile.Name)
  Next
  
  Dim i As Long
  For i = LBound(ary) To UBound(ary) - 30
    fso.DeleteFile sPath & "\" & ary(i), True
  Next
  
  Dim sFile As String
  sFile = Replace(wb.Name, ".xlsm", Format(Now(), "_yyyymmddhhmmss")) & ".xlsm"
  wb.SaveCopyAs sPath & "\" & sFile
  
  Set fso = Nothing
End Sub

Sub insertArray(ByRef ary, ByVal aIn As Variant)
  If IsEmpty(ary) Then
    ReDim ary(0): ary(0) = aIn
    Exit Sub
  End If
  
  Dim i As Long
  ReDim Preserve ary(UBound(ary) + 1)
  For i = UBound(ary) - 1 To LBound(ary) Step -1
    If aIn > ary(i) Then
      ary(i + 1) = aIn
      Exit Sub
    End If
    ary(i + 1) = ary(i)
  Next
  ary(LBound(ary)) = aIn
End Sub


30世代前の判定には、FSOで取得しながら順次ソートしています。
ファイル名でソートされて取得されるのが基本なので、ほぼそのまま詰みあがっていきます。
順序が違う場合のみソートに入れば良いので挿入ソートを使いました。
補足はありません。


補足

上記では- 30なので、この時点で30個が残ります。
その後に最新のバックアップが作られるので31個残ることになります。
問題文では30世代としているので、ここは-31が正解になります。
ただ、VBAを書きながらこのロジックの場合では、
30世代確保した上で最新バックアップを作ったほうが良いかなと思ってこのようにしてみました。
最新のバックアップがちゃんと作られるかの補償が無いからです。

むしろ、最新のバックアップを作り、それをinsertArrayで入れてから、DeleteFileしたほうが良いかもしれません。

Sub VBA100_84_01(ByVal wb As Workbook)
  Dim sPath As String: sPath = wb.Path & "\BACKUP"
  Dim fso As New Scripting.FileSystemObject
  Dim oFile As Scripting.File
  If Not fso.FolderExists(sPath) Then fso.CreateFolder (sPath)
  
  Dim ary
  For Each oFile In fso.GetFolder(sPath).Files
    Call insertArray(ary, oFile.Name)
  Next
  
  Dim sFile As String
  sFile = Replace(wb.Name, ".xlsm", Format(Now(), "_yyyymmddhhmmss")) & ".xlsm"
  wb.SaveCopyAs sPath & "\" & sFile
  Call insertArray(ary, sFile)
  
  Dim i As Long
  For i = LBound(ary) To UBound(ary) - 30
    fso.DeleteFile sPath & "\" & ary(i), True
  Next
  
  Set fso = Nothing
End Sub


サイト内関連ページ

第124回.Workbookのイベントプロシージャー
Workbookのイベントプロシージャーは、ブックに対し特定の操作(これがイベント)が行われた時に実行されます。イベントは、手動でもVBAでも、どちらで操作が行われても発生します。Workbookのイベントは多数用意されています。
第125回.Worksheetのイベントプロシージャー
Worksheetのイベントプロシージャーは、ワークシートまたはそのセルに対し特定の操作(これがイベント)が行われた時に実行されます。イベントは、手動でもVBAでも、どちらで操作が行われても発生します。Worksheetのイベントプロシージャーの一覧紹介と主要なイベントについて解説します。
ブックが閉じる時に自動実行(Workbook_BeforeCloseとAuo_Close)
Excelブックを閉じる時にマクロVBAを自動実行させる方法として、Workbook_BeforeClose Auo_Close この2通りの方法があります。それぞれの簡単な説明と、動作の違いを解説します。Workbook_BeforeClose Workbook_Closeは、ブックが閉じられたときに起動されるブ…




同じテーマ「VBA100本ノック」の記事

81本目:全フィルターの絞り込解除
82本目:ブックのドキュメントプロパティを取得
83本目:請求書を作成してPDF出力
84本目:ブックの自動バックアップ
85本目:請求日から入金予定日を算出
86本目:全シートの総当たり表を作成
87本目:数式のシート間の依存関係
88本目:クロスABC分析作成
89本目:2つのフォルダの統合
90本目:セルに重なっている画像の削除
91本目:時間計算(残業時間の月間合計)


新着記事NEW ・・・新着記事一覧を見る

還暦のVBA:VBAまでたどりつけるか… (2021-09-29)
VLOOKUPを使うことを基本としてシートを設計すべきか|エクセル雑感(2021-08-17)
コンピューターはブラックボックスで良い|エクセル雑感(2021-08-14)
小文字"abc"を大文字"ABC"に変換する方法|エクセル雑感(2021-08-13)
ADOでテキストデータを集計する|VBAサンプル集(2021-08-04)
VBA学習のお勧めコース|エクセル雑感(2021-08-01)
エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)
DXってなんだ? ITと何が違うの?|エクセル雑感(2021-06-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.Excelショートカットキー一覧|Excelリファレンス
3.変数宣言のDimとデータ型|VBA入門
4.RangeとCellsの使い方|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
8.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.並べ替え(Sort)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ