[教えて！VBA] 第11回マクロからファイルを新しく名付けて保存する際の注意点とは？？

Jan 12, 2022 · この記事は約11分で読めます · VBA Excel ·

Share on:

概要

この記事について

かんたんな概要と結論

フォルダに作成するファイルのパスを組み立てる際に、
エラーが起きないようにするチェックの仕組みを記載しました。

また、既存のファイルが有った場合には、
上書きする、別ファイルとして出力するなどの回避策があります。

こんにちは、dedeです。

この記事では、
VBAマクロに関する質問のうち、
皆が疑問に思っているトピックについて解説いたします。

今回は、
マクロからファイルを新しく名付けて保存する際に、エラーや想定外の結果にならないための注意点
を解説いたします。

※この記事は、Office VBAマクロのうち
Excel VBAマクロに関するトピックです。

レベル：初級者向け

環境

以下は、
Office 2016のExcel環境での説明です。

※2022/1時点の最新バージョンのExcelでも内容は変わりません。

また、WindowsOSのファイルシステムについての説明です（Macなどには当てはまらない箇所もあるかと思います）。
もっとも、Officeアプリを使用するのは主にWindowsユーザのため問題ないかとは思いますが。

ファイル保存について

VBAマクロでは、
取り扱うデータを収集・入力・加工したのちに、
別ファイルとしてデータを吐き出すような処理を書くことができます。

マクロによるファイルの出力

例えば、新しくブックを作成し、
マクロ登録済みのブックのセル内容を転機するマクロは次のようになります。

 1
 2Sub ブックとして出力するサンプル()
 3    Dim newWorkBook As Workbook
 4    
 5    '//ブックの新規追加
 6    Set newWorkBook = Workbooks.Add
 7    
 8    '//マクロ登録しているブックのB2セルの内容を転記
 9    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.Worksheets(1).Range("B2").Value
10    
11    '//追加したブックをtempフォルダに保存して閉じる
12    newWorkBook.SaveAs "C:\temp\ブックとして出力するサンプル.xlsx"
13    newWorkBook.Close SaveChanges:=False
14
15    
16End Sub
17

また、テキストファイルを作成し、
そちらに内容を書き込むマクロは次のようになります。

1
2Sub テキストファイルとして出力するサンプル()
3    Open "C:\temp\テキストファイルとして出力するサンプル.txt" For Append As #1
4    '//マクロ登録しているブックのB2セルの内容をテキストファイルに記入
5    Print #1, ThisWorkbook.Worksheets(1).Range("B2").Value
6    Close #1
7End Sub
8

これらに付帯する注意点と、
その対策についてを、
以下のセクションで見ていきます。

ファイル保存の注意点とチェック機構

ABOUT

ファイルを保存するためには、
必ずファイルパス（ファイルのアドレス。例：C:\temp\サンプル.txt）を指定します。

その際に、
いくつかのチェックを設けることによって、
予期せぬエラーや結果を回避することが、
マクロの性能向上にとって重要となります。

注意点1. フォルダやファイル名に空欄が無いようにする

データを挿入してファイルパスを動的に生成する場合、
挿入するデータの有効性をチェックする必要があります。

例えば、
セルの入力内容によってファイルパスを生成するような処理の場合、
セルの入力内容が空欄にならないようにチェックが必要です。

次のコードは
場合によってはエラーが発生するサンプルです。

エラー発生時、
新規作成したブックは開かれたままになってしまいます。

 1
 2Sub セルの入力内容によってファイルパスを生成_NGサンプル()
 3    Dim newWorkBook As Workbook
 4    Dim filePath As String
 5    
 6    Set newWorkBook = Workbooks.Add
 7    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
 8    
 9    '//ファイルパスを動的に生成
10    '////※　B3セルが空欄だったら1004エラー発生
11    filePath = "C:\temp\" & ThisWorkbook.ActiveSheet.Range("B3").Value & ".xlsx"
12    newWorkBook.SaveAs filePath
13    newWorkBook.Close SaveChanges:=False
14End Sub
15

これを改善するために、
次のチェック機構2点を導入します。

B3セルが空欄でないことを確かめる
空欄であった場合に処理を終了し、ブックを保存せず閉じる

 1
 2Sub セルの入力内容によってファイルパスを生成_チェック機構追加サンプル()
 3    Dim newWorkBook As Workbook
 4    Dim filePath As String
 5    
 6    On Error GoTo ErrorHandler
 7    
 8    Set newWorkBook = Workbooks.Add
 9    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
