VBA セルにドロップダウンリスト 作成

質問

Access VBAについてお尋ねいたします。
Access VBA にてExcelファイルを操作するプログラムを作成しています。
Excelの指定した範囲のセルにドロップダウンリストを作成しようとしてコードを入力したらエラーが発生しました。

エラー内容：実行時エラー'1004'アプリケーション定義かオブジェクト定義エラーです

エラーとなった処理コード
Dim Exf as string
Dim AppObj As Object 'Excel.Applicationオブジェクトの宣言
Dim WBObj As Object 'Excel.Workbookオブジェクトの宣言
Dim WsObj As Object 'Excel.WorkSheetオブジェクトの宣言
'実行時バインディング
Set AppObj = CreateObject("Excel.Application")
'編集を実施するワークブックを開く
Set WBObj = AppObj.WorkBooks.Open(Path & Exf)
'編集を実施するワークシートを設定する
Set WsObj = WBObj.Worksheets(1)
'Excelアプリケーションを表示する
AppObj.Visible = True
col = 5:Count(i) = 15
'２行目からデータが存在する最後の行までドロップダウンリストを作成する
With WsObj.Range(WsObj.cells(2, Col), WsObj.cells(Count(i) + 1, Col)).Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=Join(Li, ",")
End With

処理の解説
１：Accessと同じファイルパス内のExcelファイルを開く
２：先頭のシートにて処理を実行する
３：E列の２行目からデータがある最後の行までをドロップダウンリスト化する
４：ドロップダウンリストにするデータは各Excelファイルの先頭から２番目のシートのA列にデータがあり、Liという配列に格納してあります。

※A列のデータの範囲【行数】が処理するたびに異なるため、Liという配列を使用し、配列の全データをドロップダウンリスト化するという方法を採用いたしました。

エラーの修正方法がお分かりの方がいらっしゃいましたら回答のほどよろしくお願いいたします。

回答No.2

imogasi

#1です。
下記でテストで、動きました。質問と変えているところがありますが、まあ、やってみて、その後、自分の内容になるよう、少々修正を考えてください。
Sub test01()
Dim Exf As String
Dim AppObj As Object 'Excel.Applicationオブジェクトの宣言
Dim WBObj As Object 'Excel.Workbookオブジェクトの宣言
Dim WsObj As Object 'Excel.WorkSheetオブジェクトの宣言
Dim l As Variant
'実行時バインディング
Set AppObj = CreateObject("Excel.Application")
'編集を実施するワークブックを開く
Set WBObj = AppObj.WorkBooks.Add 'Open(Path & Exf)
'編集を実施するワークシートを設定する
Set WsObj = WBObj.Worksheets(1)
'Excelアプリケーションを表示する
AppObj.Visible = True
Col = 5: Cnt = 15
'２行目からデータが存在する最後の行までドロップダウンリストを作成する
Dim MyList(3) As String
MyList(0) = "東京"
MyList(1) = "大阪"
MyList(2) = "福岡"
MyList(3) = "仙台"
'---
With WsObj.Range(WsObj.Cells(2, Col), WsObj.Cells(Cnt + 1, Col)).Validation
.Add Type:=3, Operator:=1, Formula1:=Join(MyList, ",")
End With
End Sub
ーー
xl定数については、「エクセルＶＢＡ」（＜－－エクセルですよ）で下記を実行し、その示す具体的な数値を知り、そちらを使うことで逃げました。
正しいやり方、別のやり方は、昔に解説記事を読んだ記憶があるのですが、忘れました。他の回答者が、この点を、いつ答えてくれるかわからないので、とりあえず、上記でやってはどうかな。
Sub test02()
MsgBox xlValidateList '-->3
End Sub
Sub test02()
MsgBox xlBetween '-->1
End Sub
ーー
上記は、」ブックは新規ブックでやりました。
Exf　でやるなら修正してください。
リストの内容も普通は、エクセルでは、セル範囲のデータを指定するのが標準です。上記はプログラム内で定義．議論はあろうかと思うが、さらに、むつかしい点を抱え込むと思う。
＞２行目からデータが存在する最後の行までドロップダウンリストを作成する
はおかしい表現と思うが？？
現状はデータのないセルに入力規則を設定するものだろう？？

回答No.1

imogasi

・Path & Exf　が定義されてない
・Count(i) = 15は配列的な書き方だが、配列ではなく通常の変数の形にすべきです。変数名は関数にあるＣｏｕｎｔは使わない方がよい。
・Xlvalidatelistは、ｘｌ定数は（前質問で書いたが）エクセルVBAの世界で定義されている定数のようで、Accessの世界からは使えないかも（小生不確か）。
ーー
その他に点では、前の質問で申し上げたように、他アプリのコードからエクセルを動かそうとするのは、仕組みの知識が要る（安易にエクセルのVBAの知識だけでは危うい）ことに注意されたい。
　この点は、関連記事も書籍やＷＥＢで少なく苦労するはず。
ーー
小生も明日テストをやってみて、勉強します。とりあえず常識的に気が付いた点を挙げます。