レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

エクセルマクロ・VBA発展編１

小川慶一さんの投稿

(投稿ID: 4211)

以下の質問をもらいました。

やりたい作業処理は転記することですが、開きたいファイル 10個、開きたいシート50個あります。

この場合、Step_1→開きたいシート50個のシート名を取得する。例えば、WorkSheets.Countで書き出す。
Step_2→取得したシート名を発展2_Chap02-11.xlsのように作業処理すればよいといます。

この場合は静的配列Dim stAry(5) As Stringですが、これを動的配列にして、すべてのファイル10個、すべてのシート50個を連続処理することは可能でしょうか？

つづけて、返信の形で回答さしあげたいと思います。

2018/10/22 19:41

小川慶一さんのコメント

(コメントID: 5772)

小川慶一さん:

[1] ファイルが10個ある
[2] それぞれのファイルに、シートが50個ある

ということですね。
そもそも配列はいらないかと。

ハナコのステップに従い、内側からやります。
ということで、まずは、1つのシートについて50個のシートを連続処理するマクロ。これは For Each構文を使えば、シートがいくつあっても関係ありません。

'マクロのあるファイルと同じフォルダにFile0.xlsxがあるという前提で
Sub Hanako1()
    Dim b As Workbook
    Dim w As Worksheet
    
    Set b = Workbooks.Open(Filename:=ThisWorkbook.Path & "\File0.xlsx")
    For Each w In b.Worksheets
        '処理
        Debug.Print b.Name & vbTab & w.Name
    Next
    b.Close
End Sub

続けて、ファイルを順番に開くマクロ。
ファイル名は決め打ちで良さそうな雰囲気でしたので、エクセルシートに記載しました。「所定のフォルダにあるファイルすべてのリストを取得したい」等ありましたら、外部連携講座をどうぞ。

'マクロのあるファイルのシート「List」のセルB2-B11に開くべきファイルのリストがあるという前提で
Sub Hanako2()
    Dim b As Workbook
    Dim w As Worksheet
    
    Dim gyo As Long
    Dim bName As String
    For gyo = 2 To 11
        bName = ThisWorkbook.Worksheets("List").Range("B" & gyo).Value
        Set b = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & bName)
        For Each w In b.Worksheets
            '処理
            Debug.Print b.Name & vbTab & w.Name
        Next
        b.Close
    Next
End Sub

2018/10/22 19:51

受講生さんのコメント

(コメントID: 5778) 添付ファイルのダウンロード権限がありません

前回、複数ブック、複数シートの一括処理について教えて頂き、ありがとうございました。内部処理を行ってみましたが、ワークシート内の繰り返し処理ができません。私が作ったテキストとデータを送付させて頂きますので見て頂けないでしょうか。よろしくお願いいたします。

2018/10/31 08:06

受講生さんのコメント

(コメントID: 5781)

受講生さん

受講生さん

ファイル、拝見しました。
フォルダ「Sub」内のファイルはすべて以下の法則だ、という理解でよいでしょうか。

・シート「全体集計」は調査対象ではない。
・シート「全体集計」以外はすべて調査対象
・調査対象のシートでは、セルB28が表タイトルの左端
・調査対象のシートでは、調査対象のデータがあるのは最大でも42行目まで
・調査対象のシートでは、調査対象のデータがあるのはB列-I列まで
・調査対象のシートのB列, C列の値がそれぞれ転記先のB列、C列に来る。以下、D列, E列がB列、C列、F列, G列がB列、H列、I列, E列がB列、C列
・転記の際に特別な加工は不要。たとえば、氏名が「ＡＡ　ＦＦ」だったとして、「ＡＡ」しか取ってこないといったことはなく、単純に元データを転記してくるだけ

それから受講生さんのスキル次第で何をどこまで示せばよいのか？が変わります。
ついては、自力で以下をできるか？教えてください。

[1] 以下の3つの問題をノーヒントで解いて、それぞれ、何分かかりますか。

・条件に一致するデータを別表に転記する。AutoFilterより便利な条件抽出マクロ。
https://forum.pc5bai.com/lesson/page/144/

・条件に一致するデータを別シートに転記する
https://forum.pc5bai.com/lesson/page/145/

・複数の表の間でのマッチング。VLookUp関数がやっていることをマクロで書くと？
https://forum.pc5bai.com/lesson/page/153/

[2]ファイル「2018年度　ＡＢＣグループメンバー表.xlsx」のシート「大文字2」のセルB29の値を転記先シートのセルB2に転記する
[3]ファイル「2018年度　ＡＢＣグループメンバー表.xlsx」のシート「大文字2」のセルB29-B42の値を転記先シートのセルB2以降に転記する
[4]ファイル「2018年度　ＡＢＣグループメンバー表.xlsx」のシート「大文字2」のセルB29-B42, D29-D42, F29-F42, H29-H42, の値を転記先シートのセルB2以降に転記する(たとえ空白でも)
[5]ファイル「2018年度　ＡＢＣグループメンバー表.xlsx」のシート「大文字2」のセルB29-B42, D29-D42, F29-F42, H29-H42, の値を転記先シートのセルB2以降に転記する(空白の場合はスキップ)
[6]ファイル「2018年度　ＡＢＣグループメンバー表.xlsx」のシート「全体集計」以外のシートすべてで[5]を実現できる
[7]転記先ファイルのシート「List」に記されたすべてのファイルに対して[6]を実現できる

実務に関する具体的な相談へのサポートは、以下の「サポートの方法及び利用時の注意点について」に基づいて行っています。
念のため、改めてご確認ください。
https://www.exvba.com/support.php#hoshin

また、もしも上に紹介したページの内容について相談等ありましたらお知らせください。

僕にとっては、質問対応の過程で教材を作ることができればほかの受講生の方にも役立つので、そうしたいと思っています。
ファイルのプロパティ等まで確認しましたが、特に個人を特定できそうな情報はなさそうでした。
なので、お預かりしたファイルと質問内容をそのまま動画教材の形にしてお返しし、今後の教材としても活用させていただければいちばん効率的で理想です。

> 前回、複数ブック、複数シートの一括処理について教えて頂き、ありがとうございました。内部処理を行ってみましたが、ワークシート内の繰り返し処理ができません。私が作ったテキストとデータを送付させて頂きますので見て頂けないでしょうか。よろしくお願いいたします。

2018/11/02 03:56