Excel2000でレーダーチャートの自動作成・Wordへの自動挿入は可能か

質問

Excel2000、Word2000を使っています。
Excelで１行が１名のデータとなっています。列になっている10項目に対してレーダーチャートを作り、Wordに貼り付け、１人ごとに結果票を配りたいと思っています。
ただ、人数がおおいため、一人分ずつ（1行ずつ）レーダーチャートをつくり、貼り付けするのは避けたいと思っています。

Wordにテンプレートを作り、数値については差込しています。
レーダーチャートについてはリンクをかけるなど、差込印刷と同じようなニュアンスで作成することは出来ないのでしょうか。

回答No.6

TTak · ベストアンサー

具体的になってきました。
Excelファイルのデータベース範囲はA～Wの23列で、1行目がヘッダ、2行目以下が1行ごとに1名のデータ、そして、その中のL～Uの10列がグラフチャートの範囲ということですね。
Word側に13列のテーブルがあるとします。行数は2で1行目はヘッダとし、Excelデータベースのヘッダとあわせます（A～K,V,W列）。
マクロは、A～K列がループでもいいのですが、Word側の12,13列目とExcel側のV,W列を対応させなければなりませんので、この部分はループからはずして、個別に記入します。また、Wordでのグラフの表示位置はShapesオブジェクトのTop,leftプロパティで操作します。
総合的に、次のようなマクロ例になります。

Sub Macro1()
Dim id As Integer
Set wkbObj = CreateObject("C:\My Documents\Book1.xls")
Set MySheet = wkbObj.Worksheets("Sheet1")
Set MyGpObj = MySheet.ChartObjects(1).Chart.SeriesCollection(1)
'On Error GoTo ERRHAND

With ActiveDocument
　　id = .ScrollBar1.Value
　　Application.ScreenUpdating = False
　　For x = 1 To 11
　　　.Tables(1).Cell(2, x).Select
　　　　Selection.TypeText Text:=MySheet.Cells(id, x).Value
　　Next x
　　.Tables(1).Cell(2, 12).Select
　　　Selection.TypeText Text:=MySheet.Cells(id, 22).Value
　　.Tables(1).Cell(2, 13).Select
　　　Selection.TypeText Text:=MySheet.Cells(id, 23).Value
　　.Shapes(2).Delete
MyGpObj.Values = "=Sheet1!R" & id & "C12:R" & id & "C21"
MySheet.ChartObjects(1).Copy
Selection.Paste
Selection.ShapeRange.LockAnchor = False
Selection.ShapeRange.WrapFormat.AllowOverlap = True
　　With .Shapes(2)
　　　　.WrapFormat.Side = wdWrapBoth
　　　　.WrapFormat.Type = 3
　　　　.ZOrder 5
　　　　.Top = 50
　　　　.Left = -180
　　End With
Application.ScreenUpdating = True
Selection.MoveDown Unit:=wdLine, Count:=1
ERRHAND:
Set wkbObj = Nothing
Set MySheet = Nothing
Set MyGpObj = Nothing
End Sub

回答No.5

TTak

追加質問についての回答です。

>（１）今回の元データは１行に氏名・年齢・受験日・総合結果・各項目結果１・２～・・・・
薄々お判りとは思いますが、For～Nextの繰り返し回数が、列数に相当してます。ですから、たとえば、Excelの1列目から14列目ということであれば、
For x = 1 To 14
ということになります。グラフ範囲を変更する場合は、
MyGpObj.Values = "=Sheet1!R" & id & "C2:R" & id & "C11"
で、C2は2列目（すなわちB列）、C11が11列目（すなわちK列）に相当するので、その数値を変えます。4列目から14列目までということであれば、C4,C14ですね。
ちなみに
MyGpObj.XValues = "=Sheet1!R" & id & "C1"
は、系列名を指定しているだけです。グラフに項目軸ラベルが無い場合は省略してかまいません。

> （２）Mucrosoft Officeで使用するマクロについて、どのような方法で
>　　　習得されたのですか？これまで通常できる複数の操作をマクロに記録する
>　　　位しか行っていなかったもので・・。もしよければ教えてください。

私も最初はマクロの記録からスタートしました。そして、キーワードをいちいち調べているうちに、自動記録のマクロは効率が悪い（回りくどい記述である）ことがわかりました。そこで、試行錯誤してマクロをスリムにする訓練をしました。このとき参考書は使わず、とにかくヘルプをよく読んでいました。そのうちだんだんと自分でマクロが組めるようになってきました。今でも参考書は殆ど使いません。いつも”ヘルプ”です。どーしてもわからない時は、karimen～氏がここで質問されているように、私も、しかるべき所で質問していますよ。

補足

早速のお返事、ありがとうございました。

オブジェクト形式を変更することでグラフの表示内容も
変更されるようになりました。

