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

スポンサーリンク

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

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

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

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

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

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

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

スポンサーリンク

元データ

使用される際は、そちらの環境で十分テストのうえ使用してください。

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

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

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

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

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

購入日単価枚数購入金額
2022/02/01¥941¥94
2022/02/02¥842¥168
2022/02/03¥843¥252
2022/02/04¥8414¥1,176
2022/02/05¥8412¥1,008
2022/02/06¥846¥504
2022/02/07¥8416¥1,344
2022/02/08¥841¥84
2022/02/09¥1001¥100
2022/02/10¥8410¥840
2022/02/11¥8412¥1,008
2022/02/12¥843¥252
2022/02/13¥3206¥1,920
2022/02/14¥844¥336
2022/02/15¥843¥252
2022/02/16¥945¥470
2022/02/17¥848¥672
2022/02/18¥841¥84
2022/02/19¥3209¥2,880
2022/02/20¥845¥420
2022/02/21¥844¥336
2022/02/22¥10011¥1,100
2022/02/23¥841¥84
2022/02/24¥846¥504
2022/02/25¥152¥52
2022/02/26¥8422¥1,848
2022/02/27¥843¥252
2022/02/28¥844¥336
スポンサーリンク

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

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

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

クエリデザインを選択します

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

クエリの表示をSQL表示にします

SQLビューを選択。

クエリに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桁が表示されます。

アメリカの通貨はドルですが、ドルより小さい単位にセントがあります。

1ドル23セントという金額は、$1.23と表しますので、小数点以下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

コメントをどうぞ!

タイトルとURLをコピーしました