はじめに
前回のクリップボードのファイル名一覧をペーストするマクロの続きの処理です。
実現したこと
- バッチ処理でWindowsエクスプローラーからクラウド上のファイル名一覧をコピー ◀︎別記事の投稿
- マクロ付きExcelファイル（.xlsm）のパスをPowerShellに引数として渡す ◀︎別記事の投稿
- PowerShellでマクロを実行する ◀︎別記事の投稿
- 取得したファイル名一覧をマクロでExcelのシートにコピペする ◀︎前回の投稿内容
- 各ファイル名の文字列を分割・重複削除する ◀︎今回の投稿内容
今回扱うデータ
- 「No_シリアル.pdf」のような形式のPDFファイル
（例）10_00001234.pdf
作成したコード
Sub PasteClipboard()
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long
For i=1 To lastRow
Dim fullName As String
Dim splitPos As Long
Dim leftStr As String
Dim rightStr As String
fullName = newSheet.Cells(i, 1).Value
splitPos = InStr(fullName, "_")
serialNumberStr = 8
If splitPos > 0 Then
leftStr = Left(fullName, SplitPos -1)
rightStr = Mid(fullName, SplitPos +1, serialNumberStr)
newSheet.Cells(i, 2).Value = leftStr
newSheet.Cells(i, 3).Value = rightStr
End If
Next i
newSheet.Columns("B:C").AutoFit
newSheet.Columns("B:C").RemoveDuplicates Columns:=Array(1, 2)
End Sub
処理の流れ
- 最終行を取得する
- シートのA列1行目から最終行まで以下のループ処理を順番に実行する
- 1行目のセルの値を
fullName変数に格納する - アンダーバー
_の文字位置をLong型で、splitPos変数に格納する - アンダーバー
_より左側の値をleftStr変数に格納する - アンダーバー
_より右側の8文字の値をrightStr変数に格納する - シートのB列1行目以降のセルに
leftStr変数の値を入力する - シートのC列1行目以降のセルに
rightStr変数の値を入力する
- 1行目のセルの値を
- B列〜C列の幅を自動調整する
- B列〜C列に重複した値があれば削除する
コードの補足
Cells(Rows.Count, 1).End(xlUp).Row
-
Cells(Rows.Count, 1)でA1列の最終行を選択 -
End(xlUp)でendボタン＋↑ボタンを押す -
Rowでその場所の行番号を取得する
Excelの操作上、行の途中に空欄が入っている時があると思います。
一旦シートの一番下（シートの最終行）のセルに移動してから、Ctrl + ↑とキーを押せば、値を含む最終行に移動できるのと同じです。
InStr([ start ], string1, string2, [ compare ])
ある文字列が最初に現れる位置を指定するVariant（Long）の値を返します。
| パーツ | 説明 |
|---|---|
| start | 省略可能。それぞれの検索の開始位置を設定する数値式です。省略すると、最初の文字の位置から検索が開始されます。 |
| string1 | 必須です。検索元の文字列式です。 |
| string2 | 必須です。検索場所となる文字列式を指定します。 |
| compare | 省略可能です。文字列比較の種類を指定します。 |
Left(string, length)
文字列の中の先頭から指定した文字数分だけ抽出します。
今回はアンダーバー_より左の文字数を指定し、抽出されます。
Mid(string, start, [ length ])
文字列から指定した文字数の文字列を抽出します。
| パーツ | 説明 |
|---|---|
| string | 必須。 文字列を返す 文字列式を指定します。 |
| start | 必須です。string内で取り出す文字列の先頭の位置を指定します。 |
| length | 省略可能。Variant (Long)。 返す文字数を指定します。この引数を省略すると、start引数[抜き出す開始位置]から後ろの値全部を返します。 |
今回は「No_シリアル.pdf」の.pdfは抽出したくないため、上記の作成したコードのようにserialNumberStr = 8のようにシリアルの文字数を指定しています。
RemoveDuplicates
値の範囲から重複した値を削除します。
newSheet.Columns("B:C").RemoveDuplicates Columns:=Array(1, 2)
Columns:=Array(1,2)は重複チェックする列の指定をしています。
Excelでは列インデックスは通常1から始まります。
そのため、今回の場合は配列の(1, 2)は範囲指定した列B〜Cを表しています。
結果
エクセルの画像などないと、イメージしづらいですが
実際には以下のように文字列が分割されます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 行1 | 10_00001234.pdf | 10 | 00001234 |
| 行2 | 11_00001235.pdf | 11 | 00001235 |
| 行3 | 12_00001236.pdf | 12 | 00001236 |
まとめ
今回は複数に分けて投稿したため長くなってしまいました🙇♂️
ですが、マクロを作成したときはこれまで手動で行なっていた作業を自動化できて達成感がありました。