が
●スクロールバーを動かすとグラフの配置位置が
　Word上で変わってしまう（とんでもなく遠くの位置に作られてしまう）
●グラフにしているのはL列～U列なのですがレーダーチャート上の
　L列の値の上にA列の値がなぜか表示されてしまう。
●グラフのデータラベル（テーブル上の項目名）が表示されない。
　（データの値は表示される）
という現象が起こっています。

＜最終的に作りたいもの＞
（材料）
＊元データExcel
１行目は項目名（ID、姓、名、総合評価、A項目、B項目など）で全23列。
２行目以降１行につき１名のデータ
Wordに値として表示したいのは本当はA列～K列11列とV～W列2列の計13列。
残りL列～U列の10項目についてグラフ表示する。
ただ、リンクする関係上、L列～U列についてもテーブルとしてWord上で
値表示をしなくてはいけないのであればやむをえないと考える。
その場合、Wordに貼り付けるテーブルは23列で２～３行にわたる。

よってFor x = 1 To 23とし
MyGpObj.Values = "=Sheet1!R" & id & "C12:R" & id & "C21"
としました。

Q：Wordに貼り付けるテーブルを２～３行にすると、スクロールバーを
　動かしても１行目しか変わりません。
　それは仕方ないのでしょうか。

回答No.4

TTak

わかりました。オブジェクト形式が違ったようです。
マクロは元に戻して（回答＃３は破棄して）、＃２の回答の(2)と(3)に追記して順序を入れ替えます。

(2) Wordドキュメント上にコントロールツールボックスから、スクロールバーを１個配置してください。配置したスクロールバーを右クリックして、[オブジェクトの書式設定]＞[レイアウト（タブ）]＞[前面]＞[OK]とします。

(3) Wordドキュメント上に、2行10列のテーブル１個と、1)で作ったExcelグラフチャートを1個コピー＆ペーストしておきます。ペーストしたグラフチャートを右クリックして、[オブジェクトの書式設定]＞[レイアウト（タブ）]＞[前面]＞[OK]とします。デザインモードを終了します。

要は、Wordドキュメント上で、一度オブジェクト書式を設定し直すことで、Shapeオブジェクトとして認識するようになるみたいです。
これでだめだったら、私がテストで成功したサンプルをwebサーバに入れて、ダウンロードできるようにします。

回答No.3

TTak

> ●質問１
試しに、Macro1の5行目の
On Error GoTo ERRHAND
を削除して実行してみて、
「実行時エラー・・・・指定したコレクションに対するインデックスが・・・・」
とかいうメッセージが出たら、For～Nxtループの下の
ActiveDocument.Shapes(2).Delete
を
ActiveDocument.Shapes(1).Delete
に変えてみてください。他のエラーが表示されたら、お知らせください。
ちなみにグラフ範囲の絶対参照はマクロに関係なく変わるようです。

> ●質問２
間違いありません。なぜか文字コードに化けてしまったようです。

補足

お返事、ありがとうございます。

●「試しに、Macro1の5行目の On Error GoTo ERRHAND を削除して実行」したら
「指定されたコレクションのメンバは存在しません。実行時エラー5941」
デバックボタンを押下したところFor x～の次行のActiveDocument.Tables(1)～
が黄色で指定されていました。

●エラー内容は違いましたが
「For～Nxtループの下の ActiveDocument.Shapes(2).Delete を
ActiveDocument.Shapes(1).Delete に変えてみてください。」も実行してみましいたが上記と同様のエラーでした。

２つ質問です。
（１）今回の元データは１行に氏名・年齢・受験日・総合結果・各項目結果１・２～（10項目）と並んでいるものです。１行がひとり分となっています。
結果票には氏名・年齢・受験日・総合結果・各項目結果１～１０の値表示と
各項目結果についてのみグラフ表示したいと思っています。
（項目名を入れて２行14列）

Wordドキュメント上に2行14列のテーブルを貼り付け、この値をスクロールバーの
上下により変更　且つ　グラフは後半10列のみ参照し、変更
ということは出来ないでしょうか。

（２）Mucrosoft Officeで使用するマクロについて、どのような方法で
　　　習得されたのですか？これまで通常できる複数の操作をマクロに記録する
　　　位しか行っていなかったもので・・。もしよければ教えてください。

よろしくお願いいたします。

回答No.2

TTak

ではなるべく詳しく書きますので、参考になさってください。

