次のファイル名を作る

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

タイトルの「次のファイル名を作る」を見て、"ちょっと何言ってるか分からない"と、富沢みたいに感じた方もいるでしょう。要するに、こういうことです。

すでにいくつかのファイルが保存されています。それぞれのファイルには、何らかのルールに従った"連番"がつけられています。さて、次に保存するファイルの名前は何でしょう？ということです。あるいは、

だったり、あるいは、

だったり、あるいは、

みたいな感じで、次のファイル名を作りましょう、というお話です。これ、先日のVBAセミナーに参加された方から質問されました。そのときは、具体的な考え方などを詳しくお教えしたのですが、これって実務ではよくある話でしょうから、ここにも書いておきます。

このマクロを作るには、考えなければならないことが2つあります。

  1. すでに保存されているファイルの中で、最も大きい値は何か？
  2. その最も大きい値の、次の値は何か？

ということです。"数値"と限定せず"値"という言い方をしたのは、その連番が、1・2・3…など「数値」の場合と、20210430や2020-12みたいな「頭のいい人間はそれを"日付"と認識できるけど、実体は単なる文字列や数値」の場合があるからです。

最も大きい値のファイル名を調べる

ここでは例として、C:\Workフォルダに

  • 田中101.xlsx
  • 田中102.xlsx
  • 田中103.xlsx

という3つの連番ファイルが保存されているとしましょう。この中で、最も大きい数値を持つファイル名は「田中103.xlsx」です。これを調べるには、簡単な方法と厳密な方法があります。まずは、簡単な方法から。フォルダ内に存在するすべてのファイル名を取得するには、Dir関数を使います。たとえば、こんな感じです。

Sub Macro1()
    Dim A As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        A = Dir()
    Loop
End Sub

この「Dir関数」の使い方に自信のない方は、ぜひ下記動画をご覧ください。特に、最後の「Dir()」に関して、詳しく解説しています。

さて、上記動画でも解説していますが、Dir関数は一般的に、ファイル名を昇順で並べ替えた順番でファイル名を取得します。ちょっと変な日本語ですが、要するに「あ→い→う→え」とか「1→2→3」の順番で取得するというこです。したがって、Dir関数ですべてのファイル名を調べて、最後に見つかるのが「最も大きい値を持つファイル名」ということになります。しかし、上記コードで、Do Loopによる繰り返しが終了するのは、Dir関数がすべてのファイル名を取得し終えた後で「Dir関数が空欄("")を返したとき」です。つまり、Do Loopが終了した直後は、変数Aが空欄になってしまいます。それでは困りますので、変数Aに入ったファイル名を、別の変数に待避しておきます。

Sub Macro1()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    MsgBox B
End Sub

おそらく多くのビギナーは、この「B = A」の意味が分からなかったり、なぜその場所で「B = A」を行わなければならないのかがイメージできないでしょう。ここでモヤッとした方は、変数の挙動をイメージする力が弱いです。がんばってくださいね。

Dir関数はファイル名を昇順に並べ替えた順番で取得できると言いましたが、実は必ずそうなるわけではありません。上記動画でも解説していますが、ファイルが保存されているハードディスクやSSDなどの「フォーマットの種類」によって異なります。最近の、一般的なパソコンで使われているハードディスクやSSDなどの多くはNTFSでフォーマットされています。このNTFSでフォーマットされているディスクでしたら、ファイル名が昇順で取得されます。対して、最近ではすっかり珍しくなりましたが、いわゆるUSBメモリなどは、古いFATという形式でフォーマットされているものもあります。このFATでフォーマットされているディスクの場合は、Dir関数で取得されるファイル名が、ファイル名の昇順ではなく、ファイルが保存された日時順になります。ディスクが、どの形式でフォーマットされているかは、エクスプローラでドライブのアイコンを右クリックして表示されるメニューから[プロパティ]をクリックします。実行すると表示されるダイアログボックスで確認してください。いくつかお見せします。

これは、パソコンに内蔵されているSSDです。NTFSでフォーマットされています。

