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

スポンサーリンク
2022.10.11
スポンサーリンク

設定できるのは書式設定だけではありません

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

アクセスのVBAでエクスポートしたエクセルのセルに書式設定する
アクセスからエクセルへエクスポート。エクセルを開いて、書式設定していませんか？繰り返す作業ならVBAで自動化してみませんか？
waq3-travelog.com
2022.10.09

前回、エクスポートしてから書式設定を行う方法を紹介しました。

ちなみに、設定できるのは書式設定だけではありません。

ページ設定もできてしまいます。

早速、フッターの中央にページ番号を表示させてみましょう。

ページ番号を表示させる

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]"

    With .activesheet.pagesetup
    
        'フッター中央にページ番号
        .centerfooter = "&P ページ"
    
    End With
        
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

End Sub

31～36行目にページを表示させる設定を追加しました。

このコードを実行して出来上がったエクセルを印刷プレビューしてみましょう。

エクセルのフッター中央にページ番号を表示

フッター中央にページ番号が印刷されていることが分かります。

エクセルのファイル名を表示する

続いて、エクセルのファイル名をフッターの左側に表示したいと思います。

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]"

    With .activesheet.pagesetup
    
        'フッター左にファイル名
        .leftfooter = "&F"
    
        'フッター中央にページ番号
        .centerfooter = "&P ページ"
        
    End With
        
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

End Sub

33～34行目にファイル名を表示させる設定を追加しました。

このコードを実行して出来上がったエクセルを印刷プレビューしてみましょう。

エクセルのフッター左側にファイル名を表示

フッター左側にファイル名が印刷されていることが分かります。

ヘッダーに日付を表示する

さらに、ヘッダー右側に今日の日付を入れたいと思います。

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]"

    With .activesheet.pagesetup
    
        'ヘッダー右に日付
        .RightHeader = "&D"
    
        'フッター左にファイル名
        .leftfooter = "&F"
    
        'フッター中央にページ番号
        .centerfooter = "&P ページ"
        
    End With
        
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

End Sub

33～34行目に日付を表示させる設定を追加しました。

このコードを実行して出来上がったエクセルを印刷プレビューしてみましょう。

エクセルのヘッダー右側に日付を表示

ヘッダー右側に日付が印刷されていることが分かります。

ところで、ヘッダーの日付の書式は、年／月／日形式から変更することはできません。

でも和歴で表示したいときとか、曜日まで入れたいときもあるでしょう。

ついでに紹介しますね。

ヘッダーに和歴日付を表示する

エクセルのヘッダー右側に日付を和歴で表示
'ヘッダー右に日付
.RightHeader = Format(Date, "gggee年mm月dd日")

和歴で表示するためには、34行目をこのように変更してください。本日の日付が和歴で表示されます。

ヘッダーに曜日まで入れて日付を表示する

エクセルのヘッダー右側に日付と曜日を表示
'ヘッダー右に日付
.RightHeader = Format(Date, "gggee年mm月dd日aaaa")

曜日を表示するためには、34行目をこのように変更してください。

ヘッダー中央にシート名を表示する

最後に、ヘッダー中央にシート名を表示させましょう。

同時に、この方法でエクスポートするとシート名がクエリの名前になってしまうので、シート名を「切手購入履歴」へ変更したいと思います。

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]"
    
    'シート名を変更する
    .sheets(1).Name = "切手購入履歴"

    With .activesheet.pagesetup
    
        'ヘッダー右に日付
        .RightHeader = "&D"
        
        'ヘッダー中央にシート名
        .CenterHeader = "&A"
    
        'フッター左にファイル名
        .leftfooter = "&F"
    
        'フッター中央にページ番号
        .centerfooter = "&P ページ"
        
    End With
        
    'エクセルを上書き保存
    .activeworkbook.Save
    
    'エクセルを閉じる
    .activeworkbook.Close

End With

Set xlapp = Nothing

End Sub

30行目に、シート名の変更を追加しました。これを実行すると、シート名が切手購入履歴に変わります。

38行目に、ヘッダー中央にシート名を表示させるよう追加しました。

このコードを実行して出来上がったエクセルを印刷プレビューしてみましょう。

エクセルのヘッダー中央にシート名を表示

成功です。

(´▽｀)b

コメントをどうぞ！

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