★ 処理速度がOpenメソッド使用の2倍速くなる?~『 いちいちExcelファイルをOpenせずに 』、指定したフォルダ内の、すべてのサブフォルダを延々と掘っていって、『 すべてのExcelファイルのとその中身のすべてのシート名 』を、「ADO」にてアクティブシートにゲットする(吸い込む)プログラム。(再帰的な処理)

  
※関連記事
すべてのファイルを再帰的に順に編集
すべてのファイルのモジュールの内容を一括書き出し
すべてのファイルとフォルダの基本情報の吸い込み
処理速度がOpenメソッド利用時の2倍?すべてのExcelファイルのすべてのシート名をファイルを開かずにゲット

  

バージョン2021で作りました。

以降に挙げたプログラムを標準モジュールに単純にALLコピペして、
「GetFolderAndFileListMain02」のほうだけを実行します。
(※バージョン2000の場合はエラーになります。2003か2007以降なら動くかも?テストしてません。2013以降は動くと思います。)

『 いちいちExcelファイルをOpenせずに 』、Excelファイルのすべてのシート名をゲットしますので、Openメソッドを使う方法よりも多少なりとも速いようです。
1.5~2倍くらいは速いみたいです。

ただ、数百、数千のファイルを読み込んだ時、メモリがらみのエラーになるかどうかなどはテストしていません。
また、シート名をゲットする程度か、もしくは、ADO+SQLでなんとかなるレベルの書き換えや転記などでしか使えません。細かすぎる動きをさせたい場合は、Openメソッドを使うしかありません。
あと、パスワードがかかっているExcelファイル等々はスキップしてしまい、シートを調べることはできません。

なお、Openメソッドを使うコードとの動作速度の比較には、以下のWebページのベストアンサーのコードを使ってみてください。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10271440685
(「mpath = Range("B1").Value」を、「mpath = "d:\1"」など、
 実際の目的のフォルダパスに変更が必要です。あと、r=3をr=1などにも。
 一応、最下に、アクティブシートに書き出すように変えたモノがあります。)

ただし、実際のシートの並び順通りにシート名をゲットしたい場合は、遅くなっても仕方ないので、上記のURLのように、Openメソッドを使ったほうがいいようです。
本記事の方法だと、シート名が先頭文字の文字コード順に並ぶっぽいです。

ちなみにですが、上記のURLのコードだと、パスワード付きのファイル等々で、以下のようなエラーやメッセージが出ます。

(01)「××××××××××××××××××××××××.xlsxのリンク(.link拡張子)」のファイルでエラーが出て止まってします。(デバッグモードでVBEが立ち上がる場合があり。)
(02)「開いただけで勝手に内容変更がされて閉じるときに上書き保存ダイアログが出るファイル」がある。
(03)パスワードがかかっているファイルはパスワードを入力しないといけない。

など。ほかにもあるかも?

また、自分は初心者なので、シート名のゲットやらなんやらが完璧ではない可能性があります。
その際は、ご自分でも色々と試して書き換えてください。
  

※対象はファイルのみ、で、フォルダに対しての情報は得ないものとします。
あと、Dir関数は細かく作り替えたい時にトラブルのもととなりますので、「ループ目的」としては使っていません。(サンプル程度で架空のプログラムならまだしも)

  
※2022/12/06 追記

★かなり重要な注意事項
「Dir関数は認識できない文字(文字コード?)があるらしく、また、最悪なことに、プログラムの書き方によっては、”エラーが出てほしいところで出ない”」
という機能不足や不都合があるようです。
システムとしては致命的になってしまうので、基本的には「使わないほうが無難」なようです。

どうしても使いたいなら
『「確実にパソコン内」だけで付けたファイル名しか「絶対に・100%」ありえない 』、
という前提の場合しか役に立たないようです。

また、Dir関数を使うほうが速度は4、5倍は速いようですが、一部のファイル名を認識できずにエラーになるようでは、全てのファイルやフォルダを拾えません。「4、5倍は速い」なんてものは「何の意味もない」、ということになります。

※2024/01/28追記
電帳法の関係でExcelで検索簿を作っていたら、Dir関数でのファイルの存在チェックで、「機種依存文字」が、Windows機種依存文字であるにもかかわらず、Dir関数では認識できずにエラーになる、ということがわかりました。
前述のことは、そのことを言っていたのですね・・・。勉強不足ですみません。
ですので、「全フォルダをループしてファイル名を調べて何かする、という場合は、もはやDir関数でのループは絶対に勧めてはいけない方法、ということになりました。
「絶対に機種依存文字は使いようがない」という状況の、ループ以外の場所でなら、使えます。
いずれにしましても、WebにはDir関数を使って再帰的にフォルダを掘っていくサンプルコードやQ&Aサイトでの回答がありますが、全部、真似しないようにご注意ください。FSOだけを使っているサンプルプログラムや回答だけを拾ってください。FSOは、VBEのウォッチウィンドウなどでは機種依存文字を「?」で表示していますが、実際には、ちゃんと機種依存文字を認識してくれます。

そのあたりについて詳しく書いてあるWebサイトをご紹介します。
mhtmlなどで保存しておくことをおすすめします。
VBAでファイルリストを高速に取得する関数を自作する part1
VBAでファイルリストを高速に取得する関数を自作する part2
VBAでファイルリストを高速に取得する関数を自作する part3
VBAでファイルリストを高速に取得する関数を自作する part4

↑「ファイル・フォルダリストをゲットするだけ」というシーンでしか使えないかもしれませんが、Dir関数を使うよりも何倍も速いプログラムコードも紹介されています。
もちろん、文字コードの問題もありません。
ほんと、mhtmlなどで保存しておくことをおすすめします。

  

あと、コメント、大幅に削りました。
エラー処理も、On Error Resume Next でごまかすのをやめて、
いちおう、On Error Goto でやりました。
(結局、設計的に正しいやり方だったのかどうかはわかりませんけど・・・)

  

上記のコードを作るもとになったコード。
「GetFolderAndFileListSub()」にて、エラー処理をOn Error Resume Nextでごまかしていますので、あんまりいいコードではありません。

「GetFolderAndFileListMain02()」プロシージャだけを実行するのは同じです。

  
  

前項のコードのコメントほぼ無しバージョン。
「GetFolderAndFileListMain02()」プロシージャだけを実行するのは同じです。

これも「GetFolderAndFileListSub()」にて、エラー処理をOn Error Resume Nextでごまかしていますので、あんまりいいコードではありません。

  

OpenメソッドでいちいちExcelファイルを開いて処理した場合の例。
「Application.ScreenUpdating = False」を使っているのに、1.5~2倍くらい、遅いです。

ただ、シートの並びが「まんま」で出てくるのでわかりやすいです。
そのほうがいい場合は速度よりもこちらを選びます。

ただし、このコードはループ処理にDir関数を使っていて、
むやみに使うとトラブルを生む可能性がありますので、
実務にはDir関数を使わない他のサンプルコードを探すほうが
面倒に巻き込まれなくていいです。