Word vbaからexcel vbaへ値を渡すことができますか？

Question

Word vbaからexcel vbaへ値を渡すことができますか？
現在Wordでvbaを組んでいますが、Wordで取得した値をexcel vbaで利用したいのに変数の値がnullになってしまい四苦八苦しています。Application間で値を渡すには何か特別な処理がいるのでしょうか？（参照は設定済みです）

やりたい事は、Wordで「ShipTo」文字列をsearch→後ろの番号を変数へ格納→excel vbaを起動→シートの表中からWordで取得した番号を元に文字列を検索しセルに入れる（VLOOKUPを使用）→セルの文字列をWord vbaへ返しWord側で利用、という処理です。excel vbaの起動は以下のように記述しています。

------------------excelVBAの起動
Sub getExcelTable()

Dim xLobj As Object, myObj As Object
  
  Set xLobj = CreateObject("excel.application")
  xLobj.Visible = True

Set myObj = xLobj: workbooks.Open (ThisDocument.Path & "\sample.xls")
  myObj.Run ("'sample.xls'!getStr")

MsgBox strA　←これがでない
  MsgBox strB　←これがでない
  Set xLobj = Nothing
End Sub
-----------------excel vbaのコード
Option Explicit
  Dim endRow As Integer, ShipTo As Variant
  Dim strA As Variant, strB As Variant
Sub getStr()
  endRow = Range("a65536").End(xlUp).Row
  MsgBox ShipTo　←これがでない
  
  Cells(endRow + 2, 1).Select
  Selection.Value = "=VLOOKUP(" & ShipTo & ",$A$1:$T$" & endRow & ",3,0)"
  strA = ActiveCell.Text
  
  Cells(endRow + 2, 2).Select
  Selection.Value = "=VLOOKUP(" & ShipTo & ",$A$1:$T$" & endRow & ",13,0)"
  strB = ActiveCell.Text
End Sub

Word vbaからexcel vbaは起動します。Word側、excel側で使う関数はグローバル変数にし、variant型にしてみましたが値がわたりません（Wordでは取得できています）。Application間で値を渡す方法、もしくは根本的にもっとうまい方法があればご教示ください！

WinXP、Office2003を使用です。

Wendy02 · Accepted Answer

--Page Before continuing--

'Word VBAの中だけの処理
'Sub getExcelTable()
'で、
'Call getExcelTable3 
'とします。

'**もうひとつの案。''Excel のファイルを開かない方法(＝Excelはマクロを使わない）
''どちらが良いかというよりも、セキュリティや検索の速さで決めたほうがよいです。
''言い換えると、Excel のファイルのThisWorkbookのPropety のIsAddinをTrueにして他人には見せない方法も可能です。

