Googleスプレッドシート→Excel連携。Excelマクロでセルフマガジンを郵送する宛名ラベルをつくってみた。

2021年7月18日2021年7月20日

URLをコピーしました！

セルフマガジンの郵送をするしくみをつくってみました。

データがあればしくみをつくりやすいです。

セルフマガジンの宛名印刷

先日から無料配布の受付をしているセルフマガジン。

じぶんの考え方・仕事を知ってもらうセルフマガジン（小冊子）。Adobe InDesignでつくってみた。 | GO for IT 〜税理士植村豪 Official Blog

今週郵送する予定です。

たいていお客様とやりとりするのはメールやチャット。郵送はほとんどしないのですが、冊子をつくると決めたことで郵送手続きをやることになります。

現在は税務署にWebゆうびんで郵送することもなくなりました。

封筒に入れて、切手を貼って郵送する、数があればそれなりの手間です。

やはり、ネックになるのは宛名です。それぞれに郵送先が違うわけですので。

しくみ化しようと思ったら、やはりその情報をデータでいただくことが必須です。

今回は、セルフマガジンの申込みフォームに入力いただいた郵送先の情報をGoogleスプレッドシートに連携するようにしました。

Contact Form 7とGoogleスプレッドシートを連携して郵送先住所をシートにまとめる方法。（郵便番号自動検索付き） | GO for IT 〜税理士植村豪 Official Blog

そのデータをどう使うか。

今回はこのようにやりました。

GoogleスプレッドシートのデータをExcelに連携（読み込み）
Excelマクロで人数分の宛名ファイル（PDF）作成
プリントした宛名を封筒に貼り付ける

Googleスプレッドシート→Excelへの連携

GoogleスプレッドシートとExcelのデータを連携させることができます。

自動連携を試みましたが、こんな感じで意味不明な列ができてしまうなどうまくいかないこともあり、

今回はダウンロードしたGoogleスプレッドシートのデータをExcelで読み込んでいます。

GoogleスプレッドシートのデータをExcelに。

Googleスプレッドシートを開き、ファイル→ダウンロード→Microsoft Excelを選びファイルをダウンロードします。

Excelでダウンロードしたファイルを読み込み

次にExcel側でダウンロードしたファイルを読み込みます。

データ→データの取得→ファイルから→ブックから…と選んでいき、

GoogleスプレッドシートからダウンロードしたファイルをExcelで読み込みます。

請求フォームのデータ（架空のデータです）を読み込み、データの変換をクリック。

すると、見出しが1行目のデータになっているので、「1行目をヘッダーとして使用」をクリックすると、

1行目をヘッダーにすることができます。そのまま閉じて読み込みましょう。

すると、Excelの1番左のシートとして読み込みができているはずです。

架空の情報です。

これで宛名の準備は整いました。

セルフマガジンの宛名

先程、読み込んだデータから宛名ラベルをつくります。

それをマクロでやることにし、しくみはこちらの記事を参考にさせてもらい、じぶんなりのマクロを書いてみました。

セルフマガジン178部郵送。Excelマクロ・ドットライナー・アルミ定規で仕事のしくみ作り。 | EX-IT

今回、書いたマクロはこちらです。

Sub selfmagazine()


Option Explicit

Sub selfmagazine()


    Application.DisplayAlerts = False


    '■シート名の変更
    Worksheets(1).Name = "data"


    '■郵送済データの削除
    
    Dim Send_row
    Send_row = ThisWorkbook.Worksheets("send-data").Range("a" & Rows.Count).End(xlUp).Row
    
    If Send_row > 2 Then
    Worksheets("data").Rows("2:" & Send_row).Delete
    End If

    '■新規の郵送件数の確認
    
    Dim max_row
    max_row = Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row
    
    
    '■郵送ラベルの作成
    
    Worksheets("master").Copy
    
    
    Dim i
    For i = 2 To max_row
        If i <> 2 Then
            ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
        End If
        
        '郵便番号
        Range("a2").Value = ThisWorkbook.Worksheets("data").Range("c" & i).Value
        '都道府県
        Range("a3").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value
        '住所
        Range("a4").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value & ThisWorkbook.Worksheets("data").Range("f" & i).Value
        'アパート
        Range("a5").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value
        '氏名
        Range("a6").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value & " 様"
    
    Next
    
    
    '■PDFファイルで出力
    
    Worksheets.Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\selfmagazine.pdf"

    ActiveWorkbook.Close
    
    
    '■郵送済みデータへコピー
        
    Worksheets("data").Range("h2:h" & max_row) = Date
    Worksheets("data").Range("h2:h" & max_row).NumberFormatLocal = "yyyy/m/d"
    
    Worksheets("data").Rows("2:" & max_row).Copy ThisWorkbook.Worksheets("send-data").Range("a" & Send_row + 1)
    
    
    
    '■dataシート削除
    Worksheets("data").Delete
    
    
    '■ファイルを閉じる
    ActiveWorkbook.Close savechanges:=True
    
        

    Application.DisplayAlerts = True



