VBA ファイル名一覧からシート一覧の作成

Question

いつも丁寧に教えていただき感謝いたします。
今回はExcel2010のVBAについて教えて下さい。

###前提・実現したいこと
Excelブックの一覧を元に、ブックを開かずに(画面を表示せずに)全てのシート名を抽出し、
シート名を含めた一覧を作成したいです。

Excelのマクロブックにあるシート"FileList"には
A列に確認したいブックが置かれているフォルダのフルパス
B列に確認したいブック名
がそれぞれ記載されています。
1行目はカラムなので実際のデータは2行目から始まります。

上記の情報を元に、ブックが開かれている事を表示せずに
同じマクロブックにあるシート"Sheets"の2行目から
以下の様な表を起こしたいです。

A列 B列 C列
フォルダパス ファイル名 シート名
C:\test TEST1.xlsx Sheet1
C:\test TEST1.xlsx Sheet2
C:\test TEST1.xlsx Sheet3
C:\test Book1.xlsx Top
C:\test Book1.xlsx TestSheet
<<以下略>>

###発生している問題・エラーメッセージ
VBAを作成してみたのですが、

実行時エラー'424':
オブジェクトが必要です。

というエラーが出てしまいます。

###該当のソースコード

Sub SheetName()
    'シート含めたブックリスト作成

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook        ' 対象ファイルのファイル名
    Dim xlSheet As Excel.Worksheet      ' 取得したシート名
    Dim b As Integer                    ' Book数=b
    Dim s As Integer                    ' 各BookのSheet数=s
    Dim i As Integer                    ' シート確認結果入力時の行数
    Dim BookCnt As Integer              ' ブック数カウント値
    Dim SheetCnt As Integer             ' シート数カウント値
    Dim filePath As String              ' 対象ファイルのパス部分
    Dim fullPath As String              ' 対象ファイルのフルパス

    Set xlApp = CreateObject("Excel.Application")

Sheets("FileList").Select
BookCnt = Range("A2").End(xlDown).Row  ' 対象ファイル数確認(行数確認)

i = 0

