2017年8月25日、ブラッド・ジョーンズによって更新されました。

仕事中、microsoftexcelからWordへ、あるいはWordからmicrosoftexcelへ、コピー＆ペーストをする機会が多いのではないでしょうか。

エクセルは、プロフェッショナルなチャートやグラフの作成から、プロジェクト管理のトラッキングプログラムの構築まで、あらゆることに使える素晴らしいプログラムですが、レポートを書くとなると、ワードの方がずっと良いツールです。

今回は、Excelファイルに保存されているデータと、レポートを作成するWord文書との接続を実際にプログラムするためのバックグラウンドVBAスクリプトを掘り下げます。

microsoftexcelのデータをmicrosoftに統合するのは、正しい参照の追加方法とバックエンドのVBAコードの構文が分かれば簡単です。

スプレッドシートの設定

この例では、かなり単純なExcelのスプレッドシートから始めることにします。実際、1つのExcelファイルが大量のデータを含む複数のスプレッドシートで構成されていても、それは問題ではありません。

スプレッドシートのどこにデータがあるかさえわかっていれば、VBAを使ってデータにアクセスしたり、データを取得したりすることができます。

ここに私の例のスプレッドシートがありますが、これは1年間に計算された総費用のリストです。

ワード文書の設定

例えば、あるマネージャーが、経費をプロジェクトのようにグループ化し、より美しいレイアウトで情報を表示する、きれいな書式のレポートを見たいと言っているとします。

テキストボックスやラベルなどのオブジェクトをWord文書に合成することで、このようなことが可能になります。Wordの場合、メニューの「開発者」タブをクリックし、「コントロール」セクションの「デザインパターン」を選択するだけです。従来のツールのドロップダウンアイコンを使って、さまざまな要素**を文書に取り込むことができます。

このメニューを使う**」タブ

ラベルをドキュメントの好きな場所に配置したら（必ずしも簡単な作業ではありません）、次はデータフィードのプログラムを作成します。しかし、その前に、VBAが認識できるようにタグに名前を付ける必要があります。ラベルを右クリックし、「プロパティ」を選択します。(Name)フィールドを探し、覚えやすい名前を付けてください。

次に、同じようにツールのドロップダウンリストからコマンドボタンを追加し、ダブルクリックするとVBAエディタが起動します。後でコードが動作するようになったら、Document Open() イベントでコードが実行されるように修正すればよいのです。エディタウィンドウのオブジェクトドロップダウンボックスに表示されます。

vbaの使用

WordとExcelの連携を始めるには、VBAで後者を参照できるようにする必要があります。

インポート用のエクセルデータを準備する

ツール」をクリックし、「リファレンス」をクリックします。Microsoft Excel 16.0 Object Libraryが表示されるまでリストをスクロールし、選択します。

これさえできれば、あとはExcelのスプレッドシートからデータを抽出し、ラベルの見出しを自動的に更新する極めてシンプルなVBAスクリプトを書くだけである。

コードは以下の通りです。

Private Sub CommandButton1_Click() Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Set exWb = objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expenses.xlsx") ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(12, 2) exWb.Close Set exWb = Nothing End Sub

ほらね。exWb」Excelアプリケーション・オブジェクトは、指定したパスにあるmicrosoftexcelファイルを開き、そのまま特定のワークシートとセル番号に移動してデータを抽出し、totalu経費と名付けたタブのCaptionプロパティに格納します。スクリプトで編集する必要があるのは、ファイルパスとタブ名だけです。

テスト用マクロ

コマンドボタンをテストするには、Wordのマクロを有効にする必要があるドキュメントを指定してドキュメントを保存します。

VBAのマクロでできることは、こんなことです。

エクセルタグを統合

Wordでのタグの扱いで最も難しいのは、文末などにタグを揃えるのが難しい場合があることです。

この問題を解決する一つの方法は、VBAのコード自体に、データの横に何らかのテキストを入れることです。このように、ラベル自体を作成する際に、ヘッダーに静的テキストを配置しました。

あとは、VBAスクリプトでラベルを更新するときにテキストを入れ、そのテキストの最後にmicrosoftexcelファイルのデータを追記するだけです。

そのようなコードは、こんな感じです。

Dim objExcel As New Excel.Application Dim exWb As Excel.Workbook Set exWb = objExcel.Workbooks.Open("c:\Users\Brad\Desktop\expenses.xlsa") ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(12, 2) ThisDocument.total_hotels.Caption = "Hotels: " & exWb.Sheets("Sheet1").Cells(5, 2) ThisDocument.total_dining.Caption = "Dining Out: " & exWb.Sheets("Sheet1").Cells(2, 2) ThisDocument.total_tolls.Caption = "Tolls: " & exWb.Sheets("Sheet1").Cells(3, 2) ThisDocument.total_fuel.Caption = "Fuel: " & exWb.Sheets("Sheet1").Cells(10, 2) exWb.Close Set exWb = Nothing

文字列連結の「&」マークは、静的なテキストとExcelのワークシートから抽出したデータを連結するために使用します。以下は、更新されたWord文書での最終結果です。

さらに進む

実力を試したいなら、さらにレポートを自動化してはどうだろう。データ更新スクリプトを実行するだけで、この醜い灰色のコマンドボタン文書をマイクロソフト・ワード文書から削除することができます。開く() - すべての処理は裏で行われます。

つまり、多くの場合、最初の文書を一度作成すれば、その後は二度と作成する必要がないのです。これを開くだけで、すべてのタブが自動的に更新されたMicrosoft Excelファイルからデータを取得します。印刷」をクリックして、上司に報告書を提出するだけです。30分の仕事が1分の印刷に！？

VBAを使ったこのデータ統合のテクニックで、他に良い使い方が思いつきますか？

画像引用元：Punyaphat Larpsomboon via Shutterstock.com ウェブサイト