こちらは、パソコンのメモリースロットに差しているメモリーカードです。フォーマット形式の「exFAT」というのは「拡張FAT形式」です。このフォーマットでも、ファイル名は昇順で取得できます。

引き出しの中を探して、USBメモリを見つけました。これが「FAT形式」のフォーマットです。このUSBメモリ内に保存されているファイル名は、Dir関数で取得したときに、保存日時順で返されます。まぁ、最近はコンプライアンスやセキュリティの関係で、USBメモリを使えない企業も多いことでしょう。ですから、ほとんど心配する必要はありませんけど、Dir関数で取得したファイル名がおかしいときは、念のためフォーマットを調べてください。

  1. すでに保存指されているファイルの中で、最も大きい値は何か？
  2. その最も大きい値の、次の値は何か？

のうち、「最も大きい値を持つファイル名」を取得する方法として、簡便なやり方は、Dir関数が最後に見つけたファイル名を覚えておくということです。まぁ、最近の環境では、FAT形式でフォーマットされたディスクって、ほとんどありませんけど、でも知識としては覚えといてください。じゃ、この「Dir関数の性質」を信頼しないで、自分で「最も大きい値を持つファイル名」探すにはどうしたらいいでしょう。こうなると難易度が跳ね上がります。確実にというか厳密にやるのでしたら、取得したファイル名から数値部分を抜き出して、それら数値たちの中で最大値を見つけるという作業です。一応コードをご紹介しますけど、こんな苦労をするのは逆にどうかと思います。こんなに難しくなるんだ～という程度にご覧ください。なお、VBAで最大値を求める方法は、下記ページに詳しく書きましたので、ぜひ参照してください。

最大値や最小値を調べる

Sub Macro2()
    Dim A As String, n As Long, B() As Long
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        ReDim B(n)
        B(n) = Val(Replace(Split(A, ".")(0), "田中", ""))
        A = Dir()
        n = n + 1
    Loop
    MsgBox "田中" & WorksheetFunction.Max(B) + 1 & ".xlsx"
End Sub

取得したファイル名の"田中103.xlsx"などから、文字列部分を除去して"103"にするところを「Val(Replace(Split(A, ".")(0), "田中", ""))」と書きましたが、ここはケースバイケースです。実際のファイル名に合わせた処理が必要ですし、除去する方法もいろいろあります。これはあくまで一例です。まぁ、とにかく難しくなります。

次のファイル名を作る

問題はこっちです。先にヒントを言うと、ここでは"Excel的な発想"がポイントです。たとえば、"田中103.xlsx"などのファイル名から、数値部分に1を足した"田中104.xlsx"というファイル名を作るとき、これプログラミング的に考えたら

  1. "田中103.xlsx"から数値部分の"103"を抜き出す
  2. 抜き出した"103"に1を加えて"104"を作る
  3. 作った"104"に"田中"と".xlsx"を文字列結合してファイル名を作る

のような手順で考えます。たぶんですけど、JavaとかPythonとかだったら、そうやるかもしれません。すみません、そっち系でもっと便利な方法があるかもしれませんけど、それほど詳しくないのでご容赦ください。まぁ、でも、それが一般的なプログラミング的発想です。では、Excelだったらどうするのか。考えてみてください。セルA1に「A-123」という文字列が入力されていたとします。

この文字列から、数値部分の"123"に1を足した"124"、つまりこれ言い方を変えると「"123"から始まる連続した値」を持つ「A-124」という文字列を、手動操作で、セルA2に代入するにはどうしますか？文章で表現したので回りくどい言い方をしましたが、要するに上図を

みたくするには、どうしますか？ってことです。社会人なら誰でも分かりますよね。そう「オートフィル」です。これ、義務教育で習います。セルA1のフィルハンドルをセルA2までドラッグすればいいです。この操作で、セルA2に作られる「A-124」は何ですか？「A-123」の数値部分に1を加えた値ですよね。そして「A-123」というのは「文字列と数値で構成された値」です。ということは「田中103.xlsx」だって一緒です。こちらも「文字列と数値で構成された値」ですから、同じようにオートフィルを利用できます。

