はじめに
前回、バッチファイル〜PowerShellからマクロを実行するための内容を投稿しました。
↑「Ctrl+Vで普通に貼り付ければ良いのでは？」と勘違いされそうだと思ったので、最初に説明しておきます🙇♂️
実現したこと
- バッチ処理でWindowsエクスプローラーからクラウド上のファイル名一覧をコピー ◀︎別記事の投稿
- マクロ付きExcelファイル（.xlsm）のパスをPowerShellに引数として渡す ◀︎別記事の投稿
- PowerShellでマクロを実行する ◀︎別記事の投稿
- 取得したファイル名一覧をマクロでExcelのシートにコピペする ◀︎今回の投稿内容
作成したコード
Sub PasteClipboard()
'新しいシートを追加
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
'ブック内のすべてのシート名とインデックスを取得
Dim ws As Variant
For Each ws In Sheets
Debug.Print(ws.Name)
Debug.Print(ws.Index)
Next
'クリップボードの値を貼り付け
newSheet.Paste Destination:=newSheet.Range("A1")
'A列の幅を自動調整
newSheet.UsedRange.Columns("A:A").AutoFit
End Sub
コードの説明・補足
ThisWorkbook.Sheets
現在アクティブなワークブックのシートです。
今回の場合、マクロコードが実行されているシートにあたります。
Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
新しいシートをThisWorkbook.Sheetsの後に挿入します。
Worksheet.Add(Before, After, Count, Type)
引数の使い方
| 名前 | 必須/オプション | データ型 | 説明 |
|---|---|---|---|
| Before | 省略可能 | Variant | 指定したオブジェクトのシートの直前に、新しいシートを追加します。 |
| After | 省略可能 | Variant | 指定したオブジェクトのシートの直後に、新しいシートを追加します。 |
| Count | 省略可能 | Variant | 追加するシートの数を指定します。既定値は、選択したシートの数です。 |
| Type | 省略可能 | Variant | ワークシートの種類を指定します。 |
ThisWorkbook.Sheets.Count
現在のブック内のシートの合計数を取得します。
As Variant
データ型を宣言しない変数に使用するデータ型です。
Variant型の変数には異なる型のデータを代入することができます。
（例）
Dim a As Variant
' 数値の代入
a = 10
' 文字列の代入
a = "Hello, world!"
' 日付の代入
a = #12/31/2023#
For Each ws In Sheets
ブック内のすべてのシートをループで処理します。
For文とForEach文の違い
- For文→カウンター変数を1ずつ増やしながら繰り返し処理をする
- ForEach文→コレクション（配列、範囲など）の要素を一つずつ順番に処理する
For文
特徴
- 初期値、終了値を設定できるため、複雑な繰り返し処理もできる
- 配列の要素にアクセスするとき、インデックス番号で指定する
For カウンタ変数 = 初期値 To 終了値
繰り返し処理
Next
ForEach文
特徴
- インデックスを意識せずに直接要素へアクセスできる
- コレクションの要素をインデックスを使わずに直接、変数へ代入できる（コードが簡潔）
For Each 変数 In (コレクション)
繰り返し処理
Next
Excelでいう要素のインデックスとは、各セルの行番号と列番号に相当します。
（例） Cells(1, 1)は、A1セルを表します。
使い分け
- コレクションの全ての要素を処理したい→ForEach文
- 要素のインデックスが必要→For文
newSheet.Paste
クリップボードの内容をシートに貼り付けます。
このPasteメソッドが、クリップボードの内容を自動で調べて指定されたセルに貼り付けてくれます。
クリップボードの内容.Paste
Destination:=newSheet.Range("A1")
シートに貼り付ける時の引数を指定します。
Destination:＝貼り付け先のセル
UsedRange.Columns("A:A").AutoFit
UsedRange
指定したワークシートの使用範囲を表すRangeオブジェクトを返します。
言い換えると、データが入力されている範囲です。
UsedRangeで返されるケース
- データが入力されているセル: 数値、文字列、数式など
- 書式設定されているセル: フォントの色や背景色など
- 結合セル
UsedRangeで返されないケース
- 空のシート
AutoFit
範囲内にある列の幅もしくは行の高さを内容に合わせて調整します。
今回は指定したA列の幅を自動で調整しています。
追加の投稿（予定）
以下の処理を追加し、別の記事で投稿予定です
- 貼り付けられたファイル名一覧を加工する
- A列に貼り付けたファイル名「
No_シリアル.pdf」のような値を、「No.」と「シリアル」に分割する - 分割した文字列「
No.」と「シリアル」をそれぞれ別々の列（B列とC列）にコピペする - コピペした列の幅も自動調整する
- 重複があったセルを削除して、上に詰める
- A列に貼り付けたファイル名「
おわりに
今回のように自動化できそうな作業がまだ他にもありそうだなって考えられるようになりました。
何事も続けてみて、徐々にやれることを増やしていくのを習慣にし続けたいと思います。