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

Excelマクロ入門

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

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

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

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

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

今週郵送する予定です。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

GOforIT 24

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

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

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

GOforIT 18

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

GOforIT 14

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

GOforIT 12

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

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

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

GOforIT 25

架空の情報です。

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

セルフマガジンの宛名

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

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

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

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

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

GOforIT 27 GOforIT 26

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

GOforIT

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

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

GOforIT

架空の情報です。

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

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

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

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

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

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

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

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

GOforIT 26

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

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

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

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

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

それを

のところで書いています。

ただ、何らかの理由で同じ処理を2回やると、シート「send-data」にデータがダブってしまう可能性があるので注意です。

次回の連携に備えてラベルをつくった連携データを削除する

ラベルの作成が終わったデータについては、当日の日付を入力してシート「send-data」にコピーされます。

そして、役目を終えたシート「data」は削除しておきます。

削除しないままに保存して閉じると、次回ファイルを開いたときにシート「data」が残ったままになります。

削除しておけば、次回に使うときにも、まずはGoogleスプレッドシートのデータ読み込みから始めるという同じ流れにすることができます。

A4サイズの用紙に1/4でプリント

プリントはPDFファイルを開いて1/4サイズでプリントすると、ちょうどいいサイズになります。
IMG 9110

Macプレビューなら印刷のレイアウトを選んで、ページ数/枚を「4」に。
GOforIT

Adobe Acrobat Readerで印刷するときは、複数を選び、2×2にしましょう。
GOforIT 31

A4サイズの用紙に1/4でプリント

郵送は角六というA5サイズ用の封筒をAmazonで買いました。

ラベルを貼るときには、ドットライナーを使っています。ラクなので。

メルカリだと交換テープ、安く売っています。

 

 

ということで、GoogleスプレッドシートからExcelへのデータ連携、マクロを使ったラベルのプリントまでをやってみました。

繰り返しやることこと、苦手なことなら手間を減らすしくみを整えておきたいものです。

ここだけの話、このしくみをつくるのに4時間位かかりました。お手本があるんですけどね。

でも、次からはラクになるだろうということで。

データ連携などもっといいやり方があれば、見直しするつもりです。

あと、セルフマガジンについては、「税理士が請求してはいけないかなと思って…」という声をいただきましたが、セルフマガジンにご興味がもっていただける方なら大丈夫です。

(税理士や社労士の方にもご請求いただいています。)

ひとりで独立される方(されている方)に向けた本ではありますが、ご興味があれば。今月分は21日頃に発送する予定です。

セルフマガジン『GO for IT 』無料送付 


【編集後記】
昨日はオフ。長男(6)の学校の宿題、作文をいっしょに考えたり、ゲームをやったり。長女(12)の送り迎え、ゼルダも一緒に進めました。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
長女(12)の塾への送り迎え