エクセルＶＢＡでWorkbookオブジェクトの割当てについて

質問

すでに開いている２つのブック間でデータ参照を行いたい場合、それぞれをｗｂ１（トラン）ｗｂ２（マスタ）と紐つける方法はありますでしょうか？

トランが複数あり、マスタが大量なので出来ればマスタを開きぱなっしにしておいてトランを手動で開いてはマクロ実行、次のトランを開いて実行という簡易な感じにさせたいのです。

マスタをアクティブにしておいてからマクロ実行
１Set wb2 = ActiveBook
２トランは閉じておいてオープンDialogでトランをファイル指定させておいて
３Set wb1 = WorkBook.Open(filename)
という方法しかわかりません。
ただしこの方法だといきなりマクロの実行が始まってしまうので
不備のあるトランを事前にチェック整形して保存しておかなくてはいけません。
出来れば内容チェックして大丈夫なら即実行という形にしたいのですが
良い手を教えてください。

回答No.4

kenpon24 · ベストアンサー

#2です。

>通常のMsgboxだと画面がロックされて他のエクセルをアクティブにすることが出来ません。
メッセージボックスはモーダル(といいます)なので、表示されている間にシートの編集をすることはできません。
編集するためには、モードレスなユーザーフォームを作るのがいいでしょう。ラベル１つとボタン2つで済みます。

こちらが参考になると思います。
ユーザーフォームの作り方
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_080.html
モードレスなフォームを作成する
http://pc.nikkeibp.co.jp/article/column/20080513/1002385/?P=1

showメソッドの引数にvbModelessを指定するか、ユーザーフォームのプロパティでshowModalをFalseにするのがポイントです。

ちなみにコマンドバーを生成するのでも、モードレス的に動作させることができます。「マスタにしたいブックをアクティブにしてください」メッセージはmsgbox等であらかじめ表示しないといけませんが。

サンプル
Sub Toolbar() 'コマンドバーの作成。
Dim myToolbar As CommandBar
Dim EXEC_Button As CommandBarButton
Dim Cancel_Button As CommandBarButton
'すでに表示されているときは削除
Call cancel_tool

Set myToolbar = Application.CommandBars.Add(Name:="ますたとらん" _
, Position:=msoBarFloating, MenuBar:=False, Temporary:=True)

Set EXEC_Button = myToolbar.Controls.Add(Type:=msoControlButton, Before:=1, Temporary:=True)
With EXEC_Button
.Caption = "実行"
.Style = msoButtonCaption
.OnAction = "module1.exec_tool"
.BeginGroup = True
End With

Set Cancel_Button = myToolbar.Controls.Add(Type:=msoControlButton, Before:=2, Temporary:=True)
With Cancel_Button
.Caption = "キャンセル"
.Style = msoButtonCaption
.OnAction = "module1.cancel_tool"
.BeginGroup = True
End With

myToolbar.Visible = True 'ツールバーを表示
End Sub

Sub exec_tool()
Call cancel_tool
'実行ルーチンを記述
MsgBox "完了"
End Sub

Sub cancel_tool()
On Error Resume Next
CommandBars("ますたとらん").Delete
End Sub

お礼

参考リンクとサンプルまで教えて頂き助かりました。
コマンドバーを一時的に作り出してマクロの中断・再開が
出来るとは知りませんでした。

お蔭様でサンプルを加工して、開いている２つの
エクセルをそれぞれＷＢに紐付けることが
出来ました。

実務上はオープンダイアログとの組み合わせに
するのかどちらもコマンドバー型にするのか
これから操作性を考えていこうと思います。

なおフォーム型も試したいと思います。
どうもありがとうございました。

回答No.3

imogasi

>不備のあるトランを事前にチェック整形して保存しておかなくてはいけません。
普通のバッチ処理ではそうするはずですが。
自動化の実を挙げるため。
ーー
各ループにおいて、トランザクションファイルの指定のダイアロウグのファイル指定かMSGBOXの応答で待たしておけばダメなのですか。
その間別エクセルでトランザクションのエクセルファイルを修正し
終ればクローズする。そして応答する。
見当ハズレかな？

お礼

回答ありがとうございます。
説明不足で申し訳ありません。

トランの一連の作業の途中でマスタとのチェックをマクロで
させたいというケースなのでどちらかというと
手作業支援という感じです。
デバッグモードのようにマクロを中断させておいて、
準備が出来たら再開なんていうのがあれば解決できるんですけど。

でも紐つけ手段が無い場合は自動化方針で事前に修正しておく
しかないですね。

回答No.2

kenpon24

どんな形式になっているのか知りませんが
チェックするマクロを書けるのであれば、

形式が正しい時は実行。正しくないと判断したときは、
"押せば実行する"っていうボタンを生成する、とか。

具体的には、
実行部分をひとつのルーチンとしてまとめておいて
形式が正しいときは実行ルーチンを呼ぶ。

正しくない時はオートシェイプ、またはコマンドバーとボタンでも作って、マクロを登録する動作を書く。(そのマクロは作ったボタンを削除して、実行ルーチンを呼ぶという動作をする)

お礼

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

「"押せば実行する"っていうボタンを生成する」
うーーん、これは敷居が高そうですね。
もうちょっと簡単なこういうのは出来ますでしょうか。
たとえば「マスタにしたいブックをアクティブにしてください，ＯＫ、キャンセル」というMsgBoxを出し,該当のエクセルを選んでＯＫボタンを押す・・みたいな。
通常のMsgboxだと画面がロックされて他のエクセルをアクティブにすることが出来ません。

回答No.1

anmochi

「同じExcel.Application上で」なら、
トラン側のマクロで「Application.Workbooks()」プロパティを操作する事でマスタブックを取得する事ができる。具体的には

・マスタ：master.xls
・トラン：tran1.xls, tran2.xls,・・・

tran1.xlsのThisWorkBookに以下のコードを書く。

Dim wb2 As Excel.Workbook
Set wb2 = Me.Application.Workbooks("master.xls") ' このへんちょっとあいまい・・・・

これで、トラン側でマスタのブックを取得する事ができる。何度も言うように同じExcel.exe内で開かれていないとダメ。開いていればそれを使い、なければ自動で開くというような処理にしておけばよろしかろう。今手元にExcelが無いので試せないけど、いろんなプロパティに目を通せば大体何をすれば何ができるか分かるよ。

マスタを参照してデータをよんでごにょごにょ、というマクロをトラン側に持たせるのが肝心だ。もちろん設計しだいだが。

お礼

回答ありがとうございます。
トランは毎回異なるのでトラン側に
マクロを入れ込むのは事前のチェック整形と似た
作業的になってしまいます。

マクロブック(ボタン）にトラン、マスタの
ｗｂを認識させたいと思っております。