Access VBAよりExcelのシート名を取得

質問

お世話になります。
Access2010
Windows7 Pro

以下のページを参考にしましたが、「実行時エラー3170 インストール可能なISAMドライバーが見つかりませんでした。」と表示されてしまいます。

http://www.accessclub.jp/bbs5/0005/vba1231.html
Dim Db As DAO.Database
Dim Tbl As DAO.TableDef
Dim xlsFile As String

xlsFile = "ファイルパス"
Set Db = OpenDatabase(xlsFile, True, True, "Excel 14.0;")　←ここで止まる
For Each Tbl In Db.TableDefs
If Right$(Tbl.Name, 1) = "$" Or _
Right$(Tbl.Name, 2) = "$'" Then
'シート名の最後は必ず$が付きます
Debug.Print Tbl.Name
End If
Next Tbl
Db.Close
Set Db = Nothing

なお、参照設定にて「Microsoft DAO 3.6 Object Library」にチェックが入っていないことが原因かと思い、チェックを入れようとしましたが「この名前は既にあるモジュール、プロジェクト、オブジェクトライブラリで使われています。」というメッセージが表示され、チェックを入れることができません。

勉強不足で大変恐縮ですが、これらのメッセージからどういうことが分かりますでしょうか。
ご教授の程、宜しくお願い致します。

回答No.2

NotFound404 · ベストアンサー

Set Db = OpenDatabase(xlsFile, True, True, "Excel 14.0;")　←ここで止まる
を
Set Db = OpenDatabase(xlsFile, True, True, "Excel 12.0;")　'←ここで止まる
ならOffice2010では出来ましたよ？
アプリケーションのバージョンではなくファイルのバージョン指定っぽいです。

ちょっと蛇足かもしれんが、参考になると思われます。
・・・本来 Null でない Excel 列内の一部の値が Null として返されることがあります・・・
http://support2.microsoft.com/kb/194124/ja

>この名前は既に・・・
Microsoft DAO 3.6 Object Library の上位バージョンの
Microsoft Office 14.0 Access Database Engine Object Library に
チェックが入っていれば OK です。

お礼

NotFound404さま
いつもご回答ありがとうございます。
仰る通り、12にしてみたらいけました！！

Excel2010＝Excelバージョン14と思って、14を指定したのですが。。
ちなみにファイルのバージョンとはどこを見れば確認できますでしょうか。

>Microsoft Office 14.0 Access Database Engine Object Library に
>チェックが入っていれば OK です。
そうでしたか。そちらのチェックは元々入っておりました。

ご教授ありがとうございます。

回答No.4

30246kiku

Access　VBAにてExcelファイルのシート名を取得したい
http://answers.microsoft.com/ja-jp/office/forum/office_2010-access/access/ae9cdecf-0721-40f2-8a89-caa0f5e5dddb

でも質問されているようですが、以下に違う方法を紹介しておきます。
（ADO は使える環境になっているものとして）

Public Sub Samp1()
　　Dim cn As New ADODB.Connection
　　Dim rs As ADODB.Recordset
　　Dim sA() As String
　　Dim i As Long
　　Const CFILE As String = "Excelファイルのフルパス"

　　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
　　　　　　& "Data Source=" & CFILE & ";" _
　　　　　　& "Extended Properties='Excel 12.0'"

　　i = 0
　　Set rs = cn.OpenSchema(adSchemaTables)
　　While (Not rs.EOF)
　　　　ReDim Preserve sA(i)
　　　　sA(i) = Left(rs("TABLE_NAME"), Len(rs("TABLE_NAME")) - 1)
　　　　i = i + 1
　　　　rs.MoveNext
　　Wend
　　rs.Close
　　Set rs = Nothing
　　cn.Close
'
'　この後、sA の配列（ 0 スタートの添え字）にシート名が入っているので、
'　続けて処理するとか
'
End Sub

※ 一応、Excel ファイルが xls でも、変更することなく動くみたい

※ 2000 / 2003 で動かすのなら、以下２か所を変更してください

