Accessによる在庫管理　その11　在庫情報のExcel出力

これまで10回の記事で在庫管理向けデータベースを作成し、最低限運用に必要な機能を搭載してきました。経験上、データが蓄積されてくると、データをExcel等のファイルに出力して確認したいといったニーズが出てきます。Accessは、Excelとも連携が非常に取りやすく、これからご紹介するデータ出力の方法も、頻繁に使用することになると思います。

コマンドボタンの配置
出力する情報について
出力するファイル名について
ファイルの保存先指定
ファイルの有無を確認
Accessによるエクスポート処理
エクスポートしたExcelファイルの起動
まとめ

コマンドボタンの配置

まずは、ファイル出力をするためのコマンドボタンとして「cmd_ファイル出力」を図1に示すように配置し、ラベルも併せて配置します。

次に、「cmd_ファイル出力」コマンドボタンをクリックした際に、イベントが発生するようにVBAソースコードを記入します。当該コマンドボタンの「クリック時」プロパティに[イベントプロシージャ]を設定します。これにより、VBA画面上には以下のソースコードが追加されますので、この中にそれぞれの処理を記入していきます。

Private Sub cmd_ファイル出力_Click()

'ここに処理を記入する。

End Sub

出力する情報について

今回は、在庫情報として「品目」、「型番」、「製造元」及び最新の「在庫数」を出力することとします。そこで、在庫データ出力用のクエリを作成します。（クエリの作成方法は、以下の記事などをご参照ください。）

まず、クエリデザインビューにて、「T_品目マスタ」と「T_在庫テーブル」をそれぞれ「品目ID」フィールドと「品名」フィールドで内部結合します。

続いて、集計アイコンをクリックし、「品名」、「型番」、「製造元」及び「在庫数」フィールドを【フィールド】項目に配置します。また、【集計】項目において、「在庫数」フィールドは「合計」を、それ以外は「グループ化」とします。

最後に、「Q_在庫出力用」としてクエリを保存します。

出力するファイル名について

出力するExcelファイルの名前は、「YYMMDD##_在庫一覧.xlsx」とします。ここで、「YYMMDD」とは西暦の下2桁、月を2桁、日にちを2桁で表記するという意味です。また、「##」とは、連番を意味しており、保存先に同一ファイルがあった場合は、この値を順次増やしていく方法をとります。また、拡張子はExcelブックである「xlsx」形式とします。

例） 2020年3月8日に初めて作成した場合のファイル名：「20030801_在庫一覧.xlsx」

以上を条件として、VBAのソースコードを記入していきましょう。

ファイルの保存先指定

「cmd_ファイル出力」ボタンをクリックした際に、ファイルを出力するかの確認を行うために、次のソースコードを追加してください。メッセージボックスで「いいえ」を選択したら処理を中止するプロセスとしています。

If MsgBox("在庫データを出力しますか？", vbYesNo) = vbNo Then
　　MsgBox "処理を中止します。"
　　Exit Sub
End If

続いて、ファイルの保存先を指定するため、Microsoft Office Object Libraryを用います。Microsoft Office Object Libraryを用いるためには、VBA画面から参照設定を行います。VBA画面を開き、「ツール」→「参照設定」と選択します。そして、「Microsoft Office XX.X Object Library」にチェックを入れてください。なお、「XX.X」としているのは、Officeのバージョンで値が変わるためです。ご自身のバージョンにあったものを選択してください。

図3　Microsoft Office Object Libraryの参照設定方法

チェックを入れたのち、以下のソースコードを追加します。

'■保存先指定 
Dim inttype As Integer
Dim varSelectedFile As Variant
Dim FileSelect As String

'ファイルを選択する場合は、msofiledialogfilepicker を使う。
'フォルダーを選択する場合は、msofiledialogfolderpicker を使う。
'今回は、フォルダー参照を設定する。
inttype = msoFileDialogFolderPicker 

'ファイル参照用の設定値をセットします。
With Application.FileDialog(inttype)
　　'ダイアログタイトル名
　　.Title = "ファイル選択"

　　'最初に開くフォルダーを当ファイルのフォルダーとします。
　　.InitialFileName = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))

　　If .Show = -1 Then 'フォルダーが選択されれば　-1 を返します。

　　　　For Each varSelectedFile In .SelectedItems
　　　　　　FileSelect = varSelectedFile
　　　　Next

　　Else
　　　　Exit Sub
　　End If

End With

まず、「inttype = msoFileDialogFolderPicker」は、ユーザーによるフォルダー選択を可能にさせる値を「inttype」変数に格納しています。この条件で、「Application.FileDialog(inttype)」により、フォルダー選択画面を開きます。なお、With文をセットすることで、With文範囲内の頭文字が「.」で示されている箇所は、FileDialogのプロパティから記載することができるようになります。