10    
11    '//ファイルパスを動的に生成
12    '////B3セルが空欄でないことを確かめる
13    If ThisWorkbook.ActiveSheet.Range("B3").Value = "" Then
14        MsgBox "B3セルにファイル名を入力して下さい", vbExclamation
15        GoTo ExitHandler
16    End If
17    filePath = "C:\temp\" & ThisWorkbook.ActiveSheet.Range("B3").Value & ".xlsx"
18    
19    newWorkBook.SaveAs filePath
20
21ExitHandler:
22    '//ブックを閉じる
23    newWorkBook.Close SaveChanges:=False
24    
25    Exit Sub
26    
27ErrorHandler:
28
29    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
30    
31    GoTo ExitHandler
32    
33End Sub
34

空欄のエラーが起きた場合でも、
後始末の処理を導入しやすい形になりました。

注意点2. ファイル名に禁止文字が存在してはならない

Windows OS環境のファイル名には次の文字が使えません。
（いずれも半角です）

文字	意味
"	ダブルクォーテーション
<	小なり
>	大なり
｜	バーティカルバー
:	コロン
*	アスタリスク
?	クエスチョンマーク
¥	円記号
/	スラッシュ

もしいずれかの文字が含まれたパスでファイルを保存しようとする場合、
保存メソッドがエラーとなります。

保存メソッドのエラー

ファイル名にこれらが検知された場合、
主な対処法としては次の2点があるでしょう。

警告メッセージを表示し、保存をキャンセル
禁止文字をすべてなにかの文字（例えばアンダースコア「_」）に置き換えて出力

前者についてのサンプルはつぎのようになります。

 1
 2Private Const FILE_FORBIDDEN_CHARACTORS_STR = "\_/_:_*_?_""_<_>_|"
 3
 4Sub ファイル名禁止文字を検知_アラートを出す場合()
 5    Dim newWorkBook As Workbook
 6    Dim filePath As String
 7    Dim myFilename  As String
 8    Dim forbiddenChar As Variant
 9    Dim cancel As Boolean
10    
11    On Error GoTo ErrorHandler
12    
13    Set newWorkBook = Workbooks.Add
14    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
15    
16    '//ファイル名をセルから取得
17    myFilename = ThisWorkbook.ActiveSheet.Range("B3").Value
18    filePath = "C:\temp\" & myFilename & ".xlsx"
19    
20    
21    '//ファイル名禁止文字が含まれていないかどうかをチェック
22    cancel = False
23    For Each forbiddenChar In Split(FILE_FORBIDDEN_CHARACTORS_STR, "_")
24        If InStr(myFilename, forbiddenChar) > 0 Then
25            '//含まれている場合
26            cancel = True
27            Exit For
28        End If
29    Next forbiddenChar
30    If cancel Then
31        '//キャンセルする場合
32        MsgBox "ファイル名として使えない文字が含まれています", vbExclamation
33        GoTo ExitHandler
34    End If
35    
36    newWorkBook.SaveAs filePath
37
38ExitHandler:
39    '//ブックを閉じる
40    newWorkBook.Close SaveChanges:=False
41    
42    Exit Sub
43    
44ErrorHandler:
45
46    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
47    
48    GoTo ExitHandler
49    
50End Sub
51

もし禁止文字9文字のいずれかがセルに書かれたファイル名に存在している場合、
ブックは保存されません。

また、後者（禁止文字を置換する）についてのサンプルは次のようになります。

 1
 2Private Const FILE_FORBIDDEN_CHARACTORS_STR = "\_/_:_*_?_""_<_>_|"
 3
 4Sub ファイル名禁止文字を検知_文字を置き換えて出力する場合()
 5    Dim newWorkBook As Workbook
 6    Dim filePath As String
 7    Dim myFilename  As String
 8    Dim forbiddenChar As Variant
 9    
10    On Error GoTo ErrorHandler
11    
12    Set newWorkBook = Workbooks.Add
13    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
14    
15    '//ファイル名をセルから取得
16    myFilename = ThisWorkbook.ActiveSheet.Range("B3").Value
17    
18    '//ファイル名禁止文字が含まれていないかどうかをチェック
19    For Each forbiddenChar In Split(FILE_FORBIDDEN_CHARACTORS_STR, "_")
20        If InStr(myFilename, forbiddenChar) > 0 Then
21            '//含まれている場合、すべてアンダースコアに置換する
22            myFilename = Replace(myFilename, forbiddenChar, "_")
23        End If
24    Next forbiddenChar
25    
26    '//ファイルパスを作成
27    filePath = "C:\temp\" & myFilename & ".xlsx"
28    
29    newWorkBook.SaveAs filePath
30
31ExitHandler:
32    '//ブックを閉じる
33    newWorkBook.Close SaveChanges:=False
34    
35    Exit Sub
36    
37ErrorHandler:
38
39    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
40    
41    GoTo ExitHandler
42    
43End Sub
44

