あえてエクセルを２つ起動してセルを参照する方法

質問

ご質問させていただきます。
Excel2007を２つ起動します（あえて２つ起動する必要があります）。１つのExcel上に２つのブックを開くのではありません。
１つ目のExcel2007をExcel(1)と呼び、２つ目のExcel2007をExcel(2)と呼ぶことにします。
Excel(1)で開くファイル名は「板とチャート」で、Excel(2)で開くファイル名は「ティック」です。
Excel(1)とExcel(2)は同一のデータベンダーとDDE接続しており、平日の午前９時～午後３時までリアルタイムでデータが更新されます。
それぞれ受信したデータをVBAやワークシート関数で加工して各シートに表示しています。
このDDE接続はデータベンダーが提供するものであり、プロシージャはパスワードでロックがかかっており、私は見る事もいじる事も出来ませし、そもそもVBAに関して初歩的な知識しかないため、DDE接続自体をよく理解しおらず、いじれません。
今回やりたいことを以下に記します。
Excel(2)のファイル名「ティック」ワークシート名「連続」のQ1,Q2,Q3,Q4,Q5,Q6セルのそれぞれの値をExcel(1)のファイル名「板とチャート」ワークシート名「板」のC5,C13,C21,C29,C37,C45に表示させ、同様に、Excel(2)のファイル名「ティック」ワークシート名「連続」のR1,R2,R3,R4,R5,R6セルのそれぞれの値をExcel(1)のファイル名「板とチャート」ワークシート名「板」のI5,I13,I21,I29,I37,I45に表示させたいのです。
なお、Excel(2)のQ1～Q6セル及びR1～R6セルの値はそれぞれIF関数で"A","B"または空欄にリアルタイムで変化します。
従いまして、その"A","B",空欄をExcel(1)のファイル名「板とチャート」ワークシート名「板」のC5,C13,C21,C29,C37,C45セル及びI5,I13,I21,I29,I37,I45セルにリアルタイムに反映させたいのです。
なお、Excel(2)のIF関数の"A","B"または空欄を判定する各セルはcalculateイベントで１秒間に複数回更新されるカウンターになっています。
従いまして、IF関数の"A","B"又は空欄は１秒間の間に変化することもあります。
以上の事を実現いたしたく、以下を試してみました。

Sub できるかな()

Dim xlApp As Application
Set xlApp = GetObject("C:\Users\hoully\Desktop\ティック.xlsm", "Excel.Sheet").Application
Worksheets("板").Range("C5") = xlApp.Worksheets("連続").Range("Q1").Value

End Sub

実行すると、VBAprojectにVBAproject（ティック.xlsm）が追加されるのですが、
「実行時エラー　９
インデックスが有効範囲にありません。」
と表示され、Worksheets("板").Range("C5") = xlApp.Worksheets("連続").Range("Q1").Valueが黄色になります。
Worksheets("板").Range("C5") = xlApp.application.Worksheets("連続").Range("Q1").Valueとやっても同じ事が起こりました。
次に、以下を試してみました。

Sub できるかな2()

worksheets("板").range("C5").formula= "=INDEX([C:\Users\hoully\Desktop\ティック.xlsm]sheet2!Q1:R6,1,1))"

End Sub

「実行時エラー1004
アプリケーション定義またはオブジェクト定義のエラーです」
と表示されてしまいます。
私はExcelもVBAも初心者で、非常に単純なプロシージャを作るにも本やネットを参照しなければ作れないレベルです。
そのため、どこが悪いのかよくわかりません。
どなたか教えていただけないでしょうか？
よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

特段マクロを使わなくても，前回お話ししていたようにDDEの式を記入しておくだけで競合せずにリアルタイムでリンクするはずですが，そちらは具体的にどのような不都合があったのでしょうか？