(1) Sheet1に10列・行数任意のデータ（データ範囲"B2:C?" ?は任意行数とします）と、そのデータの任意の1行がグラフ範囲になっているレーダグラフチャートが1個あるファイルを作り、
C:\My Documents\Book1.xlsに保存します。
(2) Wordドキュメント上には、2行10列のテーブル１個と、1)で作ったExcelグラフチャートを1個コピー＆ペーストしておきます。
(3) Wordドキュメント上にコントロールツールボックスから、スクロールバーを１個配置してください。
(4) WordのVBEを起動し、[ツール]＞[参照設定]でExcelのオブジェクトライブラリの参照設定にチェックを入れます（Excel2000の場合は”Microsoft Excel 9.0 Object Library”）。
(5) Word-VBEのプロジェクトエクスプローラで、Project(ファイル名)を右クリックして、[挿入]＞[標準モジュール]を選択します。
(6) 同プロジェクトエクスプローラで、Project(ファイル名)àMicrosoft Word ObjectsàThisDocumentをダブルクリックして、コードウインドウを開き、次の２つのイベントプロシージャを転記します。

Private Sub Document_Open()
　Call ScrollBarSet
End Sub

Private Sub ScrollBar1_Change()
　ActiveDocument.Activate
　Call Macro1
End Sub

(7) 同プロジェクトエクスプローラで、Project(ファイル名) à標準モジュールàModule1をダブルクリックして、コードウインドウを開き、次の２つのプロシージャを転記します。

Sub ScrollBarSet()
Set wkbObj = CreateObject("C:\My Documents\Book1.xls")
Set MySheet = wkbObj.Worksheets("Sheet1")

ActiveDocument.ScrollBar1.Min = 2
ActiveDocument.ScrollBar1.Max = _
　　MySheet.Range(MySheet.Cells(MySheet.Rows.Count, 1) _
　　　.End(xlUp).Address).Row

Set wkbObj = Nothing
Set MySheet = Nothing
End Sub

Sub Macro1()
Dim id As Integer
Set wkbObj = CreateObject("C:\My Documents\Book1.xls")
Set MySheet = wkbObj.Worksheets("Sheet1")
Set MyGpObj = MySheet.ChartObjects(1).Chart.SeriesCollection(1)
On Error GoTo ERRHAND

id = ActiveDocument.ScrollBar1.Value
Application.ScreenUpdating = False
For x = 1 To 11
　　ActiveDocument.Tables(1).Cell(2, x).Select
　　Selection.TypeText Text:=MySheet.Cells(id, x).Value
Next x
ActiveDocument.Shapes(2).Delete
MyGpObj.Values = "=Sheet1!R" & id & "C2:R" & id & "C11"
MyGpObj.XValues = "=Sheet1!R" & id & "C1"
MySheet.ChartObjects(1).Copy
Selection.Paste
Selection.ShapeRange.WrapFormat.Type = 3
Selection.ShapeRange.ZOrder 5
Selection.ShapeRange.IncrementTop 200
Application.ScreenUpdating = True

ERRHAND:
Set wkbObj = Nothing
Set MySheet = Nothing
Set MyGpObj = Nothing

End Sub

(8) Word-VBEを閉じます。Excelも閉じてかまいません。
(9) Wordドキュメント上のスクロールバーを動かしたり、up,downボタンを押すたびに、テーブルのデータと、グラフチャートが変化します。なお、グラフの編集はExcel側で行ってください。

お礼

OSはMe。Office2000で行いました。
結論としてはスクロールバーを動かすことでExcelからWordに貼り付けた２行１０列のテーブルの値はBook1.xlsの次行データ値、次々行データ値と変更されますが、レーダーチャートは変更されません。

●質問１
Book1のSheet1で作るレーダーチャートのデータ範囲ですが
手入力で=Sheet1!A1:J2としても後から確認すると絶対参照に
なってしまっています。（=Sheet1!$A$1:$J$2)
このせいでしょうか？
またこれはどのようにすればよいのでしょう？

●質問２
（６）のThisDocumentですが
Project(ファイル名）以下のMiceosoft以下のThisDocumentと
認識し、編集しましたがよいですよね？
（à～はなし）
（７）についても
Project(ファイル名）以下の標準モジュール以下のModule1
を編集しました。

よろしくお願いします。

回答No.1

TTak

Wordマクロを使ってExcelを操作するという方法があります（上級者向けです）。
Word VBEで、Excelのオブジェクトライブラリを参照させることで、Excelの操作ができます。Wordドキュメント上のデータをグラフに反映させる方法が見あたらないので、レコードが更新されるごとに元のExcelシート上でグラフを再作成し、それをWord上に貼り付けます。差込み機能を使ってExcelデータをWordに差し込んでもいいのですが、Excel側でグラフの参照範囲を変更させるので、Word側へのデータ差し込みもマクロを使います。まずはヒントまで、この方法を試したい場合はお知らせください。
当方はWIN2000+EXCEL2000+WORD2000で動作確認しました。

お礼

アドバイス、ありがとうございます。
おっしゃる方法を試してみたいと思うのですが教えていただけないでしょうか。