2014/03/01

【VBA】Excelのシート名の一覧を取得し、変換する

WS000029
【VBA】Excelのシート名を一括置換するというエントリを投稿したところ、『VBAマクロ書かなくても、ソフト使えば一括変換できるよ』的なコメントを頂いた。(ちょっとスパムっぽかったけどw)

以前のエントリは文字列置換を用いたシート名の変換だったが、教えてもらったものは「今のシート名」と「変更後のシート名」を一覧にして一括で置換するものだった。


便利そう!


でもシステム屋さんはお客様のPCを借りたり、サーバなど好き勝手インストールできない環境で仕事をしているので、インストールが必要なソフトは使えない。


でも、便利そう!!

これは、私のパーソナルマクロ集に加えたい!!!



ってことで、VBAマクロで再現してみた。


シート名の一覧を取得する



まずは現在のシート名の一覧を取得する。
Sub シート名取得()
    Dim ws As Worksheet
    Dim i As Long
    i = 0
    
    For Each ws In Worksheets
        Cells(Selection.Row + i, Selection.Column) = ws.Name
        i = i + 1
    Next
End Sub

Worksheetsをフェッチして、そのWorksheetのNameを取得して選択行から下に表示していく。
※選択行の下に文字があっても上書きされる。しかも、マクロで変更した箇所はUndoできないので要注意!


追記:2015/04/25 17:00
シート名取得の6行目と7行目の間に、
Cells(Selection.Row + i, Selection.Column).NumberFormatLocal = "@"

を追加することで、セルの書式を"文字列"にできる。
シート名がゼロ始まりや演算子始まりの場合は、NumberFormatLocalに@(アットマーク)を指定することで、頭落ちせずシート名が取得できる。

ys さんに教えていただきました。ありがとうございました。


追記:2015/07/14 20:00
グラフシートの名前が取得・置換できないとのコメントをいただいた。
今回のVBAでは「Worksheets」のみを対象にしているため、グラフシートの名前の取得、置換ができない。
グラフシートを表すオブジェクトは「Charts」のため、以下の内容を9行目と10行目の間に書き加えると実現できる。
グラフシート名の置換は次の章で...

Dim graph As Chart
For Each graph In Charts
    Cells(Selection.Row + i, Selection.Column) = graph.Name
    i = i + 1
Next




上記マクロを実行すると以下のようにシート名が取得できる。

WS000025



シート名を一括で変換する



先ほどのマクロでシート名一覧を取得し、その右隣の列に「変更後のシート名」を書いておく。
WS000027

そして、変換前と後のシート名を範囲選択してから、以下のマクロを実行すると……
Sub シート名変換()
    Dim ws As Worksheet
    Dim row, col As Long
    Dim wsNames As Collection
    
    Set wsNames = New Collection
    
    row = Selection.row
    col = Selection.Column
    
    Do While Not IsEmpty(Cells(row, col))
        ' KeyがStrig型でないと「型が一致しません」というエラーになる
        wsNames.Add Item:=Cells(row, col + 1).Value, _
                     Key:=CStr(Cells(row, col).Value)
        row = row + 1
    Loop
    
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.name = wsNames.Item(ws.name)
    Next
End Sub

WS000028

こんな感じでシート名が一気に変わってくれる。

ちょいと面倒だが、1つ目のループで「wsNames」というコレクションに「変換前のシート名」と「返還後のシート名」を格納していく。
2つ目のループでKeyに変換前のシート名を渡して、Itemの変換後のシート名を取得し、シート名を書き換えている。



追記:2015/07/14 20:00
グラフシート名の置換は、以下の内容を21行目と22行目の間に書き加えることで実現できる。
Dim graph As Chart
For Each graph In ActiveWorkbook.Charts
    On Error Resume Next
    graph.name = wsNames.Item(graph.name)
Next



追記:2014/05/27
【VBA】Excelのシート名を一括置換する も合わせでどうぞ!


以上

9 件のコメント :

  1. 毎月煩わされていたシート名の変換作業が少しの手間で可能になり、大変役に立ちました!
    素晴らしい記事をありがとうございます。

    返信削除
    返信
    1. そういっていただけるとブログをやってて本当に良かったって思います。
      こちらこそ、ありがとうございました!

      削除
  2. 今、仕事でシート名を一気に変換することが多いので、役立ちます。
    このままだと0303001みたいなシート名は303001と取得されるので、
    シート名一覧取得の6行目と7行目の間に
    Cells(Selection.Row + i, Selection.Column).NumberFormatLocal = "@"
    を入れるのはどうですか?

    返信削除
    返信
    1. コメントありがとうございます!
      ゼロ開始のこと完全に考慮していませんでした。
      たしかにExcelならyyMMddでシートつくることありますね!

      エントリに追記いたしました。

      削除
  3. 現在、やりたい事がこちらのマクロにて解決出来そうなのですが
    一点、グラフが入っているシートが存在している場合には
    上記VBAでは対応出来ないのですがどこを修正すれば良いでしょうか?

    返信削除
    返信
    1. えっと、、、シート名を一括変換したときに、グラフが参照しているシート名も変わってしまって、グラフに値が反映されないということでしょうか?

      例えば、[集計シート]ってシートに値があって、それを参照してグラフを作っている。
      その[集計シート]を[データシート]って名前に変えた時に、グラフの参照先シートが見つからなくて値が反映されない。

      みたいなことでしょうか?


      私の環境(Win7 Office2007)だと、当記事のマクロで名前を変換したところ、ちゃんと参照先も自動で変わってくれました。ちなみにグラフは、リボンの[挿入]にある[グラフ]でつくりました。

      もう少し具体的な情報をいただけると、こちらで確認することもできるのですが…。

      削除
    2. B.C.Rikko様
      御連絡ありがとうございます
      説明不足で申し訳ありません

      シートを10枚ほど作成しておりますが
      そのうち5枚をグラフ表示のみVBAでは
      ”sheet”ではなく”graph”で表示されている
      シートになります。

      ※シート内にグラフがあるのではなく、シート全体がグラフとなっている物になります

      誠に申し訳ありませんがお手隙の時に御確認を御願い致します

      削除
    3. 情報ありがとうございます。

      さっそく試してみたところ、うまくいったので当記事の内容を更新いたしました。
      「追記:2015/07/14 20:00」のところを参照いただければと思います。(2カ所あります)

      もし、これでもうまくいかない場合は……また、相談してください!

      削除
    4. B.C.Rikko様

      確認出来ました
      ありがとうございます

      削除