今回の処理では、在庫管理DBが保存されている箇所を最初に開くフォルダーとして設定するため、「CurrentDB.Name」で自身のファイルパスを取得します。さらに、InStrRev(CurrentDb.Name, “\”)で、「\」の文字列をファイルパスの後方から検索して見つかった位置を返すことで、Left関数と組み合わせて、自身が保存されているフォルダーの位置を取得します。（図4に示す例をご参照ください。）

次に、ユーザーによりフォルダーが選択されればフォルダーパスを「FileSelect」変数に格納します（フォルダーパスの一番最後は「\」を含みません。）。もし、フォルダーが選択されなかった場合は、キャンセルとなりIf-else文の「Exit Sub」が実行されます。

ファイルの有無を確認

さて、保存先フォルダーが決定した後は、「YYMMDD##_在庫一覧.xlsx」という形式でファイル名を決めます。この際、同一ファイル名のExcelファイルが既に保存されていると、誤って上書きする危険性があるため、ファイル名中の「##」で連番を付番するようにします。「##」は「01」から始まり、同一ファイルがあれば「02」→「03」・・・とインクリメントをします。そこで、指定先フォルダにおける当該ファイルの有無をチェックする機能を次のように実装します。

'■ファイル名の設定
Dim newFile As Double
Dim strPath As String '作成するファイル名YYMMDD連番ファイル名(文字列)
Dim srchXls As String '作成するフルパス

'newFileをYYYYMMDD00とする（Valにより数字に変換、西暦は下2桁にしないのはゼロ落ちを防ぐため。）
newFile = Val(Format(Date, "yyyymmdd") & Format(0, "00")) 

'後判定のDo Loop文 
Do
　　'newFileの00部分に＋1 
　　newFile = newFile + 1
　　
　　'YYMMDD00_ファイル名+拡張子  
　　strPath = Right(newFile, 8) & "_在庫一覧.xlsx"

　　'サーバー上にファイルを保存
　　srchXls = FileSelect & "\" & strPath

'Dir検索で　指定したファイル名があればループから抜ける  
Loop Until strPath <> Dir(srchXls, vbNormal)

まず、Val関数を用いて「YYYYMMDD00」形式を倍浮動小数型にキャストします。この時、西暦4桁にしているのはゼロ落ちを防ぐためです。

次に、後判定のDo Loop文により、必ずファイル名の連番部分が「01」から開始となるようにします。Right関数（以下記事参照）により「YYMMDD##」形式とし、さらに「_在庫一覧.xlsx」を付与します。

さらに、先に指定したフォルダーパスとファイル名を「\」で繋いで、フルパスを「srchXls」変数に格納します。

最後に、Dir関数を用いてファイルの有無を確認します。ここで、Dir関数は指定したファイル（第1引数）が存在するとき、そのファイル名を返す関数となります。つまり、「Dir(srchXls, vbNormal)」でフルパスを指定すると、該当するファイルが存在すれば、戻り値は「strPath」が返されることになります。したがって、「strPath <> Dir(srchXls, vbNormal)」がTrueになれば当該ファイルは存在しないことになり、Do Loop文から抜け出すことになります。

Accessによるエクスポート処理

Excelファイルの出力は、Accessに搭載されている「Docmd.TransferSpreadsheet」を用います。これまでに作成した出力用クエリと、ファイル名を用います。以下のソースコードを追加してください。

'■ファイルの出力
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Q_在庫出力用", srchXls, True, "在庫一覧"

上で示した「Docmd.TransferSpreadsheet」の引数はそれぞれ次のことを意味しています。

第1引数（acExport）：ファイルをエクスポートする。
第2引数（acSpreadsheetTypeExcel12Xml）： Microsoft Excel 2010以降XML形式（.xlsx）
第3引数（”Q_在庫出力用”）：エクスポート内容を示す選択クエリの名前
第4引数（srchXls）：エクスポートするファイルのパス
第5引数（True）：フィールド名をワークシートの最初の行に使用する場合にTrue
第6引数（”在庫一覧”）：セルの範囲の名前

たった1行で「Q_在庫出力用」クエリの内容をExcelファイルとして出力することができました。この際の保存先は、最初にご自身で指定された場所となります。

エクスポートしたExcelファイルの起動

作成したExcelファイルを確認するため、Shell関数を用いてファイルを開きます。以下のソースコードを追加してください。なお、ファイルパスは「”」で括る必要があり、Chr(&H22）で指定しています。

If MsgBox("作成したファイルを開きますか?", vbYesNo) = vbYes Then

　　 'Shellで作成したExcelを起動 
　　Shell "Excel.exe " & Chr(&H22) & srchXls & Chr(&H22), vbNormalFocus

End If

これにより、作成したExcelファイルが起動し、内容を確認することができます。