Microsoft.ACE.OLEDB.12.0
↓
Microsoft.Jet.OLEDB.4.0

Excel 12.0
↓
Excel 8.0

回答No.3

NotFound404

私にExcelのことをお尋ねになるのは後悔することになるかも？
大別すると、対象とするファイルの拡張子が XLS なら 8.0 で十分ですが(Excel1997以降)、
XLSX(XLSMなども）も含めるのならなら 12.0 (Excel2007以降)です。
つまり、生（なま）のデータの入れ物としてのフォーマットは現時点では上記の二種類です。
それに各バージョンで書式や関数などが付加されている・・・と考えて良いのではと思います。
こんな処でご勘弁ください。

お礼

NotFound404さま
ご回答ありがとうございます。

そもそも「シート名を取得する」ということで、ヒットしたページに記載のあったものをコピペしただけで試した状況です。。なので、Excelのバージョン云々以前に、
　Set Db = OpenDatabase(xlsFile, True, True, "Excel 12.0;")
が何をしているかも理解できておりません。
恐らくシート名を取得する為に、そのExcelファイルのパスを指定し、かつ、そのバージョンを指定している・・ぐらいしか理解できておりません。
その後の、
　For Each Tbl In Db.TableDefs
If Right$(Tbl.Name, 1) = "$" Or _
Right$(Tbl.Name, 2) = "$'" Then
の部分に関してはさっぱりです。

もう少し勉強した上で質問させて頂こうと思います。
ありがとうございました。

回答No.1

nda23

Excelをデータベースとして開くのではなく、OLEオートメーションで操作します。
Dim APL 'アプリケーション
Dim WBS 'ワークブック・コレクション
Dim WKB 'ワークブック・オブジェクト
Dim WSS 'ワークシート・コレクション
Dim WKS 'ワークシート・オブジェクト
Dim シート数
Dim 索引
Dim シート名
Dim パス名

Dim パス名 = "～"
'Excelアプリケーションをインスタンス化する
Set APL = CreateObject("Excel.Application")
ワークブック・コレクションを取得する
Set WBS = APL.Workbooks
ワークブックを開いて、ワークブック・オブジェクトを取得する
Set WKB = WBS.Open(パス名) 'エラーハンドリング要
ワークシート・コレクションを取得する
Set WSS = WBK.WorkSheets
'シート数を取得する
シート数 = WSS.Count
'各シートについて処理する
For 索引 = 1 To シート数
　　'ワークシート・オブジェクトを取得する
　　Set WKS = WSS(索引)
　　'シート名を取得する
　　シート名 = WKS.Name
　　'シート名による処理
　　★略
　　'ワークシート・オブジェクトを解放する
　　Set WKS = Nothing
Next
'ワークシート・コレクションを解放する
Set WSS = Nothing
'ワークブックを閉じる
WKB.Close
'ワークブック・オブジェクトを解放する
Set WKB = Nothing
'ワークブック・コレクションを解放する
Set WBS = Nothing
'アプリケーションを終了する
APL.Quit
'アプリケーションを解放する
Set APL = Nothing

丁寧にコードを書きましたが、中間オブジェクトを省略することも
できます。但し、内部でどのようなことが行われているかを知れば、
中間オブジェクトを省略することが、ものすごく効率が悪いという
ことを知るべきでしょう。特に変数が静的な場合は解放を怠ると、
メモリが解放されず、重ねて使うと、メモリストレスが増大します。
尚、オブジェクトのプロパティやメソッドの引数、及び戻り値の
内部形式は全てVariant型です。そうでないものは変換されて
使用されます。このため、サンプルでは変数は全て型指定を省略
してVariant型でやっています。

OLEオートメーションではExcelのVBAと同じことができます。
但し、xl～のようなExcelマクロ固有の定数は使用できません。

お礼

nda23さま
詳しいご回答ありがとうございます。

説明不足で申し訳ございません。。。実は当該AccessはRuntime環境で利用することを想定し、作り込みを行っております。
よって、「CreateObjectを使えない」状況です。

教えて頂いた方法は今回使えませんが、大変勉強になりました。

ありがとうございました。