AccessVBAでVLookupを使いたい

質問

Access VBA で VLookup を使いたいと思っていたのですが、
「メソッドまたはデータメンバが見つかりません。」とエラーになります。

箇条書きになりますが、行いたいことは以下のとおりです。
・「D:\0402\コメント.xlsx」を開き、最初のシート（sheet1）を選択する。
・sheet1の A列には検索対象の文字列が、B列にはそれに対するコメントが文字列で入っています。
・VLookup 関数を使用し、変数「vDir.Name」を検索値、sheet1全体が検索範囲、
B列の情報がほしいので、列は「2」、完全一致で取りたいので「False」としています。
・B列の返り値を変数「vComment」に代入します。

下記が書いたソースなのですが、「vComment = ～」を記述すると、
冒頭の「メソッドまたはデータメンバが見つかりません」とエラになってしまいます。
前後のプログラムの関係上、Accessのクエリの使用や、Excelをリンクテーブルにすることはできません。

解決方法がわかる方、助けていただけますでしょうか。
Access は 2010 になります。よろしくお願い致します。

'エクセルを開く

Dim oCommentXLS As Object
Dim oCommentXLSWorkbook As Object
Dim vFileCommentXLS As String

vFileCommentXLS = "D:\0402\コメント.xlsx"

Set oCommentXLS = CreateObject("Excel.Application")
Set oCommentXLSWorkbook = oCommentXLS.Workbooks.Open(vFileCommentXLS)

Dim oSheet As Object
Set oSheet = oCommentXLS.Worksheets(1)

Dim raCommentFind As Range
Dim vComment As String
Set raCommentFind = Cells.Find(What:=vDir.Name, LookIn:=xlValues, LookAt:=xlWhole)
If Not raCommentFind Is Nothing Then
Debug.Print "みつかりました。"
vComment = Application.WorksheetFunction.VLookup(vDir.Name, Range("A1:B1000"), 2, False)
Debug.Print vComment
Else
Debug.Print "見つかりませんでした"
End If

回答No.2

30246kiku

提示されたのは、どのような環境で記述されたものなのでしょうか。

> Dim raCommentFind As Range

がエラーになっていない？であれば、Excel は参照設定されているのでしょう。
参照設定しているのであれば、

> Dim oCommentXLS As Object
> Dim oCommentXLSWorkbook As Object
> Dim oSheet As Object

部分は、

Dim oCommentXLS As Excel.Application
Dim oCommentXLSWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet

と記述しておけば、例えば、oSheet と記述した後に . を入力すると、候補が表示され便利です。
また、
> Set oCommentXLS = CreateObject("Excel.Application")
する必要はなく、
Set oCommentXLS = New Excel.Application
で事済みます。

また、Excel で良く使う Application は、oCommentXLS にあたるので
　Application.WorksheetFunction
↓
　oCommentXLS.WorksheetFunction

参照設定していないのであれば、xlValues　xlWhole　等の Excel での定数は定義しておく必要があります。
また、Cells　Range を記述する時には、誰の下で・・・を指定します。
　Cells.Find( → oSheet.Cells.Find(
　Range("A1　→ oSheet.Range("A1

余談）

参照設定すれば Cells　Range はそのまま記述しても動くようですが、
誰の下で・・・を指定しておいた方が良いかも
以下、Excel を参照設定してある環境で実行してみます。

Public Sub test1()
　　Dim oApp As Excel.Application

　　Set oApp = New Excel.Application
　　oApp.Visible = True
　　oApp.Workbooks.Add
　　oApp.Range("B2") = "ABCD" ' Range("B2") = "ABCD" でも同じ結果に
　　Set oApp = Nothing
End Sub

※ Application 直下に Range 記述しているので、アクティブブックのアクティブシートが隠れているようですが、
さて、以下での ★ は、どこに記述されるのでしょう

Public Sub test2()
　　Dim oApp1 As Excel.Application
　　Dim oApp2 As Excel.Application

　　Set oApp1 = New Excel.Application
　　Set oApp2 = New Excel.Application
　　oApp1.Visible = True
　　oApp2.Visible = True
　　oApp1.Workbooks.Add
　　oApp2.Workbooks.Add
　　oApp1.Range("B2") = "ABCD"
　　oApp2.Range("B2") = "abcd"
　　Range("C2") = "1234" ' ★
　　Set oApp1 = Nothing
　　Set oApp2 = Nothing
End Sub

Public Sub test3()
　　Dim oApp As Excel.Application

　　Set oApp = New Excel.Application
　　oApp.Visible = True
　　oApp.Workbooks.Add
　　oApp.Range("B2") = "ABCD"
　　Set oApp = Nothing

　　Set oApp = New Excel.Application
　　oApp.Visible = True
　　oApp.Workbooks.Add
　　oApp.Range("B2") = "abcd"
　　Range("C2") = "1234" ' ★
　　Set oApp = Nothing
End Sub

※ エラーになる場合もありますね。
エラーにならない時には、どちらも 1234 の左側は ABCD でしたね
（Vista + Access2007 にて）

回答No.1

kumatti1

Accessからなのだから、ライブラリ名を省略しちゃだめでしょう。
(参照設定はExcel2010だとMicrosoft Excel 14.0 Object Libraryかな)

＞vComment = Application.WorksheetFunction.VLookup(vDir.Name, Range("A1:B1000"), 2, False)
vComment = Excel.Application.WorksheetFunction.VLookup(vDir.Name, Range("A1:B1000"), 2, False)