アクセスのVBAでエクスポートしたエクセルのセルに書式設定する

アクセスのテーブルをエクセルにエクスポートすることは、ちょくちょくあると思います。

ですが、エクスポートしたエクセルを開いてみると、書式設定が思ったものと違う場合がありませんか？

マイクロソフトオフィスって、基本的にアメリカで使われる書式をサポートしています。

日本で使うようにカスタマイズされているとはいえ、エクスポートしたときの書式設定といった細かいところでは日本ではなじみのない書式が使われていて、いちいち設定しなおしていませんか。

例えば、日付の形式は和歴で出力してほしいとか、西暦で良いけど年／月／日の形にしたいとか、金額の列は￥マークを付けてほしいとか。

エクスポートしたエクセルをいちいち開いて手入力で設定するのは、時間がかかりますよね。

今日は、エクスポートしたエクセルのセルの書式設定を、アクセスから行う方法を紹介します。

元データ

今回の元データは、切手の購入記録にしてみました。

テーブル「購入記録」を作成しました。フィールドの設定は次の通りです。

テーブルの各フィールドの書式設定は、初期値のままです。

購入日	単価	枚数	購入金額
日付/時刻型	通貨型	数値型	通貨型
切手を購入した日	切手の単価	購入した枚数	購入した金額（単価×枚数）

テーブルに入力したデータは、こちらです。

購入日	単価	枚数	購入金額
2022/02/01	¥94	1	¥94
2022/02/02	¥84	2	¥168
2022/02/03	¥84	3	¥252
2022/02/04	¥84	14	¥1,176
2022/02/05	¥84	12	¥1,008
2022/02/06	¥84	6	¥504
2022/02/07	¥84	16	¥1,344
2022/02/08	¥84	1	¥84
2022/02/09	¥100	1	¥100
2022/02/10	¥84	10	¥840
2022/02/11	¥84	12	¥1,008
2022/02/12	¥84	3	¥252
2022/02/13	¥320	6	¥1,920
2022/02/14	¥84	4	¥336
2022/02/15	¥84	3	¥252
2022/02/16	¥94	5	¥470
2022/02/17	¥84	8	¥672
2022/02/18	¥84	1	¥84
2022/02/19	¥320	9	¥2,880
2022/02/20	¥84	5	¥420
2022/02/21	¥84	4	¥336
2022/02/22	¥100	11	¥1,100
2022/02/23	¥84	1	¥84
2022/02/24	¥84	6	¥504
2022/02/25	¥1	52	¥52
2022/02/26	¥84	22	¥1,848
2022/02/27	¥84	3	¥252
2022/02/28	¥84	4	¥336

エクセルへエクスポートする

出力元になるクエリを作る

早速、「購入記録」テーブルをエクセルにエクスポートしたいところですが、後々、期間を指定したり並び替えを指定したりするようになるかもしれませんので、一旦クエリをかませましょう。

リボンから「作成」－「クエリデザイン」を選択し、

SQLビューを選択。

次のSQL文を貼り付けてください。

SELECT 購入記録.*
FROM 購入記録
ORDER BY 購入記録.購入日, 購入記録.単価;

このクエリは、購入日、単価順に並ぶように設定しています。

貼り付けができたら、このクエリに「出力元」という名前を付けて、保存してください。

「出力元」クエリをエクセルにエクスポートする

続いて、標準モジュールを作成し、

Option Compare Database
Option Explicitの下に、次のVBAコードを記入しましょう。

Sub Excel_Formatting()

Dim strTgTFldNM As String '出力先のフォルダ
Dim strTgTFleNM As String '出力先のファイル名

'出力先のフォルダは、アクセスがあるフォルダを指定
strTgTFldNM = Application.CurrentProject.Path

'出力先ファイル名は、「購入履歴」を指定
strTgTFleNM = "購入履歴"

'エクセルにエクスポート
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "出力元", strTgTFldNM & "\" & strTgTFleNM

End Sub

このコードを実行すると、このようにアクセスを置いてあるフォルダ内に、エクセルファイルが出力されます。

では、このエクセルファイルを開けてみましょう。

和休のパソコンの環境では、日付は年／月／日形式、単価と購入金額は小数点以下2桁が表示されています。

アクセスのテーブルで、単価と購入金額は通貨型を使用しているため、アメリカで使い勝手の良い小数点以下2桁が表示されます。

アクセスからエクセルに書式設定する

では、エクスポートしたエクセルファイルの書式設定を、アクセスから設定してみたいと思います。

日付は和歴、単価と購入金額は\表示かつカンマ区切りとします。

Sub Excel_Formatting()

Dim strTgTFldNM As String '出力先のフォルダ
Dim strTgTFleNM As String '出力先のファイル名

Dim xlapp As Object 'エクセルオブジェクト

'エクセルの準備
Set xlapp = CreateObject("excel.application")

'出力先のフォルダは、アクセスがあるフォルダを指定
strTgTFldNM = Application.CurrentProject.Path

'出力先ファイル名は、「購入履歴」を指定
strTgTFleNM = "購入履歴.xlsx"

'エクセルにエクスポート
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "出力元", strTgTFldNM & "\" & strTgTFleNM

With xlapp

    '編集するエクセルを開く
    .workbooks.Open strTgTFldNM & "\" & strTgTFleNM
    
    'A列に書式を設定する
    .Range("A:A").NumberFormatLocal = "gggee""年""mm""月""dd""日"""
    
    'B列、D列に書式を設定する
    .Range("B:B,D:D").Style = "Currency [0]"
    
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

End Sub

Dim xlapp As Object 'エクセルオブジェクト

'エクセルの準備
Set xlapp = CreateObject("excel.application")

6～9行目にエクセルを扱うためのコードを追加しました。

With xlapp

    '編集するエクセルを開く
    .workbooks.Open strTgTFldNM & "\" & strTgTFleNM
    
    'A列に書式を設定する
    .Range("A:A").NumberFormatLocal = "gggee""年""mm""月""dd""日"""
    
    'B列、D列に書式を設定する
    .Range("B:B,D:D").Style = "Currency [0]"
    
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

20～39行目がエクスポートしたエクセルを開き、書式設定を施して上書きし、エクセルを閉じる処理です。このエクセルを開けてみると、

このように書式設定ができていますね。成功です。

(´▽｀)b

次回の記事では、アクセス側からエクセルのページ設定を行います。

アクセスのVBAでエクセルへのエクスポートと同時にエクセルのページ設定をする

アクセスからエクセルのページ設定を行います。前回、書式設定を施しましたが、VBAを使用することでページ設定もできてしまいます。

waq3-travelog.com

2022.10.11