この発想の、何が超絶便利かというと「次の日付」も同じ操作で作れるというところです。よく実務では「20210430」のような表現をします。これは日付ではありません。単なる8桁の数値です。もしかしたら、電話番号かもしれないし、社員番号かもしれないし、製品番号かもしれません。でも人間は頭がいいですから、これを見て「2021年4月30日」という日付を連想します。この「20210430」の次として、単純に1を加えた「20210431」としようものなら「はぁ？ナニ言ってんの？4月は30日までしかないんですけどぉ～ｗ 4月30日の次は5月1日なんですけどぉ～ｗ 次は20210501に決まってるでしょ！バカなの？超ウケるんですけどぉ～ｗ ぷ～くすくす～(CV:アクア)」って言われます。「次の日付」というのは、単純に1を加えたものではありません。12の次は13ではなく1かもしれません。28の次が1とも限りません。29になる場合もあります。これらを計算で求めようとしたら、メチャクチャ大変なことは容易に想像できるでしょう。でも、Excelの「オートフィル」だったら一発です。

では、やってみましょう。なお、VBAでオートフィルを操作する"考え方"や"応用例"などは、下記ページに詳しく解説していますので、ぜひご覧ください。

連続データを作成する

C:\Workフォルダに

  • 田中101.xlsx
  • 田中102.xlsx
  • 田中103.xlsx

という3つの連番ファイルが保存されているとしましす。最も大きい値を持つファイル名「田中103.xlsx」を取得するところは、上記の解説をご覧ください。取得したファイル名「田中103.xlsx」をセルA1に代入し、オートフィルで次の名前を作成します。

Sub Macro3()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = B
    Range("A1").AutoFill Range("A1:A2")
    MsgBox Range("A2")
End Sub

アクティブブックを、この名前で保存するのでしたら、こんな感じでしょうか。

Sub Macro4()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = B
    Range("A1").AutoFill Range("A1:A2")
    ActiveWorkbook.SaveAs "C:\Work\" & Range("A2")
End Sub

言うまでもありませんが、

  • 集計 (1).xlsx
  • 集計 (2).xlsx
  • 集計 (3).xlsx

みたいなケースでも大丈夫です。

さあ、それでは日付編いきましょう。ここでは例を2つご紹介します。まず最初は、フォルダ内に次のようなファイルが存在するケースです。

  • 20210428.xlsx
  • 20210429.xlsx
  • 20210430.xlsx

まずは、Dir関数を使って「最も大きい値のファイル名」を取得します。

Sub Macro5()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    MsgBox B
End Sub

ただし、今までのように、この「20210430.xlsx」から連続データを作ってはいけません。「20210430」は日付ではなく、単なる8桁の数値なのですから。そこで、この「20210430」を、Excelが理解できる日付(シリアル値)に変換してやります。人間が「20210430」を見て、これを「2021年4月30日」と連想するとき、おそらく脳内では次のように判断しているはずです。

  • 先頭(左端)から4文字が"2021"だから、これは2021年だろう
  • 年の次(5文字目)から2文字が"04"だから、これは4月だろう
  • 月の次(7文字目)から2文字が"30"だから、これは30日だろう

これと同じことをVBAでやります。この「20210430」が変数Bに入っているのなら、

  • 先頭(左端)から4文字が"2021"だから、これは2021年だろう → Left(B, 4)
  • 年の次(5文字目)から2文字が"04"だから、これは4月だろう → Mid(B, 5, 2)
  • 月の次(7文字目)から2文字が"30"だから、これは30日だろう → Mid(B, 7, 2) 
Sub Macro5()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 5, 2), Mid(B, 7, 2))
End Sub

DateSerial関数は、年月日を表す3つの数値からシリアル値を生成する関数です。

代入したセルA1から連続データを作ります。

Sub Macro5()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 5, 2), Mid(B, 7, 2))
    Range("A1").AutoFill Range("A1:A2")
End Sub

そして最後に「2021/5/1」という日付を「20210501」という単なる8桁の数値に変換します。

