シート名、名前付き範囲の一覧を取得する

シート名、名前付き範囲の有無を確認する関数を作成する

シート名、名前付き範囲の有無を確認する関数

Function chkName(chkFile As String, chkTBL As String) As Boolean
Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" & _
           "Data Source=" & chkFile
'レコード件数をカウントするのでCursorLocationの設定をする
myCon.CursorLocation = adUseClient

'Excelブックのスキーマ情報を取得
Set myRS = myCon.OpenSchema(adSchemaTables)

'TABLE_NAMEフィールドを元にフィルタをかける
myRS.Filter = "TABLE_NAME = '" & chkTBL & "'"

'対象となるレコードの件数でテーブルの有無を判定
If myRS.RecordCount > 0 Then
  chkName = True
Else
  chkName = False
End If

myRS.Close: Set myRS = Nothing
myCon.Close: Set myCon = Nothing

End Function

ユーザー定義関数を利用する

Sub テーブルの存在確認()
Dim myFile As String, myTable As String
'確認したいファイルを指定
myFile = ThisWorkbook.Path & "\xls\DataBook.xls"
'確認したいシート、名前付き範囲を指定
myTable = "商品マスタ$"

'結果を表示
If chkName(myFile, myTable) = True Then
  MsgBox myTable & "は存在します。"
Else
  MsgBox myTable & "は存在しません。"
End If

OpenSchemaメソッドの結果から、TABLE_NAMEフィールドの値を元にフィルタをかけ、抽出されたレコードが1件以上あれば、すでにそのテーブル名が存在していると判断して結果を返す。