ExcelVBA内のVBSで別ブックを開いたとき

質問

ブック1で、誰のPCからでもセキュリティの警告を出さないようにブック2を開くVBSを実行し、ブック1で使用した変数xの値をブック2に渡すということをやりたいのですが、上手くいきません...。
ブック2を開く所まではできたのですが、ブック2のセルに変数xの値を入れようとすると、エラー「インデックスが有効範囲にありません。」が発生し、そもそもブック2をアクティブにできません。
ファイル名・パス等は絶対に間違えていないと思います。
原因とどう修正すれば良いか、教えて頂けないでしょうか。
下記が簡略化したプログラムになります。

Book1のプログラム
Sub Book1()

Dim x As String
x = "aaa"

CreateObject ("Shell.Application")
CreateObject("Shell.Application").ShellExecute "○○.vbs"

Workbooks("Book2.xlsx").Sheets("Sheet1").Range("A1") = x

End Sub
----------------------------------------------------------------------------------------------------------
VBSのプログラム
Dim strExcFileName
Dim objExcApp

strExcFileName ="○○￥Book2.xlsx"

Set objExcApp = WScript.CreateObject("Excel.Application")
objExcApp.Visible = True

Call objExcApp.Workbooks.Open(strExcFileName,,True)

objExcApp.DisplayAlerts = False

WScript.Quit

以上、宜しくお願い致します。

回答No.2

Prome_Lin

いろいろ実験してみたのですが・・・

たとえば、「abc.vbs」に

Dim strExcFileName
Dim objExcApp
strExcFileName ="D:\Programming\Book2.xlsx"
Set objExcApp = CreateObject("Excel.Application")
objExcApp.DisplayAlerts = False
objExcApp.Visible = True
Set bk = objExcApp.Workbooks.Open(strExcFileName)

と、記述しておき、

「VBA」の方は、

Sub Book1()
Dim x As String
x = "aaa"
CreateObject("WScript.Shell").Run "D:\Programming\abc.vbs"
Application.Wait Now() + TimeValue("00:00:10")
Debug.Print Workbooks.Count
Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = x
End Sub

としたところ、「Book2.xlsx」ファイルが立ち上がるのを「10秒」も待っているのに（実際立ち上がっています）、「Workbooks.Count」は、「1」のままなのです。

どうも、マクロを動かすときに、開いているブックが確定してしまい、後から開いたブックは認識されないみたいです。

マクロを動かす前に、これから開くブックを開いておかなければならない、ということですから、質問者のおやりになりたいことは、このままでは、不可能のように思えます。

ただ、「VBScript」側で、データを書き込んでしまうことは可能でした。

「abc.vbs」

Dim bk, wa, strExcFileName, objExcApp
Set wa = WScript.Arguments
strExcFileName ="D:\Programming\Book2.xlsx"
Set objExcApp = CreateObject("Excel.Application")
objExcApp.DisplayAlerts = False
objExcApp.Visible = True
Set bk = objExcApp.Workbooks.Open(strExcFileName)
bk.Worksheets("Sheet1").Range("A1").Value = wa(0)

「VBA」側
Dim x As String
x = "aaa"
CreateObject("WScript.Shell").Run "D:\Programming\abc.vbs " & x

すなわち、「WScript.Arguments」で、引数を受け取るのです。

回答No.1

m-take0220

CreateObjectだと、新たにExcelのインスタンスが生成されるので、既存のExcelとは別になってしまい、相互にデータの参照ができない状態になると思います。
既存のExcelインスタンスを取得するには、GetObjectが使えるはずです。