> できれば開けずに、値を集約したかったです。
ADODB であれ Excel であれ OpenXML であれ ClosedXML であれ、
ファイルを開かないと中身が読み取れないという点は同じことですが、
数式や画像が多い場合は、Excel.Application だと時間がかかってしまうかもしれませんね。


>> ADOX の方は CreateObject を使わず、参照設定を必須にしている点に違和感があります。
> Dim adoCat As ADOX.Catalog
> または Set adoCat = CreateObject("ADOX.Catalog")
> に、修正する形でよろしいでしょうか？
そうですね。
ついでに言えば、「ADODB と ADOX の両方を参照しておく」か、
「いずれも参照設定しない状態」にする方が統一性が取れるでしょう。
（可能なら、参照して使う方法にしておいた方が良いとは思います）


> エクセルのシート順を変えても順が変わりません。
ADOX にしても adSchemaTables にしても、
列挙順については保証の限りでは無い、ということなのでしょう。

手元の環境でも、
　シート順を変更して保存すると列挙順が変化するワークブック
　シート順を変更して保存しても列挙順が変わらないワークブック
と様々です。

検証を重ねていないので、何が影響しているのかまでは特定しておりませんが、
いずれの方法も、「列挙順」に関しては、確実性のある列挙方法とは言い難いです。
Excel.Application 経由で調べる方法と比較すると、
正直、そこまでの厳密性は期待できなさそうですね。

Excel のバイナリーフォーマットは公開されているので、調べようとすれば
Excel.Application を使うよりも早く列挙することができるかもしれませんが、
それだけのために直接バイナリを扱うのは、あまり現実的では無さそうです。

なお、snowmansnow さんの最初のコードにしても私の手法にしても、
ファイルによっては、追加の作業が発生するようです。

手元にあった 3 シート構成のブックが 0～5 の 6 件のデータを返すことがありました。
そのケースでは .Worksheets(1).Name が "実施概要" だったのですが、
　SNo = 0 → "実施概要$"
　SNo = 1 → "実施概要$_xlnm#Print_Area"
といった形式で、の文字列を返してきました。
どうやら印刷範囲設定が、名前の定義として認識されているようですね。

また、.Worksheets(2).Name = "実施概要 (変更前)" に対しては
　SNo = 2 → "'実施概要 (変更前)$'"
　SNo = 3 → "'実施概要 (変更前)$'_xlnm#Print_Area"
のような文字列が返されるケースもありました。
末尾 $ だけでなく、両端シングルクォートも考慮せねばならないようです。


>> ReDim sheetNames(1 To rs.RecordCount)
> が、エラー(値が-1）になり、１００決め打ちで
　Dim cn As ADODB.Connection, rs As ADODB.Recordset
　Set cn = CreateObject("ADODB.Connection")
　cn.CursorLocation = adUseClient
にした場合は正しい件数を返したのに対して
　Dim cn As Object, rs As Object
　Set cn = CreateObject("ADODB.Connection")
　cn.CursorLocation = adUseServer
だと、-1 を返してきました。
どちらの場合も、rs.CursorLocation は adUseServer になりました。

RecordCount に頼るのも不安なので、配列ではなく Dictionary に加えていくか
rs.GetRows で取得した方が安全そうですね。


Print_Area 関連を除去するコードも加えると、こんな感じでどうでしょう。

'シート名の一覧を返す(列挙順は保証できない) 

Function GetSheetNames(ByVal excelFile As String) As String()

    Dim cn As ADODB.Connection, rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.CursorLocation = adUseClient

    cn.Provider = "Microsoft.ACE.OLEDB.12.0"

    cn.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"

    cn.Open excelFile

    Set rs = cn.OpenSchema(adSchemaTables)

    

    Dim re As VBScript_RegExp_55.RegExp

    Dim ms As VBScript_RegExp_55.MatchCollection, rm As VBScript_RegExp_55.Match

    Set re = New VBScript_RegExp_55.RegExp

    re.Pattern = "^'?(.*)\$'?(#Print_Area|_xlnm#Print_Area)?$"

    Dim dict As Scripting.Dictionary

    Set dict = New Scripting.Dictionary

    Dim tableName As String

    Do Until rs.EOF

        tableName = rs.Collect("TABLE_NAME")

        '各種接尾辞を削ぎ落す 

        Set ms = re.Execute(tableName)

        If ms.Count > 0 Then

            Set rm = ms.Item(0)

            tableName = rm.SubMatches(0)

        Else

            Debug.Print tableName

        End If

        dict(tableName) = CByte(0)

        rs.MoveNext

    Loop

    rs.Close

    cn.Close



    '「内部型式が String なバリアント型の一次元配列」を「文字列型の一次元配列」に変換 

    GetSheetNames = Split(Join(dict.Keys(), vbNullChar), vbNullChar)

End Function

この関数の戻り値は As String() ですが、
これを As Variant() にしても構わなければ、最後の行は
　GetSheetNames = dict.Items()
だけで済みます。

また、名前以外の附帯情報が付いても構わない場合は GetRows を使うのが簡単です。
この場合、MoveNext や Dictionary も不要です。

Set rs = cn.OpenSchema(adSchemaTables)

GetSheetNames = rs.GetRows(-1)  '二次元配列として返却 

rs.Close

cn.Close

このケースでは、戻り値が (列番号, 行番号) 形式の二次元配列となり、
TABLE_NAME は列番号 2 となります。