Sub getExcelTable3()
Dim wb As Object
Dim mPath As String
Dim mFrm As String
Dim y As Long
Const FNAME As String = "[Sample.xls]"
mPath = ThisDocument.Path & "\"
　On Error Resume Next
　With CreateObject("Excel.Application")
　　mFrm = "'" & mPath & FNAME & "Sheet1'!"
　 If Not IsNumeric(ShipTo) Then ShipTo = """" & ShipTo & """"
　 y = .ExecuteExcel4Macro("Match(" & ShipTo & "," & mFrm & "C1,0)") 'A列検索
　 strA = .ExecuteExcel4Macro(mFrm & "R" & y & "C3")　'C列
　 If IsError(strA) Then strA = "Na!"
　 strB = .ExecuteExcel4Macro(mFrm & "R" & y & "C13")　'M列
　 If IsError(strB) Then strB = "Na!"
　End With
　On Error Goto 0
　If Not IsNumeric(ShipTo) Then ShipTo = Replace(ShipTo, """", "") '""を取り去る(オプション）
End Sub

4)ページめくりについて。
Application.Browser.Target = wdBrowsePage　これを入れているということですから、
SearchShipToのプロシージャで、Selection.HomeKey wdStory　を入れて、最初の所に飛ばしたほうがよいと思います。本来、Do-Loopループで、"ship to" をさがすなら、ページめくりは不要なはずです。最初から検索させれば済むとは思います。

また、最初のほうに、
 Application.ScreenUpdating = False
があるようですが、
 Application.ScreenUpdating = True が見当たらないような気がしますが、必要に応じて、入れたほうが良いかもしれません。
 
p.s.やはり、昨日から、PCの問題ではなく私自身の調子がおかしいようです。だから、気をつけましたが、間違いがあるかもしれません。

Wendy02 · Answer

>y = .ExecuteExcel4Macro("Match(" & ShipTo & "," & mFrm & "R1C1:R194C1,0)") 'A列検索
で範囲を直接指定しました。

これは、単に大きめにしても大丈夫だったと思います。なぜ、C1で通らないのか分かりませんが、それで、範囲が足りていれば、実害があるわけではないと思います。これは、古い裏技のひとつですが、Office 2007以上のバージョンでも、一応検査しています。古いものは通るのは分かりますが、上位バージョンのようにZIP圧縮したものがなぜ通るのかは分かりません。

>（Application.RunとCallの違いは参照渡しと値渡しの違いと考えていいのでしょうか？

なんとなくそういうような習慣でしたので、あまり考えていなかったのですが、言われて気が付きました。Application.Run にすると、みんな、「値渡し」になってしまいます。

本当は、Call と、付けないで書く方法との違いもあったようですが、Call を付けたほうが見やすいので付けています。

>ExecuteExcel4Macro
本当は、これは、なくなるはずだったような気がしましたが、Office 2007 で、新たに、MS側で手を加えたようですね。Helpとか、MSのテキストは持っているのですが、前のもの共通性がない部分があるので、分からなくて、ちょつと困ってしまいます。

>勉強不足でお恥ずかしいです。
いえいえ、こちらで質問される方で、これだけ理解して書ける人は、それほど多くありませんね。

Wendy02 · Answer

全体を、見なおしてみまして、なかなか難しい部分がありましたね。
それと、ちょっと気になったのは、コードの中では、あまり知られていないテクニックがあるようですね。

必ずしも正しいとは限りませんが、内容は、ご自身の研究材料にしてみてください。私自身も課題が残っているようです。

1) ship to の後の文字の取得について。

以下の○○○○の部分がどうなっているかは分かりませんが、
ship to ○○○○

>Selection.EndKey unit:=wdLine, Extend:=wdExtend
>Set myRng = Selection.Range
> ShipTo = Mid(myRng, 11, 5)
この部分は少し不安が残ります。余計なことですが、ShipTo で取った時は、String/Variant 型になっているのですが、なぜか、特別なコードが入ってしまうことがあります。Excelにはない現象です。必ず、16文字以上なら問題はないと思うのですが。

こちらではエラーが発生していたので、理由を考えたら、CRコードが邪魔になっていました。一応、念のためにこういうことを考えました。
Set myRng = Selection.Range
ShipTo = Mid(Replace(myRng.Text, vbCr, ""), 11, 5)

EndKeyメソッドも、MoveEnd メソッドも、そのままでは、CRコードが入ってしまいます。

この辺りは、未だ良い方法を思いついていませんが、もう一案なのですが、

ship to(space)○○○○ となるなら、
その3行に変わるコードとして、
 Selection.MoveRight Unit:=wdWord, Count:=3
 ShipTo = Selection.Text

というスタイルが可能です。しかし、手打ちで入れるでしょうから、：(コロン）の後必ずスペースが入るとは限りません。そこで、

ship to= ○○○○ や　ship to_ ○○○○とするなら、wdWordで、Count が 3 で取れます。
ただ、それも不案内なら、Replace(myRng.Text, "ship to", "") などを繰り返して除去する方法も思いつきます。TextBox という方法も考えますが、これは、また別のセキュリティとかロックの問題が発生してしまいます。

2) Run について。
Application.Run "getExcelTable2"
　　　 ↓
Call getExcelTable2 '同じプロジェクト内なら、このように Call でよいです。

3) 文字列か数値かの違いについて。
>strA = Application.VLookup(Val（ShipTo）, endRow, 3, False)

これも一案ですが、そのままで通るようなExcel 側のマクロを考えてみました。

--
Sub getStr(ShipTo As Variant, strA As Variant, strB As Variant)
　Dim endRow As Range
　Dim c As Range
　With ActiveSheet
　　Set endRow = .Range("A1", .Range("A65536").End(xlUp))
　End With
　 Set c = endRow.Find( _
　 What:=ShipTo, _
　 LookIn:=xlValues, _
　 SearchOrder:=xlByColumns)
　 If Not c Is Nothing Then
　　　strA = c.Offset(, 2).Value
　　　strB = c.Offset(, 13).Value
　 Else
　　 strA = "Na!"
　　 strB = "Na!" ''(後のコードをみると、短い方が良いようです）
　 End If
End Sub

--Next Page --

rukuku · Answer

こんばんは

WordからExcelを開いたときには、Excelを閉じるまではExcelのセルの値を
変数の代わりに使うことが出来ます。
（←の部分は、役目を終えたエクセルをうまく閉じるためのものです）

Sub getExcelTable()

Dim xLobj As Excel.Application ' ←
Dim ShipTo As String
Dim StrA As String
Dim StrB As String

ShipTo = "こんにちは" 'Word VBAで変数の値を設定しています

Set xLobj = CreateObject("excel.application")
xLobj.Visible = True
xLobj.Workbooks.Open (ThisDocument.Path & "\sample.xls")

xLobj.[A1] = ShipTo 'Excelのセルにワードの変数の値を設定しています
xLobj.[A2] = "こんばんは" 'Excelのセルに値を設定しています

StrA = xLobj.[A1] 'Excelのセルの値をWord VBAの変数に設定しています
StrB = xLobj.[A2] 'Excelのセルの値をWord VBAの変数に設定しています

MsgBox StrA
MsgBox StrB

xLobj.Workbooks("sample.xls").Close False
xLobj.Quit ' ←
Set xLobj = Nothing
End Sub

Wendy02 · Answer

今、すぐに書けませんが、
>strA = Application.VLookup(Val（ShipTo）, endRow, 3, False)
と、shiptoにValをつけたらいけました！

この件、少し待っていてください。だから、すぐに締めないでください。実は、すでに、そのオプションについては、こちらでは考え済みですが、Val とは違うことを考えていました。後の内容も、もう一度、見直します。

Wendy02 · Answer

>excel側で格納されたstrAがエラー2042になってしまいました。2042=#N/Aなのかな？

すみません。本日は、私は、本当に何か調子が悪いような気がします。朝の星占いも最悪になっていましたからね。（^^;まだコードが間違えているようです。ふだんは、こんなことはないのですが。

strA = Application.VLookup(ShipTo, endRow, 3, False)
　　If IsError(strA) Then strA = "見つかりません"
　　 strB = Application.VLookup(ShipTo, endRow,13, False)
　　If IsError(strB) Then strB = "見つかりません"

とすればよいはずです。
本当は、ShipTo 取得の部分から作ればよかったのですが……。

なお、
>Appkication.VlookupはエラーのためにWorksheetFunctionを省略した形なんですね！勉強になりました

これは、Excel 97方式とか呼ばれる古い書き方です。なお、実際に、私個人が作る時は、VLookup はほとんど使いません。理由は、オブジェクトの範囲が広いからなのです。

Wendy02 · Answer

>モジュールレベルで宣言していた変数のデータ型は変えました。
この場合は、モジュールレベルの変数の宣言は必要ありませんね。

データ型が一致しないというエラーは、どこの部分か、こちらでは分からないのですが、ご自分で直すには、その仕組みを理解していただくしかありません。

strA = Application.VLookup(ShipTo, endRow, 3, False)

ShipTo は、文字型、数値型、どちらでもOK ですから、Variant 型で良いわけです。endRow は、Range型、前回説明したように、strA の、Variant 型しかありません。そうしないと、エラーが発生します。

'//Word 
Sub getExcelTable2()
Dim wb As Object
Dim strA As Variant, strB As Variant

のstrA, strB の、String 型は、Variant 型ですね。

#2のコードの間違い
×　.Run "getStr", "c", strA, strB　　'"c"は、こちらの仮の値です。

訂正後
　.Run "getStr", ShipTo, strA, strB　　'ShipToの格納の仕方が見えなかったからです。

strA, strB　は空の変数を、参照渡しの変数にして、Excelに格納させます。

Wendy02 · Answer

>根本的にもっとうまい方法があればご教示ください
別にそのままで手を入れようとしたのですが、不明なエラーがなぜ発生するか、すぐに気が付きませんでした。結局、あれこれ直したら、元とは違ったものになってしまいました。

>Wordで「ShipTo」文字列をsearch→後ろの番号を変数へ格納→excel vbaを起動→シートの表中からWordで取得した番号を元に文字列を検索しセルに入れる

エラーが出る原因は、ShipTo の文字列を渡すことは可能ですが、数式には、そのままでは扱えません。本来は、「""」で文字列を囲んであげないと、Sheet には貼りつかないわけです。でも、それも正直なところ、面倒だし、シートの状態がはっきりしていない場合は、うまく数式の貼り付けがうまく行かないことがあります。

それから、VLookup の使い方は、レベルが少し高いはずです。
Application.VLookup にする理由は、エラー値を格納するためです。そうしないと、RunTime Error を起こしてしまいます。その受ける変数は、必ず、Variant 型にしなければなりません。

>後ろの番号を変数へ格納
ここの部分か解決していると思います。
---

'//Word 
Sub getExcelTable2()
Dim wb As Object
Dim strA As String, strB As String
　On Error Resume Next
　With CreateObject("Excel.Application")
　.Visible = True 'True/False どちらでも良い
　 Set wb = .Workbooks.Open(ThisDocument.Path & "\sample.xls")
　.Run "getStr", "c", strA, strB
　 wb.Close False
　 .Quit
　End With
　MsgBox strA
　MsgBox strB
End Sub

'//Excel 標準モジュール
　Sub getStr(ShipTo As Variant, strA As Variant, strB As Variant)
　Dim endRow As Range
　With ActiveSheet
　　Set endRow = .Range("A1", .Range("A65536").End(xlUp).Offset(, 19))
　End With
　　MsgBox ShipTo
　　 strA = Application.VLookup(ShipTo, endRow, 3, False)
　　If IsError(strB) Then strB = "Error"
　　　 strB = Application.VLookup(ShipTo, endRow, 3, False)
　　If IsError(strA) Then strB = "Error"
End Sub

conanthe · Answer

わたしも興味があって調べてみましたが、Excelマクロにパラメータを直接渡すことはできないようです。ただし、VBS経由で渡す方法が見つかりました。
http://www.ken3.org/vba/backno/vba086.html

わたしが昔使っていた方法ですが、いったんテキストファイルなどにパラメータを書き込み、呼び出されたマクロからそのファイルを読み込むという方法があります。

そのほかにもWindowsのユーザー環境変数を使う方法もあります。

Word vbaからexcel vbaへ値を渡すことができますか？

>y = .ExecuteExcel4Macro("Match(" & ShipTo & "," & mFrm & "R1C1:R194C1,0)") 'A列検索

--Page Before continuing--

全体を、見なおしてみまして、なかなか難しい部分がありましたね。

この回答への補足

こんばんは

この回答への補足

今、すぐに書けませんが、

この回答への補足

>excel側で格納されたstrAがエラー2042になってしまいました。

この回答への補足

>モジュールレベルで宣言していた変数のデータ型は変えました。

この回答への補足

>根本的にもっとうまい方法があればご教示ください

この回答への補足

わたしも興味があって調べてみましたが、Excelマクロにパラメータを直接渡すことはできないようです。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング