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

質問

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を使用です。

回答No.8

Wendy02 · ベストアンサー

--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の問題ではなく私自身の調子がおかしいようです。だから、気をつけましたが、間違いがあるかもしれません。

お礼

詳細な回答ありがとうございます！excelを開くコードで実行したところ、ものすごい時間がかかってしまったので、excelを開かずに値を取得するコードを教えていただけてよかったです。
y = .ExecuteExcel4Macro("Match(" & ShipTo & "," & mFrm & "C1,0)") 'A列検索
上記がうまく行数を取得できなかったので、検索範囲を変更しました。
excelの表はほとんどかわらないので
y = .ExecuteExcel4Macro("Match(" & ShipTo & "," & mFrm & "R1C1:R194C1,0)") 'A列検索
で範囲を直接指定しました。（ちょっとスマートじゃないですが）

これからページめくりに取り掛かります！
ほとんど教えて頂いたような形になってしまい、勉強不足でお恥ずかしいです。
どうもありがとうございました！

回答No.9

Wendy02

>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のテキストは持っているのですが、前のもの共通性がない部分があるので、分からなくて、ちょつと困ってしまいます。

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

お礼

色々と教えてくださりありがとうございました！
ページめくりの部分をDo Loopにしたところ、最終ページのみ処理が行われず、比較演算子での条件を思いつけなかったので結局Forでループすることにしました。ですが、
Selection.HomeKey wdStory
は、明記して確実にページ初めから始まるようにしました。

Application間の値の受け渡しから始まり、たくさんコードを考えて教えてくださり本当にありがとうございました。
マクロ4.0を利用する方法なども大変勉強になりました。
Excelを開かずに済んだことでスピードも格段にUPしました。
感謝感激です！これで質問を締め切らせていただきますね。
すべての回答をベストアンサーにしたいです。（できるのかわからないのですが）
ポイントなどもよくわからず、お礼が出来なかったら申し訳ありません。

どうもありがとうございました！

回答No.7

Wendy02

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

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

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 --

補足

たくさんアドバイスをありがとうございます！ネットで探したコードを切り貼りして作ったWord vbaなのでよくわかっておらずお恥ずかしい限りです。

ShipTo = Mid(myRng, 11, 5)
のところは、私の方ではCRコードは入らなかったのですが、ユーザーさんのところで入ってしまってはいけないので、
ShipTo = Mid(Replace(myRng.Text, vbCr, ""), 11, 5)　に変更しました。
また、Application.Runを多用していましたが、Callにします。
（Application.RunとCallの違いは参照渡しと値渡しの違いと考えていいのでしょうか？どちらにせよ今回、プロシージャ間で値は返してないので意味ないですね。）

その他、1つ1つ実行して検証してみますので、少しお時間ください！
ありがとうございました！

回答No.6

rukuku

こんばんは

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

補足

回答ありがとうございます！結構シンプルに値を渡せるのですね！勉強になりました。
今回、Wordでshiptoへ格納した値を使ってexcel側で検索を行いたいので、excel側に作ったvbaを走らせるコードを入れていました。検索された値はセルに格納せず変数に直接入れたいので（最初はわからずセルに入れたのですが・・・）色々とやってみていたのですが・・・教えていただいたコードを参考にしてみます！分かりやすい説明をありがとうございました！

回答No.5

Wendy02

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

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

補足

回答ありがとうございます。昨日は帰宅してしまい遅くなりすみません！
Valは問題ありますか？このまま締めずにおきます。

回答No.4

Wendy02

>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 はほとんど使いません。理由は、オブジェクトの範囲が広いからなのです。

お礼

原因が分かりました！Wordで取得していたshiptoが文字列形式だったので、excel側の形式と合わず、VLOOKUPで拾えなかったようです！
strA = Application.VLookup(Val（ShipTo）, endRow, 3, False)
と、shiptoにValをつけたらいけました！

本当に何度も回答を頂きありがとうございました。本当に助かりました！私も頑張って勉強します！
またわからない時にはどうぞよろしくお願いいたします。

補足

本当に何度もありがとうございます！IF文のところは大丈夫です。エラーはmsgboxに"Error"と出ていたからではなく、excel側で変数をみたところ2042とポップヒントが出ていたので、#N/Aかなと思いまして・・・。
ShipTo 取得の部分はWord側で下記のようにつくっています。プロシージャがたくさんあるので、変数はすべてモジュールレベルに宣言しています。

Sub SearchShipTo()
'-----------------------------------------
'SHIP TOをsearchして段落内の文字列を取得
'ページ数分ループ
'-----------------------------------------
Application.ScreenUpdating = False
ActiveDocument.Repaginate

pageCount = ActiveDocument.BuiltInDocumentProperties(14)
MsgBox pageCount

For i = 1 To pageCount

mySearch = "ship to"
Selection.Find.ClearFormatting
With Selection.Find
.Text = mySearch
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = False
.MatchFuzzy = False
End With

Selection.Find.Execute
Selection.EndKey unit:=wdLine, Extend:=wdExtend

Set myRng = Selection.Range
ShipTo = Mid(myRng, 11, 5)

Application.Run "getExcelTable2"　←教えていただいたコードへ

　　 myTxt = ShipTo & "/" & strA & "/" & strB

　　 Application.Run "addTextBox"　←別プロシージャでテキスト追加

Application.Browser.Target = wdBrowsePage
Application.Browser.Next
Next i
End Sub

excelで下記のように単体テストをしましたら、これはちゃんと文字を表示しました。
Sub test()
Dim ShipTo As Variant
Dim strA As Variant, strB As Variant
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1", "t194")
End With

ShipTo = 60036
strA = Application.VLookup(ShipTo, myRng, 3, False)

MsgBox strA
End Sub

原因を探してみます。何度もお付き合いくださりありがとうございます！

回答No.3

Wendy02

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

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

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に格納させます。

補足

何度も回答ありがとうございます！Appkication.VlookupはエラーのためにWorksheetFunctionを省略した形なんですね！勉強になりました！"c"は"shipto"に変更しています。データ型もわかりました。Word側でstrAとBをstringで書いてしまっていたミスでした！
デバックは出なくなりましたが、excel側で格納されたstrAがエラー2042になってしまいました。2042=#N/Aなのかな？と思いますが、shiptoの検索値から見た3列目のセルには値が入っているので、これからそこを調べてみます！

回答No.2

Wendy02

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

>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

補足

早速回答ありがとうございます！書いてくださったコードで試してみたところ、途中までうまくいきました！
ただ、excel側の
strA = Application.VLookup(ShipTo, endRow, 3, False)
で型が一致しないとエラーが出てしまいます。もともとモジュールレベルで宣言していた変数のデータ型は変えました。なぜエラーかわからないのですが・・・Application.Vlookupは初めてで理解が足りず申し訳ありません！

回答No.1

conanthe

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

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

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

お礼

早速回答ありがとうございます。会社なので環境変数は変えられないのですが、APIのGetCommandLineを使う方法でしょうか、これを試してみたいと思います。ありがとうございました！