Access

【Access VBA】指定された Book ファイルを 月日時分 _MMDDHHMM をつけてコピーする

指定された Book ファイルを 月日時分 _MMDDHHMM をつけてコピーする

この記事では「指定された Book ファイルを 月日時分 _MMDDHHMM をつけてコピーする」ために利用可能な CopyBookWithMMDDHHMM というユーザー定義関数について紹介します。 この情報が読者のお役に立てば幸いです。

解決できること

この CopyBookWithMMDDHHMM は例えば、RedMine からの ダウンロードファイル issue.csv をもとにプロジェクト管理ファイル.xlsx ファイルを更新しているようなケースで、この issue.csv のファイル作成タイムスタンプ情報から接尾語 _MMDDHHMM を抽出し  プロジェクト管理ファイル.xlsx から プロジェクト管理ファイル_MMDDHHMM.xlsx を作成したいようなときに使用します。

具体的には Microsoft Access の中でここで紹介する CopyBookWithMMDDHHMM をマクロの中で呼び出すと 指定された参照テーブルに保存された ファイルの生成時間 から､月日時分  ‘_MMDDHHMM‘ 付(例えば ‘_03271230″ で3月27日12時30分に作成という意味)を抽出し、そして　％USERPROFILE% からの相対パスで指定されたターゲットのブックファイル名にこの’_MMDDHHMM‘ を付加した名称のブックファイルにコピーします。

ここに ％USERPROFILE% は Windows であらかじめ定義されている環境変数で Windows 10 搭載 PCを使っている利用者のデフォルトのフォルダパスを表しています。この環境変数については必要に応じて以下の記事を参照下さい。

【Windows業務効率化】バッチファイルの基本テクニックを知ろう

読んでほしい方

こうした Microsoft Access VBA を使ったアプリケーションをこれから作ろうと思っている方、今まで VBA によるアプリケーションを作ってきたが、そのツールを第三者に提供する際に、フォルダの保存パス名等の利用者個人に依存した環境差分をVBAのプログラムの中にじかに書くのに抵抗がある方。

 

CopyBookWithMMDDHHMM 関数 の紹介

説明

指定テーブルに保存されているファイル生成時間を参照し、 そして ％USERPROFILE% からの相対パスで指定されたターゲットとなるブック ファイルを 　％USERPROFILE% からの相対パスで指定されたターゲットとなるブック ファイルと同じフォルダ配下にこのブックファイル名 の後ろに接尾語  _MMDDHHMM を追加したブックファイルとしてコピーします。

参照オブジェクト

この関数を利用するためにはこの関数の実行前に データベースツール > Visual Basic > ツール > 参照設定

 

の中で

  • Microsoft Office 16.0 Access Database engine
  • Microsoft ActiveX Data Objects 6.1 Library

にチェックを入れる必要があります。

利用例

本ツールの利用にあたっては事前に

【Access VBA】指定フォルダ配下のファイル名を保存するテーブルを自動生成する

で紹介した MakeBookNameTable  を使い “FileNamesList” テーブルを作成し

【Access VBA】指定フォルダ配下のファイル名を自動取得する

で紹介した GetBookNameList を使い “FileNamesList” テーブルに指定フォルダ配下に保存されているファイル名を取得したあと この “FileNamesList” テーブルからタイムスタンプ MMDDHHMM を取り出すファイルのみを残すための各種クエリ操作を行いレコード登録数を１個にします。

次に Access のマクロ定義例えば

“M0020_Call_CopyBookWithMMDDHHMM” の中で

つまり


If CopyBookWithMMDDHHMM("FileNameList","Documents\Desktop\T1040_日付_退院者数_入院者数_退院率.xlsx")
  メッセージボックス ("Documents\Desktop\T1040_日付_退院者数_入院者数_退院率.xlsx" のコピー作成に失敗しました,はい,なし)
If 文の最後

のように CopyBookWithMMDDHHMM を “Documents\Desktop\T1040_日付_退院者数_入院者数_退院率.xlsx“といったパラメータを付けて呼び出します。 ここに “FileNameList” は取得したいファイルの フルパス名 、ファイル名 、ファイル作成日時 が入っているテーブル名、 “Documents\Desktop\T1040_日付_退院者数_入院者数_退院率.xlsx” はコピー元となるブックファイルの  ％USERPROFILE% からの相対ファイル名です。 この “M0020_Call_CopyBookWithMMDDHHMM” のでデザインビューを閉じて、この　”M0020_Call_CopyBookWithMMDDHHMM” マクロをダブルクリックすると、マクロ  CopyBookWithMMDDHHMMが実行され、コピー元となるブックファイルの  ％USERPROFILE% からの相対ファイル名と同じフォルダ配下に以下のように  “Documents\Desktop\T1040_日付_退院者数_入院者数_退院率_MMDDHHMM.xlsx” ブックが作成されます。

この画像は Microsoft が2020 年にリリースした 新 Windows Terminal ソフトでのコマンドプロンプト画面です。

引数

属性 意味
参照テーブル名 文字型 タイムスタンプを取得したいファイルの フルパス名 、ファイル名 、ファイル作成日時 が入っているテーブル名
ブックのフルパス名 文字型 タイムスタンプ付きを作成したいターゲット Excel ブックを　％USERPROFILE% からの相対パス名

戻り値

値 意味
0 正常終了
1 異常終了

VBAコード


Function CopyBookWithMMDDHHMM(reference_table_name As String, relative_path_book_name As String) As Integer

  Const C_SUCCESS As Integer = 0
  Const C_FAILURE As Integer = 1

  Dim databaseCurrent As Database
  Dim referenceTableName As New ADODB.recordSet

  Dim fullPathBookName As String

  fullPathBookName = Environ("UserProfile") & "\" & relative_path_book_name

  On Error GoTo exitWithFailure

  referenceTableName.Open reference_table_name, CurrentProject.Connection

  Dim bookDateTime As Date
  Dim mmddhhmm As String
  Dim bookName As String
  Dim newBookName As String
  Dim fullPathName As String

  Do Until referenceTableName.EOF
    bookDateTime = referenceTableName![bookDateTime]
    referenceTableName.MoveNext
  Loop
  mmddhhmm = Format(bookDateTime, "MMDDHHMM")
  referenceTableName.Close

  bookName = Dir(fullPathBookName)
  fullPathName = Left(fullPathBookName, Len(fullPathBookName) - Len(bookName))
  newBookName = Left(bookName, Len(bookName) - 5) & "_" & mmddhhmm & ".xlsx"

  FileCopy fullPathBookName, fullPathName & "\" & newBookName

  CopyBookWithMMDDHHMM = C_SUCCESS
Exit Function

exitWithFailure:
  CopyBookWithMMDDHHMM = C_FAILURE
End Function

VBAコードの簡単な説明

行番号 説明
1 関数名と関数の戻り値の型(Integer:整数型)、入力パラメータ reference_table_name   、relative_path_book_name の名前、型(String:文字列型)を定義しています。
3-4 このプログラムの中で利用する定数C_SUCCESS(0:成功)、C_FAILURE(1:失敗)を定義しています。ここでは38行目、42行目ででこの関数の戻り値が C_SUCCESS(0:成功)、C_FAILURE(1:失敗)であることが明確にわかるようなコーディングの仕方になります。
6-7 ADO(注1) を用いてオブジェクト  referenceTableName  の定義をします。この referenceTableName  は 入力パラメータで指定された 参照テーブル名 reference_table_name に設定されます。
9-11 Environ 関数を使用して利用者の %USERPROFILE% に相当する絶対パス名 userProfileを取得しパラメータとして入力された relative_path_book_name を結合し、ターゲットファイルの絶対パス名 fullPathBookName  を求めます。
13 この関数の実行中にエラーが発生したときの例外処理として、exitWithFailure にジャンプします。
15 referenceTableName  で指定されたテーブルをオープンします。
17-21 この関数の中で使用する変数 bookDateTime 、 mmddhhmm 、 bookName 、  newBookName 、 fullPathName を定義しています。bookDateTime は日付型でそのほかの変数は文字列型です。
23-27 referenceTableName  で指定されたテーブルから、“bookDateTime”というフィールド値を読み出し、これをもとに”＿MMDDHHMM”という接尾語を作成します。
28 参照テーブルをクローズします。
30 fullPathBookName から絶対パス部分を除き、対象ブック名部分のみを bookName として定義します。

31

 パラメータとして入力された full_path_name の絶対パス部分を fullPathName として定義します。
32 bookName に ‘_MMDDHHMM’ をつけたブック名を newBookName として定義します。
34 fullPathBookName で指定されるブックを newBookName  コピーします。
36-37 この関数の戻り値に C_SUCCESS(0:成功) を設定して関数を終わります。
39-41 この関数の戻り値に C_FAILURE (1:失敗) を設定して関数を終わります。
(注1) ADO (ActiveX Data Objects) : DAOの後継としてMicrosoftが仕様策定した統一的方法でデータベースを操作、参照するためのソフトウェア部品のパッケージおよびその呼び出し規約（API）。ActiveXコントロールとして様々なソフトウェアやプログラミング言語から共通の方法で簡単にデータベースにアクセスでき、また表計算ソフトのワークシートやCSVファイルなどを含む様々な形式のデータに同じ手順でアクセスすることができます。

まとめ

この記事では「指定された Book ファイルを 月日時分 _MMDDHHMM をつけてコピーする」ために利用可能な CopyBookWithMMDDHHMM というユーザー定義関数について紹介しました。 この情報が読者のお役に立てば幸いです。

ライブラリ一覧