VBAトレーニング-05　[ブック＆シート操作について-⑤]-[シートの転記方法]

このページで学習出来る事

　VBAトレーニング5回目となる今回は、前回に引き続きExcelブックとシートの使い方について説明をしていきたいと思います。

　ただ単純にシートを切り替えたり、削除したりするコードを覚えるのではなくブックやシートオブジェクトを見ながら、実際にエクセルファイルを作成したりその中にあるシートを操ってみたいと思います。

準備

　今回は[C:\temp]以下に自動でエクセルファイルを作成しますので、あらかじめ[C:\temp]フォルダを準備しておきます。（サンプル用のzipファイルを用意してありますので、それを解凍してCドライブにコピペしてください）
　下記図のようになるよう設置してください。

サンプルコード

　zipファイルに含まれる[sample-05.xlsm]を開いて、中に書かれているコードを確認しましょう。
　今回のコードでは、新規にエクセルファイルとシートを作成し、[source.xlsx]から読み取ったシートを張り付けるという内容が書かれています。

　このファイルをそのまま使用しても構いませんし、新しくエクセルファイルを作って、自身でコードを記載して動かしても構いません。（直接コードを書いた方が覚えやすいので、時間がある方は書く練習もしてみましょう）

Sub main()
    Dim objXlsApp As New Application
    Dim arrayText As New Collection
    
    Dim objWorkbookSource As Workbook
    Dim objWorksheetSource  As Worksheet
    Dim objWorkbook As Workbook
    Dim objWorksheet  As Worksheet
    Dim i As Integer

    '[Sheet1]のセル値を配列に設定しています。
    Set objWorkbook = Application.ActiveWorkbook
    Set objWorksheet = objWorkbook.Worksheets(1)
    For i = 1 To 3
        arrayText.Add (objWorksheet.Cells(i, 1))
    Next i
    
    
    '[source.xlsx]を[objWorkbookSource]に読み込んでいます。
    Set objWorkbookSource = objXlsApp.Workbooks.Open("C:\temp\source.xlsx")
    Debug.Print "wait"
    
    '配列をループさせながらエクセルファイルをシートを作成しています。
    For Each strText In arrayText
        Set objWorkbook = objXlsApp.Workbooks.Add
        
        For i = 1 To 5
            Set objWorksheetSource = objWorkbookSource.Worksheets(i)
            objWorksheetSource.UsedRange.Copy
            
            Set objWorksheet = objWorkbook.Worksheets.Add
            objWorksheet.Name = "作ったシート--" & i
            objWorksheet.Range("A1").PasteSpecial xlPasteAll
        Next i
        
        objWorkbook.SaveAs ("C:\temp\" & strText & ".xlsx")
        objWorkbook.Close
        Debug.Print "wait"
    Next strText

End Sub

コードの解説　変数設定

コードの解説　セル値の取得と配列への代入。エクセルファイルの読み込み

コードの解説　新規ブックとシートの作成。エクセルファイルの保存

各オブジェクトと変数の確認

　[Debug.Print]の所でブレークポイントを設定して実行してみましょう。
　ブレークポイントで止まったところで、設定した変数に「開いているブックオブジェクト」、「[Sheet1]のシートオブジェクト」、「セルの値を入れた[arrayText]の配列」、「コピー元となるエクセルファイルのオブジェクト」がそれぞれ確認できると思います。

　ローカルウィンドウの[+]を押して、オブジェクトや配列の中身を確認していきましょう。

新規エクセルブックの作成と確認

　変数の確認が終わったら、ブレークポイントで止まっている状態から再度動かしてみましょう。
　次のブレークポイントで止まった状態で、[C:\temp]フォルダを開くと、[北海道.xlsx]というエクセルファイルが作成されているはずです。
　この処理は[Set objWorkbook = objXlsApp.Workbooks.Add]で新規ブックが作成され、[objWorkbook.SaveAs (“C:\temp\” & strText & “.xlsx”)]にて、ファイルが保存されいるためです。

新規シート作成と値の確認

　次にこの状態で[北海道.xlsx]を開いてみましょう。
　新規にシートが5個作成されており、それぞれのシートに[source.xlsx]の中身がコピーされていると思います。
（[source.xlsx]-[Sheet1]の内容が、新規シートの[作ったシート–1]にコピーされていれば成功です）

　今回は[For i = 1 To 5]にて、5個の新規シートを作成しながら[source.xlsx]の[Sheet(i番目)]をコピーして貼り付け処理をしていますので、それぞれの番号のシート内容がコピーされることとなります。

　最後に、開いている[北海道.xlsx]をいったん閉じて、処理が終わるまで実行してみましょう。そして、処理が終わったら再度[C:\temp]フォルダを確認してください。3個のエクセルファイルが出来ているがわかります。
　また[北海道.xlsx]同様に、他のエクセルファイルも開いて、問題なく内容が書き込まれているか確認してみましょう。

最後に

　今回は前回のコードに少し手を加えた物になりますが、逆に言えばブックやシートオブジェクトを正しく使えば、少し手を加えただけでこの様な処理が出来るという事になります。

　これまでの内容でブックやシートオブジェクトを使い方を説明してきましたが、それぞれのオブジェクトとエクセルの機能を適切に使用することで、シンプルなコードを書くことが出来るようになります。

　もし、今までにブックやシートを操作するVBAを作ってこられた方がいましたら、一度見直してみてオブジェクトに置き換えれそうな所があるか確認してみましょう。
　そして練習のため、一度置き換えをしてみて、その違いを確認してみるのも勉強の一つになります。

　次回からは、一旦ブックやシート操作から離れて、関数操作の方法を説明していきたいと思います。