もう一度同じ内容ですが作成例：
＞Excel(2)のファイル名「ティック」ワークシート名「連続」のQ1,Q2,Q3,Q4,Q5,Q6セルのそれぞれの値をExcel(1)のファイル名「板とチャート」ワークシート名「板」のC5,C13,C21,C29,C37,C45に表示させ

Excel(2)のティック.xlsのシート名「連続」のQ1:Q6に　MyRng1　と名前を定義する
Excel(1)の板のC5に
=INDEX(Excel|ティック.xls!MyRng1,1)
と数式を記入する

＃もちろん，ブック名は拡張子を含めて正確に記載すること

お礼

keithin 様

ご回答ありがとうございます。

>特段マクロを使わなくても，前回お話ししていたようにDDEの式を記入しておくだけで競合せずにリアルタイムでリンクするはずですが，そちらは具体的にどのような不都合があったのでしょうか？

はい。前回は以下のご回答をいただきました。

>たとえば元のブックのA1からA48のセル範囲にTargetと名前を定義しておき，=INDEX(excel|book2.xls!Target,ROW(A1))

これを見て、私はExcelを２つ起動しているのでexcel|book2.xls!の部分にファイルフルパスを記入する必要があるのだと思い込み"=INDEX([C:\Users\hoully\Desktop\ティック.xlsm]sheet2!Q1:R6,1,1))"としたのですが、根本的に間違っていたみたいですね。

今回教えていただいた方法を試してみました。
まず、Excel(2)のティック.xlsmのシート名「連続」のQ1からQ6までをドラッグして右クリックし、「範囲に名前を付ける」を選択し、MyRng1と名前を付けました。
次に下記のプロシージャを実行してブックの名前を取得しました。

Sub ブックの名前()

MsgBox "現在のブック名:" & ActiveWorkbook.Name

End Sub

実行すると「現在のブック名：ティック.xlsm」と表示されたので、
Excel(1)の板のC5に
=INDEX(Excel|ティック.xlsm!MyRng1,1)
と入力いたしました。
すると、C5に#REF!と表示されてしまいました。
ここで行き詰ってしまいました。
どのようにすればよろしいでしょうか？
教えてください。
よろしくお願いいたします。

回答No.2

keithin

追加の情報提供で書かれている#REFが出る前に，「外部データにアクセスできません」のダイアログが出ていた場合，Excelのオプションの詳細設定の欄でダイアログをずっと下に下げたところにある「Dynamic Data Exchange(DDE)を使用する他のアプリケーションを無視する」のチェックが入っているのかも？しれません。チェックを外し，全部のエクセルを再起動します。

または，Excel(1)とExcel(2)を別個に起動したいがために，何か特別な細工を以前にしていた場合は，それが悪さをしている可能性もあります。

別の可能性としては，「MyRng1」を設定する手順が実は書かれているのと違うことをしていて，INDEX関数が正しく計算できていない場合にも#REFが出る事が考えられます。
別のエクセルでティック.xlsmを開いておいた状態で，こちらのエクセルのセルに
=excel|ティック.xlsm!myrng1
とだけ記入して，myrng1の先頭セルの値が参照できれば成功になります。

お礼

keithin 様

できました！ありがとうございます！

>Excelのオプションの詳細設定の欄でダイアログをずっと下に下げたところにある「Dynamic Data Exchange(DDE)を使用する他のアプリケーションを無視する」のチェックが入っているのかも？しれません。

チェック自体は入っていなかったのですが、ある事に気が付きました。
地震の影響でデータベンダーとのDDEリンクが来週まで接続できません。
そのため、EXCELの数式バーの上に「セキュリティーの警告　リンクの自動更新が無効にされました　（オプション）」と表示されていました。この（オプション）をクリックし、リンクの接続を色々といじっていたらできるようになりました。

複数回に渡りご回答いただきまして本当にありがとうございます。
また、私の説明不足のためにお手数をおかけしたことをお詫び申し上げます。
ありがとうございました。