About
このページで学習出来る事
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
コードの解説 変数設定
‘エクセルアプリケーションを使用する宣言をしています。
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
コードの解説 セル値の取得と配列への代入。エクセルファイルの読み込み
‘[Sheet1]のセル値1行～3行目（新規に作成されるエクセルファイルの名前が記載されています）を配列に格納しています。
‘[arrayText.Add]の記述で配列に値を入れています。
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”)
コードの解説 新規ブックとシートの作成。エクセルファイルの保存
‘配列最後までループを実施する処理です。
For Each strText In arrayText
‘エクセルアプリケーションから新規ブックを追加しています。
Set objWorkbook = objXlsApp.Workbooks.Add
‘コピー元の(i)番目のシートをシートオブジェクトに読み込んでいます。
‘そして[UsedRange.Copy]にて、読み込んだシート内のセル値をコピーしています。
Set objWorksheetSource = objWorkbookSource.Worksheets(i)
objWorksheetSource.UsedRange.Copy
‘作成した新規ブックに対して新しいシートを5個追加しています。
‘そして追加したシートの名前を変更しています。
Set objWorksheet = objWorkbook.Worksheets.Add
objWorksheet.Name = “作ったシート–” & i
‘作成したシートに先ほどコピーした内容を、[A1]に対して貼り付け処理をしています。
objWorksheet.Range(“A1”).PasteSpecial xlPasteAll
‘配列の値をファイル名として[C:\temp\]に保存処理をしています。
objWorkbook.SaveAs (“C:\temp\” & strText & “.xlsx”)
objWorkbook.Close
各オブジェクトと変数の確認
[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番目)]をコピーして貼り付け処理をしていますので、それぞれの番号のシート内容がコピーされることとなります。
今回はシートの中身を転記する方法として、[UsedRange.Copy]と[PasteSpecial xlPasteAll]を使用しています。
objWorksheetSource.UsedRange.Copy
objWorksheet.Range(“A1”).PasteSpecial xlPasteAll
他の方法としては[For]文を使用する方法がありますが、コピー元の行数や列数を確認する必要があり、記述内容を多くなってしまいます。
その点[UsedRange.Copy]であれはシート内で「使用している範囲」を全てコピーしてくれますので、とんでもない大きい情報を扱わない限り、Excelの機能を使用した処理の方が便利な場合もあります。
最後に、開いている[北海道.xlsx]をいったん閉じて、処理が終わるまで実行してみましょう。そして、処理が終わったら再度[C:\temp]フォルダを確認してください。3個のエクセルファイルが出来ているがわかります。
また[北海道.xlsx]同様に、他のエクセルファイルも開いて、問題なく内容が書き込まれているか確認してみましょう。
最後に
今回は前回のコードに少し手を加えた物になりますが、逆に言えばブックやシートオブジェクトを正しく使えば、少し手を加えただけでこの様な処理が出来るという事になります。
これまでの内容でブックやシートオブジェクトを使い方を説明してきましたが、それぞれのオブジェクトとエクセルの機能を適切に使用することで、シンプルなコードを書くことが出来るようになります。
もし、今までにブックやシートを操作するVBAを作ってこられた方がいましたら、一度見直してみてオブジェクトに置き換えれそうな所があるか確認してみましょう。
そして練習のため、一度置き換えをしてみて、その違いを確認してみるのも勉強の一つになります。
次回からは、一旦ブックやシート操作から離れて、関数操作の方法を説明していきたいと思います。
このページで学習出来る事 VBAトレーニング4回目となる今回は、前回に引き続きExcelブックとシートの使い方について説明をしていきたいと思います。 ただ単純にシートを切り替えたり、削除したりするコードを覚えるのではなくブック[…]
このページで学習出来る事 前回まではブックやシートオブジェクトの操作に関して解説をしてきましたが、6回目となる今回は、VBAの関数操作に関して説明をしていきます。 一般的にコードは書けば書くほど行数が長くなっていきますが、プロ[…]