禁止文字のひとつひとつをファイル名に存在するか調べ、
該当すれば、すべて置換します。

この方法ならば、
どのようなファイル名（パス名全体で255文字を超えない限り）でも出力することができます。

+α 厳密にチェックするには

上記の注意点1, 2を総括すると、
ファイル名の有効性をチェックするというタスクになります。

ところで、
文字列のチェックには正規表現を使用すればより厳密なチェックが可能です。

多くの場合は注意点1, 2で十分に対応可能かと思いますが、
より厳密にチェックが必要であれば、正規表現を使用しましょう。

正規表現の使い方についてはこちらに素晴らしい記事があります。

正規表現のパターン指定は使用環境や目的によってまちまちと思いますが、
VBAのパターンはJavascript(ES2015以降)などの他のモダン言語に比べてやや貧弱であることは注意しなければならないでしょう。

注意点3. 指定パスにファイルが存在する場合

指定パスに作成したいファイルと同名ファイルが既に存在していた場合、
状況はやや違ってきます。

同名ファイルが既に存在

ファイルシステムの都合上、
ファイルはユニークなパス（唯一無二のパス）を持つ必要があるため、
フォルダに同名ファイルは設置できません。

回避策として、

既存ファイルを上書きする
警告を表示して保存をキャンセルする
ファイル名にランダム文字列を付け、別ファイルとして出力するが挙げられるでしょう。

それらについて見ていきます。

既存ファイルを上書き

この方法のユースケースとしては、
指定フォルダに同名ファイルがあっても気にしない（すでに古くなったファイルとみなす）場合や、
出力するファイルの内容が変化しないことを想定している場合に用いられるでしょう。

上書きする際には、
通常はアラートメッセージが表示されます。

アラートメッセージ

それを回避するために、
DisplayAlertsプロパティをいったんFalseに設定します。

コードは次のようになります。

 1
 2Sub 指定パスにファイルが存在する_既存ファイルを上書きする方法()
 3    Dim newWorkBook As Workbook
 4    Dim filePath As String
 5    
 6    On Error GoTo ErrorHandler
 7    
 8    '//いったん警告メッセージを非表示化
 9    Application.DisplayAlerts = False
10    
11    Set newWorkBook = Workbooks.Add
12    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
13    
14    '//ファイルパスを動的に生成
15    filePath = "C:\temp\" & ThisWorkbook.ActiveSheet.Range("B3").Value & ".xlsx"
16    
17    newWorkBook.SaveAs filePath
18
19ExitHandler:
20    '//ブックを閉じる
21    newWorkBook.Close SaveChanges:=False
22    
23    '//警告メッセージの設定を戻す
24    Application.DisplayAlerts = True
25    
26    Exit Sub
27    
28ErrorHandler:
29
30    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
31    
32    GoTo ExitHandler
33    
34End Sub
35

注意しなければならないのは、
Application.DisplayAlertsの値を必ずTrueに戻すことです。

DisplayAlertsを無効化している間は、
すべての警告メッセージが出ないようになるので、
普段遣いのExcelでの作業がやりにくくなる可能性があるためです。

警告を表示しキャンセル

この方法のユースケースとしては、
既存ファイルを削除したくない場合や、
そもそも同名のファイルを出力するようなオペレーションが、業務フローに対して本質的に間違っているので
ユーザにやりなおしをさせたい場合などが該当するでしょう。

ファイル存在有無の検知には、
Dir関数を利用します。
（FSOのFileExistsメソッドを利用しても可能です）

 1
 2Sub 指定パスにファイルが存在する_警告を表示しキャンセルする方法()
 3    Dim newWorkBook As Workbook
 4    Dim filePath As String
 5    
 6    On Error GoTo ErrorHandler
 7    
 8    Set newWorkBook = Workbooks.Add
 9    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
