ややこしいですがご協力ください

質問

アクセスからエクセルを起動してエクセルで実行した変数をアクセスに持って帰りたいです。

【アクセス側】_
Sub エクセルの値を持ってくる()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim MyFileName As String
Dim myStr As String

Set xlApp = New Excel.Application

MyFileName = "C:\Users\エクセル.xlsm"
Set xlBook = xlApp.Workbooks.Open(MyFileName)

xlApp.Visible = True
xlApp.Run "'" & MyFileName & "'!" & "Excel_test"
Set xlApp = Nothing

MsgBox myStr　’★

End Sub

【エクセル側】
Sub Excel_test()
Dim myStr As String
myStr = "アクセスへ渡す"
MsgBox myStr
End Sub

としたときに、アクセスの MsgBox myStr　★でも
エクセルで実行させた変数を持って帰りたいのですが
どこを修正すればよろしいでしょうか？

ご教授よろしくお願いします。

回答No.2

m3_maki · ベストアンサー

値を返すためには
Function プロシージャ が必要です。

【エクセル側】
Function Excel_test()　'※
　　Dim myStr As String
　　myStr = "アクセスへ渡す"
　　Excel_test = myStr
End Sub

【アクセス側】_
　　myStr = xlApp.Run("'" & MyFileName & "'!" & "Excel_tes")
　　MsgBox myStr

※返す値の型がわかっているのなら明示した方が良いでしょう。
Function Excel_test() As String

お礼

そうですね！
値を返すという事はFunctionプロシージャーにするべきでしたね。
教えて頂いた通りに行ったらうまく出来ました。ありがとうございました。

回答No.1

mitarashi

エクセル側でエクセルの作業用シートのセルに書き込んで、そこからアクセスが読み出す方法はいかがですか。

別解ですが、クリップボード経由をやってみました。
アクセス、エクセル共に、Microsoft Forms 2.0 Object Libraryに参照設定する必要があります。
参照設定ダイアログボックスの一覧に表示されない場合は、参照ボタンをクリックして、System32フォルダーのFM20.DLLに参照設定して下さい。
☆Access側

Sub test7()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim MyFileName As String
Dim mystr As String

'System32フォルダーのFM20.DLLに参照設定要
'FM20JPN.DLLはFM20.DLLから使うモノらしいのでそちらではNG－と思う
Dim CB As DataObject

Set CB = New DataObject
Set xlApp = New Excel.Application

MyFileName = "C:\Users\?????\Documents\エクセル.xlsm"
Set xlBook = xlApp.Workbooks.Open(MyFileName)

xlApp.Visible = True
xlApp.Run "'" & MyFileName & "'!" & "Excel_test"
xlApp.Quit
Set xlApp = Nothing
With CB
.GetFromClipboard
mystr = .GetText
End With
MsgBox mystr '★
End Sub

☆Excel側
Sub Excel_test()
Dim myStr As String
Dim CB As New DataObject
myStr = "アクセスへ渡す"
With CB
.SetText myStr
.PutInClipboard
End With
End Sub

参考URL：

http://officetanaka.net/excel/vba/tips/tips20.htm

お礼

クリップボード経由でも可能なのですか。ありがとうございました。