Sub Macro5()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 5, 2), Mid(B, 7, 2))
    Range("A1").AutoFill Range("A1:A2")
    MsgBox Format(Range("A2"), "yyyymmdd")
End Sub

名前をつけて保存するなら、こんな感じですかね。

Sub Macro5()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 5, 2), Mid(B, 7, 2))
    Range("A1").AutoFill Range("A1:A2")
    ActiveWorkbook.SaveAs "C:\Work\" & Format(Range("A2"), "yyyymmdd") & ".xlsx"
End Sub

では、日付編のふたつめ。今度は、フォルダ内に次のようなファイルが保存されています。

  • 2020-10.xlsx
  • 2020-11.xlsx
  • 2020-12.xlsx

次に作りたいファイル名は「2021-01.xlsx」です。今度は、先より少し難しくなります。ポイントは「どれだけExcelの機能を知っているか」です。VBAだけできればいい、と考えている人には、相当難しいと思います。さて、まずは、最も大きい値のファイル名を取得します。

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    MsgBox B
End Sub

さて困りました。「2020-12」というのは、日付形式にすらなっていません。日付は年月日です。「2020-12」には日がありません。これでは日付になりませんので、適当な日をつけてやります。何日でもいいです。だって今回は、年と月だけが欲しいのですから。先と同じように、Mid関数などを使うのなら、次のようにします。

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 6, 2), 1)
End Sub

このように、年や月を表す数値の、位置と文字数で抜き出すのが簡単です。参考までにマニアックなやり方もご紹介します。どちらが良いという話ではありません。両方思いつくのがベストです。

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = Split(B, ".")(0) & "-1"
End Sub

問題はここからです。この「2020/12/1」から「2021/1/1」という連続データを作りたいんです。これ、どうしたらいいでしょう。簡単です。Excelにはその機能があります。まずは、手動操作でやってみましょうか。「2020/12/1」が入力されているセルA1のフィルハンドルを右ボタンでドラッグします。マウスのボタンを放したときに表示されるメニューから[連続データ(月単位)]をクリックします。

実行すると、月単位の連続データを作成できます。

Excelには、こういう機能があるんですよ。マクロを作りたいのなら、まずはExcelの機能を学習してください。さて、この月単位の連続データを作成するには、AutoFillメソッドの引数に定数xlFillMonthsを指定します。このへんの詳しい解説は、下記ページをご覧ください。

連続データを作成する

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 6, 2), 1)
    Range("A1").AutoFill Range("A1:A2"), xlFillMonths
End Sub

作成した「2021/1/1」という日付を「2021-01」に変換します。

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Range("A1") = DateSerial(Left(B, 4), Mid(B, 6, 2), 1)
    Range("A1").AutoFill Range("A1:A2"), xlFillMonths
    MsgBox Format(Range("A2"), "yyyy-mm")
End Sub

名前をつけて保存するところは、上述を参考にしてください。

今回の「次のファイル名を作る」という解説は以上です。要するに、"次の値"というのは「1を加えて作る」と考えるよりも、Excelの「オートフィル機能」を利用した方が簡単で便利ですよと。特に日付の場合は、効果絶大です。だから、オートフィル機能で何ができるのかという"機能の学習"を忘れないようにしてくださいねと。そういうお話です。最後に、ここまでの解説ではすべて、連続データをアクティブシートに作ってきました。「連続データを作成する」でも解説していますが、こういう「一時的に作る連続データ」は、非表示のワークシートに作った方が、実務では応用が利きます。最後に、そのコードもご紹介します。

Sub Macro6()
    Dim A As String, B As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        B = A
        A = Dir()
    Loop
    Sheets.Add.Visible = False
    With ActiveSheet.Previous
        .Range("A1") = DateSerial(Left(B, 4), Mid(B, 6, 2), 1)
        .Range("A1").AutoFill .Range("A1:A2"), xlFillMonths
        MsgBox Format(.Range("A2"), "yyyy-mm")
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
End Sub

コードの意味とポイントは「連続データを作成する」をご覧ください。