End Sub

Sub selfmagazine()

Option Explicit

Sub selfmagazine()

Application.DisplayAlerts = False

'■シート名の変更

Worksheets(1).Name = "data"

'■郵送済データの削除

Dim Send_row

Send_row = ThisWorkbook.Worksheets("send-data").Range("a" & Rows.Count).End(xlUp).Row

If Send_row > 2 Then

Worksheets("data").Rows("2:" & Send_row).Delete

End If

'■新規の郵送件数の確認

Dim max_row

max_row = Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row

'■郵送ラベルの作成

Worksheets("master").Copy

Dim i

For i = 2 To max_row

If i <> 2 Then

ActiveSheet.Copy after:=Worksheets(Worksheets.Count)

End If

'郵便番号

Range("a2").Value = ThisWorkbook.Worksheets("data").Range("c" & i).Value

'都道府県

Range("a3").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value

'住所

Range("a4").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value & ThisWorkbook.Worksheets("data").Range("f" & i).Value

'アパート

Range("a5").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value

'氏名

Range("a6").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value & " 様"

'■PDFファイルで出力

Worksheets.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\selfmagazine.pdf"

ActiveWorkbook.Close

'■郵送済みデータへコピー

Worksheets("data").Range("h2:h" & max_row) = Date

Worksheets("data").Range("h2:h" & max_row).NumberFormatLocal = "yyyy/m/d"

Worksheets("data").Rows("2:" & max_row).Copy ThisWorkbook.Worksheets("send-data").Range("a" & Send_row + 1)

'■dataシート削除

Worksheets("data").Delete

'■ファイルを閉じる

ActiveWorkbook.Close savechanges:=True

Application.DisplayAlerts = True

End Sub

先程読み込みしたデータがシート「data」にある状態。

シート「master」には、ラベルのフォーマットが、

シート「send-data」には、すでに送ったデータが表示されています。（マクロで前回のラベル作成時にマクロで書いているもの）

マクロを実行すると、シート「data」にあるリストからシート「master」がコピーされて、郵送先分のシートが追加されます。

架空の情報です。

最後にすべてのシートを選択した状態でPDFファイルを出力するという流れです。

Excelマクロ超入門　Excel→PDFファイルを出力するには？ | GO for IT 〜税理士植村豪 Official Blog

今回やったことをいくつか挙げてみます。

シートの名前が変わることがある

Googleスプレッドシートからデータを読み込んだときにシート名が変わることがありえます。読み込み時に設定をし忘れたなど。

シート「data」という名前が変わってしまうと、マクロを動かしたときにエラーになってしまいます。

そのため、最初にどんなシート名で読み込みされたとしても、

 '■シート名の変更
    Worksheets(1).Name = "data"

1 2	'■シート名の変更 Worksheets(1).Name = "data"

として、シート名を「data」に変更するようにしています。そうすればマクロがエラーになることはないので。

Googleスプレッドシートの連携データからすでに送ったデータを削除する

注意しないといけないのが、Googleスプレッドシートからデータを読み込んでいるので、データは累計だということ。

かといって、Googleスプレッドシートのデータを削除してしまうと、累計のデータがとれなくなります。

そこで、Excelにデータを連携したあとで、シート「send-data」にあるすでに郵送したデータの数を数えて、その分だけをシート「data」から行削除しています。

そうすることで、ダブって処理してしまうことはなくなります。

それを

  '■郵送済データの削除
    
    Dim Send_row
    Send_row = ThisWorkbook.Worksheets("send-data").Range("a" & Rows.Count).End(xlUp).Row

    Worksheets("data").Rows("2:" & Send_row).Delete

'■郵送済データの削除

Dim Send_row