質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.51%

  • VBA

    2308questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1924questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Windows

    1708questions

    Windowsは、マイクロソフト社が開発したオペレーティングシステムです。当初は、MS-DOSに変わるOSとして開発されました。 GUIを採用し、主にインテル系のCPUを搭載したコンピューターで動作します。Windows系OSのシェアは、90%を超えるといわれています。 パソコン用以外に、POSシステムやスマートフォンなどの携帯端末用、サーバ用のOSもあります。

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

解決済

回答 3

投稿

  • 評価
  • クリップ 1
  • VIEW 3,163

Kussy-

score 49

いつも丁寧に教えていただき感謝いたします。
今回は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
も見ましたが、よく理解することが出来ませんでした。

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+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


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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/16 15:54

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

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

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

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


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

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

    キャンセル

+1

>Set xlBook = CreateObject("Excel.Workbook") 
何をしたいかが、書かれていません。
1.新規にブックを作成したい、
検索ワード: EXCEL VBA ブック作成
2.ブックを開きたい
検索ワード:  EXCEL VBA ブック 開く


EXCEL、EXCEL-VBA独習用に役立つサイト例
’ 検索ワード
Excelでお仕事!
OFFICE TANAKA
VBレスキュー花ちゃん
Excel 大辞典

moug 過去ログ

掲示板頼みで御仕事を終わらせても、
後で過大評価と無能の法則が待っていないとよいですがねぇ。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/16 15:26

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

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

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

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

    キャンセル

  • 2016/06/16 16:01 編集

    >仕事の内容を掲示板などで調べるのは、NGということでしょうか。
    などという意味で、書いてはいません。
    検索ワードを示したのも、検索してねという、趣旨ですし。
    teratail での過去の御自身の質問を見なおしてください。
    調べれば解る、初心者的、「教えてください」の連続ですよね?
    ⇒解らないから質問しているは、有です。その為の掲示板ですから、
     しかし、調べる、学習する手段も能力も御持ちなのに、
     教えての連続では、自身の能力向上に成りますか?

    杞憂かもしれませんが、
    TPP発効後は、来歴不明のコードに対する、
    暇な米国弁護士による、濫訴が起きるかもしれません。
    ネット上のコードの流用などは、仕事用コードでは避けておいた方が無難です。
     
     

    キャンセル

  • 2016/06/16 16:51

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

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

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

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/16 15:45

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

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

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

    キャンセル

同じタグがついた質問を見る

  • VBA

    2308questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

  • Excel

    1924questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • Windows

    1708questions

    Windowsは、マイクロソフト社が開発したオペレーティングシステムです。当初は、MS-DOSに変わるOSとして開発されました。 GUIを採用し、主にインテル系のCPUを搭載したコンピューターで動作します。Windows系OSのシェアは、90%を超えるといわれています。 パソコン用以外に、POSシステムやスマートフォンなどの携帯端末用、サーバ用のOSもあります。