10    
11    '//ファイルパスを動的に生成
12    filePath = "C:\temp\" & ThisWorkbook.ActiveSheet.Range("B3").Value & ".xlsx"
13    
14    '//フォルダに同名ファイルが存在するかどうかをチェック
15    If Dir(filePath) <> "" Then
16        '//キャンセルする
17        MsgBox "出力先のフォルダに、すでに同じ名前のファイルが存在します。", vbExclamation
18        GoTo ExitHandler
19    End If
20    
21    newWorkBook.SaveAs filePath
22
23ExitHandler:
24    '//ブックを閉じる
25    newWorkBook.Close SaveChanges:=False
26    
27    Exit Sub
28    
29ErrorHandler:
30
31    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
32    
33    GoTo ExitHandler
34    
35End Sub
36

別ファイルとして出力

この方法のユースケースとしては、
既存・新規それぞれのファイルは維持したいが、
ユーザにもう一度ファイル名の設定をやり直させたくない場合、
および、マクロで定期的に自動でファイルを吐き出すような処理
（VBAでそのようなプログラムを走らせるのはあまり現実的ではないかもしれませんが）を実行したい場合が挙げられるでしょう。

別ファイルとして出力するために、
ファイル名の最後にランダム文字列を付与します。

ランダム文字列の実装には、
SHA-256ハッシュ値を使用します。

こちらでご紹介されていたハッシュ関数を利用しました。

リンク先でもご紹介されているように、
.NET FrameworkのSystem.Security.Cryptographyライブラリを利用することで、
VBAの環境でもSHA-256を利用することが可能になります。

ハッシュの引数として、
現在時刻（Now）を採用すれば、
それぞれのファイルで決して被ることがない文字列が生成できます。

 1
 2'// 引用:https://blog.nekonium.com/vba-hash/
 3Public Function SHA256_HEX(str As String) As String
 4    Dim sha256m As Object
 5    Dim utf8 As Object
 6    Dim bytes() As Byte
 7    Dim hash() As Byte
 8    Dim i As Integer
 9    Dim res As String
10
11    Set utf8 = CreateObject("System.Text.UTF8Encoding")
12    bytes = utf8.GetBytes_4(str)
13    Debug.Print bytes
14
15    Set sha256m = CreateObject("System.Security.Cryptography.SHA256Managed")
16    hash = sha256m.ComputeHash_2((bytes))
17    Debug.Print hash
18    
19    For i = LBound(hash) To UBound(hash)
20        res = res & LCase(Right("0" & Hex(hash(i)), 2))
21    Next i
22
23    SHA256_HEX = LCase(res)
24End Function
25
26
27
28Sub 指定パスにファイルが存在する_別ファイルとして出力する方法()
29    Dim newWorkBook As Workbook
30    Dim filePath As String
31    Dim myFileName As String
32    
33    On Error GoTo ErrorHandler
34    
35    Set newWorkBook = Workbooks.Add
36    newWorkBook.Worksheets(1).Range("A1").Value = ThisWorkbook.ActiveSheet.Range("B2").Value
37    
38    '//ファイルパスを動的に生成
39    myFileName = ThisWorkbook.ActiveSheet.Range("B3").Value
40    filePath = "C:\temp\" & myFileName & ".xlsx"
41    
42    '//フォルダに同名ファイルが存在するかどうかをチェック
43    If Dir(filePath) <> "" Then
44        '//ファイル名にハッシュ値を付与
45        myFileName = myFileName & "_" & SHA256_HEX(Now())
46        '//ファイルパスを再設定
47        filePath = "C:\temp\" & myFileName & ".xlsx"
48    End If
49    
50    newWorkBook.SaveAs filePath
51
52ExitHandler:
53    '//ブックを閉じる
54    newWorkBook.Close SaveChanges:=False
55    
56    Exit Sub
57    
58ErrorHandler:
59
60    MsgBox "エラー発生" & vbLf & Err.Description, vbCritical, Err.number
61    
62    GoTo ExitHandler
63    
64End Sub
65

【以下、デモです】

フォルダに「テストファイル」が存在する状態でマクロを実行すると……

マクロ実行前

ハッシュ値が付与されたファイルが出力されます。

マクロ実行後

終わりに

以上、ファイルを新規生成してフォルダに出力する際の注意点について説明しました。

ケースバイケースでそれぞれの回避策を組み合わせれば、
既存のマクロをさらに使い勝手の良いマクロに成長させることができるでしょう。

[教えて！VBA] 第11回 マクロからファイルを新しく名付けて保存する際の注意点とは？？

概要