前回は、在庫管理データベースに保存している在庫データをExcelファイルに出力する方法を紹介しました。この際、得られたExcelファイルは単なるテキストの貼り付けで、印刷等をする際には、別途体裁を整える必要がありました。
そこで、今回はAccessからExcelを起動し、最低限の体裁を整える処理方法について紹介をしていきます。なお、整形するExcelファイルは前回の記事で作成した在庫データとし、Shell関数でExcelファイルを起動する前に整形処理をします。
なお、Excel VBAについては、様々な書籍等で紹介されていますので、ここでの説明は割愛させていただきます。
AccessからのExcel起動
AccessからExcelを起動するために、エクセルオブジェクトを作成し、この中でExcelファイルの処理をしていきます。前回紹介した、Excelファイルをエクスポート処理する「Docmd.TransferSpreadsheet」の後ろに、次のコードを追記してください。
'Excelレイアウト処理 Dim objExcel Dim objSelection'エクセルオブジェクトを作成します。Set objExcel = CreateObject("Excel.Application")'エクセル画面を表示します。objExcel.Visible = False'ワークブックを開きます。objExcel.Workbooks.Open srchXls'一つ目のワークシートをobjSelectionにセットします。Set objSelection = objExcel.Workbooks(1).Worksheets(1)
まず、Excelファイルを加工するためのオブジェクトを設定します。そして、「objExcel.Visible = False」で画面上にExcel画面を表示せずに処理をする設定をします。続いて、Excelファイルを開いてWorkSheetの1番目を開いて処理を開始できる状態にします。
ここまでで、エクスポートしたExcelファイルの加工ができる状態になりました。次に、全ての行を文字列の幅に合わせる処理、1行目の背景色をグレーにし、罫線で区切りを設定する処理を行います。
Accessでは、Excelの関数等を利用するために「Microsoft Excel XX.X Object Library」を参照設定することで、罫線の設定、フォント、印刷範囲や用紙サイズ等の設定、等々、様々なニーズに合わせた処理をできますが、今回は、最低限の処理にとどめた紹介にいたしますので、あしからずご了承ください。
Excelファイルの加工
今回出力するフィールドは4つです。そこで、ExcelのA列からD列までを文字列の幅に合わせて整形します。続いて、Excelセルの「A1」～「D1」をタイトル行として背景色をグレーにします。
まずは、以下のソースコードを追記してください。
objSlection.Columns("A:D").AutoFit
objSelection.Range("A1:D1").Interior.Color = RGB(200, 200, 200)
ここで、「Columns（範囲）.AutoFit」は指定した範囲の列幅を自動調整する処理を行います。
次に「objSelection.Range(範囲).Interior.Color = RGB(200, 200, 200)」で、指定した範囲のセル背景色をRGBで指定した色に設定します。なお、「RGB(200, 200, 200）」は淡い灰色となります。
Excelファイルの保存及び終了処理
様式を整形したExcelファイルは、上書き保存をし、Excelファイルは閉じます。設定した変数に対しては、メモリーを開放するための処理をします。
'Excelを上書き保存して閉じます。 objExcel.ActiveWorkbook.Save objExcel.Workbooks.Close objExcel.Quit 'エクセルオブジェクトの破棄（ガーベージコレクション） Set objSelection = Nothing Set objExcel = Nothing
以上の処理で、Excelの整形が完了しました。
Excelファイルエクスポート処理の最終形
前回と今回の記事で、在庫情報のExcel処理について紹介をしてきました。これまでのソースコードを一連で示します。
Private Sub cmd_ファイル出力_Click()
If MsgBox("在庫データを出力しますか？", vbYesNo) = vbNo Then
MsgBox "処理を中止します。"
Exit Sub
End If
'■保存先指定
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
'■ファイル名の設定
Dim newFile As Double
Dim strPath As String '作成するファイル名YYMMDD連番ファイル名(文字列)
Dim srchXls As String '作成するフルパス
newFile = Val(Format(Date, "yyyymmdd") & Format(0, "00")) 'newFileをYYYYMMDD00とする
Do '後判定のDo Loop文
newFile = newFile + 1 'newFileの00部分に＋1
strPath = Right(newFile, 8) & "_在庫一覧.xlsx" 'YYMMDD00_ファイル名+拡張子
srchXls = FileSelect & "\" & strPath 'サーバー上にファイルを保存
'Dir検索で 指定したファイル名があればループから抜ける
Loop Until strPath <> Dir(srchXls, vbNormal)
'■ファイルの出力
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Q_在庫出力用", srchXls, True, "在
庫一覧"
'■Excelレイアウト処理
Dim objExcel
Dim objSelection
'エクセルオブジェクトを作成します。
Set objExcel = CreateObject("Excel.Application")
'エクセル画面を表示します。
objExcel.Visible = True
'ワークブックを開きます。
objExcel.Workbooks.Open srchXls
'一つ目のワークシートをobjSelectionにセットします。
Set objSelection = objExcel.Workbooks(1).Worksheets(1)
'列幅の調整、タイトル行の背景色設定、タイトル行下部の形成設定をします。
objSelection.Columns("A:D").AutoFit
objSelection.Range("A1:D1").Interior.Color = RGB(200, 200, 200)
'Excelを上書き保存して閉じます。
objExcel.ActiveWorkbook.Save
objExcel.Workbooks.Close
objExcel.Quit
'エクセルオブジェクトの破棄
Set objSelection = Nothing
Set objExcel = Nothing
If MsgBox("作成したファイルを開きますか?", vbYesNo) = vbYes Then
'Shellで作成したExcelを起動
Shell "Excel.exe " & Chr(&H22) & srchXls & Chr(&H22), vbNormalFocus
End If
End Sub
以上のソースコードでExcelファイルのエクスポート処理ができるようになりました。
まとめ
今回はAccessからExcelファイルの操作方法について紹介をしました。MS-Officeの連携により、各ソフトウェア間でのVBA処理をスムーズに実装することができます。業務効率を改善するにあたり、慣れしたんだソフトウェアを用いて、より効率的な業務を実行できるようにVBAの適用を視野に入れていただければと思います。
これまで12回にわたり、在庫管理向けのデータベースを作成する方法について紹介してきました。基本的な運用の紹介でしたが、応用をしていくことでより汎用性の高いシステムを構築できるようになると思います。
今後は、Accessでデータベースを作成する際に生じる課題や、疑問点を中心に紹介をしていきたいと思います。
スポンサーリンク
コメント