For b = 0 To BookCnt
    Sheets("FileList").Select
    Range("A2").Select


    filePath = ActiveCell.Offset(b, 0).Value
    Set xlBook = ActiveCell.Offset(b, 1).Value


        SheetCnt = (filePath + "\" + xlBook.Sheets.Count) ' 対象ファイルシート数カウント
        For s = 1 To SheetCnt  ' 対象ファイル数確認(行数確認)

            xlSheet = Sheets(s).Name

            Sheets("Sheets").Select
            Range("A2").Select
        
        With ActiveCell ' 結果をセルにデータ書き込み
            .Offset(i, 0).Value = filePath
            .Offset(i, 1).Value = xlBook
            .Offset(i, 2).Value = xlSheet
        End With
            
        i = i + 1
        Next s

Next b

End Sub

###試したこと
エラーの内容についてはWebや過去の投稿を検索してみましたが
コレという内容がわかりませんでした。

Set xlApp…の下の行に以下を追加
Set xlBook = CreateObject("Excel.Workbook")
すると以下のエラーが出ます。
実行時エラー'429':
ActiveX コンポーネントはオブジェクトを作成できません。

https://teratail.com/questions/25735
も見ましたが、よく理解することが出来ませんでした。

申し訳ありませんが、本件解決のお知恵をお貸しいただきたく投稿させていただきます。
情報の過不足が有りましたら捕捉いたしますのでコメントにてお願い致します。

Answer 1

問題がいくつかあるようです。

①xlBook変数やxlSheet変数に、ブック名やシート名の文字列を代入している。
②対象ファイル数の数え方が誤っている。
③ループ処理の記述誤りにより必要以上のループ回数となっている。

まず、今回エラーとなっていると言われているのは①の部分だと思います。
Excel.Workbook型にブック名を代入しても、そのブックを参照することはできません。
これはExcel.Worksheet型も同様です。

上記を修正しても、②・③の問題により過剰なループ処理が行われ、未入力のセルを元にファイル参照してしまうことになります。

以下、上記の修正案です。

Sub SheetName()
    'シート含めたブックリスト作成

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook        ' 対象ファイルのファイル名
    Dim xlSheet As Excel.Worksheet      ' 取得したシート名
    Dim b As Integer                    ' Book数=b
    Dim s As Integer                    ' 各BookのSheet数=s
    Dim i As Integer                    ' シート確認結果入力時の行数
    Dim BookCnt As Integer              ' ブック数カウント値
    Dim SheetCnt As Integer             ' シート数カウント値
    Dim filePath As String              ' 対象ファイルのパス部分
    Dim fullPath As String              ' 対象ファイルのフルパス

    Set xlApp = CreateObject("Excel.Application")

    Sheets("FileList").Select
    
    'BookCnt = Range("A2").End(xlDown).Row ' 対象ファイル数確認(行数確認)
    '●↑これで取得できるのは最終行番号です。
    '●対象件数を知りたいのであれば↓
    BookCnt = Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count ' 対象ファイル数確認(行数確認)
    
    i = 0
    
    '●0から回すのであれば件数-1とする必要があります。
    'For b = 0 To BookCnt
    For b = 0 To BookCnt - 1
        
        '●変数fullPathが使用されていなかったので使ってみました
        'Sheets("FileList").Select
        'Range("A2").Select
        'filePath = ActiveCell.Offset(b, 0).Value
        filePath = Sheets("FileList").Range("A2").Offset(b, 0).Value
        fullPath = filePath & "\" & Sheets("FileList").Range("A2").Offset(b, 1).Value
        
        '●Excel.Workbook型の変数にファイル名を値としてセットしても、ブックは参照できません。
        'Set xlBook = ActiveCell.Offset(b, 1).Value
        Set xlBook = xlApp.Workbooks.Open(fullPath)
        
        '●シート数の取得とファイルパスの作成がごちゃ混ぜになっている？？
        'SheetCnt = (filePath + "\" + xlBook.Sheets.Count)  ' 対象ファイルシート数カウント
        SheetCnt = (xlBook.Sheets.Count)  ' 対象ファイルシート数カウント
        For s = 1 To SheetCnt  ' 対象ファイル数確認(行数確認)

            '●シート名を値としてセットしても、シートは参照できません。
            '●また、特に指定なくSheetsを扱った場合、現在アクティブなシートが参照されます。
            'xlSheet = Sheets(s).Name
            Set xlSheet = xlBook.Sheets(s)

            '●毎回アクティブシートやアクティブセルを変更するのは重くなる元なので、見せる必要がなければ直接参照します。
            'Sheets("Sheets").Select
            'Range("A2").Select
            'With ActiveCell ' 結果をセルにデータ書き込み
            '    .Offset(i, 0).Value = filePath
            '    .Offset(i, 1).Value = xlBook
            '    .Offset(i, 2).Value = xlSheet
            'End With
            With Sheets("Sheet2").Range("A2") ' 結果をセルにデータ書き込み
                .Offset(i, 0).Value = filePath
                .Offset(i, 1).Value = xlBook.Name
                .Offset(i, 2).Value = xlSheet.Name
            End With
    
            i = i + 1
        Next s
        
        xlBook.Close False
    
    Next b

End Sub

改修ポイントに●をつけてありますので参考としてください。

Comments

[Kussy-]

丁寧な回答を頂き、誠にありがとうございます。

こちらに投稿後も引き続き試行錯誤しており、ご回答いただいている通り
　> Excel.Workbook型の変数にファイル名を値としてセットしても、ブックは参照できません。
のところの認識誤りに気づきようやくできるようになったのですが、
頂きました回答のコードでより一層綺麗なコードにすることが出来ました。

SheetCnt = (filePath + "\" + xlBook.Sheets.Count)
の部分については、()の中に"[フルパス].Sheets.Count"が必要だと思ったので、結果めちゃくちゃなコードになってしまいました。

また、
　> 毎回アクティブシートやアクティブセルを変更するのは重くなる元
ということも知ることが出来、他のコードでも同じような部分があるので、早速修正していきたいと思います。

わざわざコード例を作って回答頂きありがとうございました。
わかりやすい解説付きのコード例を含めて回答をしていただきましたので、
本件のベストアンサーとさせていただきます。

今回は誠にありがとうございました。
また何か投稿させていただいた際はよろしくお願い致します。

Answer 2

新規にワークブックを作りたいのであれば、ここのページを参考にしてください。
https://msdn.microsoft.com/ja-jp/library/x80526fk(v=vs.71).aspx

既にあるExcelブック（ファイル）のオブジェクトを表したいのであれば、前回の質問の回答を参考にしてください。
https://teratail.com/questions/37322

Comments

[Kussy-]

今回は既にあるブックについての操作でした。

オブジェクトのそもそもの意味合い(使い方)を勘違いしていたようです。
以前の投稿に頂いた回答を元に、改めて見なおしてみました。
おかげさまでやりたかったことができるようになりました。

度重なるお力添え、大変感謝いたします。
誠にありがとうございました。

Answer 3

Set xlBook = CreateObject("Excel.Workbook")

何をしたいかが、書かれていません。
１．新規にブックを作成したい、
検索ワード： EXCEL VBA ブック作成
２．ブックを開きたい
検索ワード： EXCEL VBA ブック 開く

’
ＥＸＣＥＬ、ＥＸＣＥＬ－ＶＢＡ独習用に役立つサイト例
’ 検索ワード
Excelでお仕事！
OFFICE TANAKA
VBレスキュー花ちゃん
Excel 大辞典
’
moug 過去ログ
’
掲示板頼みで御仕事を終わらせても、
後で過大評価と無能の法則が待っていないとよいですがねぇ。

Comments

[Kussy-]

回答頂きありがとうございます。

そもそもオブジェクトの考え方に誤りがあったようです。
おかげで誤認識を正すことが出来ました。
ありがとうございます。

ご提示いただいたサイトも、ここに投稿する前に
自分で調べて読んでいた時はピンとこなかったのですが、
全ての辻褄があい、合点がいくことが出来ました。

迷惑ついでと言ってはなんですが、もう一つ教えて下さい。
　> 掲示板頼みで御仕事を終わらせても、
　> 後で過大評価と無能の法則が待っていないとよいですがねぇ。
とのお言葉を頂戴していますが、仕事の内容を掲示板などで調べるのは
NGということでしょうか。
今回に限らずWeb上のブログやMSのテックなどで調べて仕事してしまっているのですが。
私は知らなかったのですが、マナーなのだとしたら大変申し訳ありません。
失礼いたしました。

[daive]

>仕事の内容を掲示板などで調べるのは、NGということでしょうか。
などという意味で、書いてはいません。
検索ワードを示したのも、検索してねという、趣旨ですし。
teratail での過去の御自身の質問を見なおしてください。
調べれば解る、初心者的、「教えてください」の連続ですよね？
⇒解らないから質問しているは、有です。その為の掲示板ですから、
　しかし、調べる、学習する手段も能力も御持ちなのに、
　教えての連続では、自身の能力向上に成りますか？
’
杞憂かもしれませんが、
ＴＰＰ発効後は、来歴不明のコードに対する、
暇な米国弁護士による、濫訴が起きるかもしれません。
ネット上のコードの流用などは、仕事用コードでは避けておいた方が無難です。

[Kussy-]

そういう意味合いだったんですね、失礼しました。

ご指摘の通り投稿させていただく質問については初心者的で｢教えて下さい｣というスタンスが多いです。
ただ、本件投稿にもある通り、投稿前に自分なりではありますが、Webや書籍・過去投稿などで調べ、
それでもわからない時に投稿させていただいております。
今回の件でもご提示いただいたキーワードの"Excelでお仕事！"以外は検索しておりますし、
それ以外についても検索しております。ex.Excel2010 VBA シート名 等

投稿させていただく際は、こんなにWebや書籍などに情報があってもわからないので
私の勉強不足と考え、初心者としてdaive様含め諸先輩方や有識者の方へ
投稿を通してお力添えをお願いさせていただいており、自分で調べても
分からない部分を教えて頂くので｢教えて下さい｣という投稿をさせていただいております。
勿論投稿後も投げっぱなしではなく引き続き自分でも調べております。
今回も投稿後に自分の誤認識している箇所に気付き、解決することが出来ました。

投稿の仕方に問題があり、不快な思いをさせてしまったのでしたらこの場を借りて謝罪いたします。
申し訳ありませんでした。
また、貴重なご回答有り難うございました。