エクセルVBAでファイル作成とパスワード設定を同時に行う方法

2019年7月24日
2019年9月2日
パスワード設定
Excel, VBA, パスワード

パスワード設定

みなさん、こんにちはケンケンです。

前回は、VBAで８桁のパスワードを作成することに成功しました。

今回は、ファイルの作成とパスワードの生成を同時にできるようにして、システムを完成させます。

1 前回の復習
2 ファイル作成とパスワード設定を同時に行うには
3 パスワード管理ファイルの管理方法
4 おまけ：２００兆分の１
5 まとめ

前回の復習

前回は８桁のパスワードをVBAを使って作成することに成功しました。

前回のコードはこちらです。

Sub save_pass8()
    Dim cnt 'パスワード生成用カウント
    Dim pass '１文字格納用
    Dim pass8 '８文字格納用
    Dim cnt8  'シート書き出し用カウント
    
    For cnt8 = 2 To 10
        For cnt = 1 To 8
            pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)
            pass8 = pass8 + pass
        Next
        Sheets("計算結果").Range("C" & cnt8).Value = pass8
        pass8 = ""
    Next
End Sub

Sub save_pass8()

Dim cnt 'パスワード生成用カウント

Dim pass '１文字格納用

Dim pass8 '８文字格納用

Dim cnt8 'シート書き出し用カウント

For cnt8 = 2 To 10

For cnt = 1 To 8

pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)

pass8 = pass8 + pass

Sheets("計算結果").Range("C" & cnt8).Value = pass8

pass8 = ""

End Sub

RandBetween関数とMid関数を使ってランダムに文字を抽出し、変数を駆使して８桁のパスワードを生成しました。

このパスワード管理シートを作って、そこに格納しファイル名と共に管理してみます。

ファイル作成とパスワード設定を同時に行うには

それでは、パスワード設定と同時にファイル作成もできるシステムを作って行きましょう。

パスワード管理シートを作ってそこで管理する

まずは、パスワードを管理するシートを作りましょう。

特別なことはしません。単に新しいシートを作ればOKです。

「pass管理」という名前にしました。

A列に作りたいファイル名、B列にパスワードを入力していきます。

ちなみにこのシートは前回作った「計算元」シートがあるファイルに作ってください。

パスワードを生成して管理シートに書き出そう

前回作ったパスワード生成ツールを応用して管理シートにパスワードを書き出してみましょう。

修正したコードは以下の通りです。

Sub save_pass9()
    Dim cnt 'パスワード生成用カウント
    Dim pass '１文字格納用
    Dim pass8 '８文字格納用
    Dim cnt8  'シート書き出し用カウント
    Dim Lrow  '①
    
    Lrow = Sheets("pass管理").Range("A" & Rows.Count).End(xlUp).Row '①
    
    For cnt8 = 2 To Lrow '②シート書き出し用
        For cnt = 1 To 8 'パスワード８桁準備用
            pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)
            pass8 = pass8 + pass
        Next
        Sheets("pass管理").Range("B" & cnt8).Value = pass8 '③
        pass8 = ""
    Next
End Sub

Sub save_pass9()

Dim cnt 'パスワード生成用カウント

Dim pass '１文字格納用

Dim pass8 '８文字格納用

Dim cnt8 'シート書き出し用カウント

Dim Lrow '①

Lrow = Sheets("pass管理").Range("A" & Rows.Count).End(xlUp).Row '①

For cnt8 = 2 To Lrow '②シート書き出し用

For cnt = 1 To 8 'パスワード８桁準備用

pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)

pass8 = pass8 + pass

Sheets("pass管理").Range("B" & cnt8).Value = pass8 '③

pass8 = ""

End Sub

①から③が修正および追加した部分です。

ひとつずつ解説します。

①変数Lrowを宣言して、pass管理シートA列の最終行を取得しています。

つまり、作成したいファイルの数(行数)を数えて、その分だけパスワードを生成したいからです。

②書き出し用の変数の最終値をLrowに置き換えます。

③書き出し先をpass管理シートのB列に指定しています。

それでは、実行してみましょう。

コード実行ボタンを作成しよう

ここまで出来ればあとは、以前作ったファイル作成用のプロシージャと合算するだけです。

ファイル作成用のコードは以下の通りです。

Sub save_pass3()
    Dim fileCnt 'カウント変数
    Dim Lrow
    
    Application.ScreenUpdating = False
    Lrow = Range("A" & Rows.Count).End(xlUp).Row 'A列最終行を変数Lrowに格納
    For fileCnt = 2 To Lrow
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyymmdd") & "_" & Range("A" & fileCnt).Value, Password:=Range("B" & fileCnt).Value
        ActiveWorkbook.Close
    Next
    Application.ScreenUpdating = True
End Sub

Sub save_pass3()

Dim fileCnt 'カウント変数

Dim Lrow

Application.ScreenUpdating = False

Lrow = Range("A" & Rows.Count).End(xlUp).Row 'A列最終行を変数Lrowに格納

For fileCnt = 2 To Lrow

Workbooks.Add.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyymmdd") & "_" & Range("A" & fileCnt).Value, Password:=Range("B" & fileCnt).Value

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

このプロシージャと先ほど作ったプロシージャを合算で呼び出すプロシージャを作成します。

Sub save_pass_complete()
    call save_pass9
    call save_pass3
End Sub

Sub save_pass_complete()

call save_pass9

call save_pass3

End Sub

ちょっと分かりにくいかもですが、save_pass9がパスワード生成用のプロシージャでsave_pass3がファイル作成用のプロシージャです。

ここまでできたら、pass管理シートにボタンを作ります。

開発タブから挿入を選択し、対象プロシージャを選びOKをクリックします。

ボタンができたらクリックしてみましょう。

パスワードとファイルが作成されたことを確認してください。

そして、どれでもいいのでファイルを開いてパスワードを入力してみてください。

ファイルが開けば成功です。

パスワード管理ファイルの管理方法

システム自体は完成しましたが、課題が残っています。

パスワード管理ファイルそのものの存在です。

これを誰でも閲覧できてしまっては何のためのパスワードか分かりませんね。

パスワード管理ファイルはそれ単体で重要な書類なので、そのファイル自体を保護しましょう。

保護方法としては

パスワードが記載されているシートを非表示にする
ファイルにパスワードをかけ、そのパスワードを紙に書き金庫に保存する
ファイルにパスワードをかけ、パスワード管理ツールを使う

などがあると思います。

今回は深堀りしませんが、重要なことなのでもし実務で遭遇したら適切に対応できるように、日頃から考えておくと良いでしょう。

おまけ：２００兆分の１

実はもうひとつ問題があります。

パスワードが重複した場合の処置をとっていないのです。

当然パスワードは使い回さない方が良いのですが、今回は、その処理を見送って、別の機会があればご紹介したいと思います。

その理由は、パスワードが重複して作られる確率を調べたところ約２００兆分の１の確率だったからです。

62文字から１文字をランダムで選び、それを８回繰り返した結果、同じ英数字が並びも一緒になる確率は、62の８乗分の１になりますね（計算方法が違ったら教えてください。）

途方もない数字だったので、この結果を見た時に正直重複を回避するシステムを作る動機を失いました(笑)

ただし、実務では万が一のことも考えて、そんな甘いことは言ってられないので、もしみなさんがパスワード等の管理が重要でかつ重複を避けたほうが良いものに関しては、ちゃんと対応策を考えておく方がよいことは言うまでもありません。

先ほども少し触れましたが、管理ツールを使うなど、今では安価で便利なシステムがたくさん存在するので、そちらを活用することもおススメします。

まとめ

いかがだったでしょうか。

数回に分けてパスワードの重要性と設定の仕方を紹介してきました。

関数やVBAの知識以前に、実務では気を付けなければいけないことがたくさんあります。

パスワード管理はいい例ですね。

実務で様々な問題に直面したときに、まずは何が重要か考えるクセをつけましょう。その後で必要な知識やスキルを身に付ければ応用も効きます。

パスワード管理に限らず使えるテクニックもちりばめていますので、是非みなさんも実務で活用してください。

それでは、また別のシリーズでお会いしましょう。

お疲れ様でした。

今シリーズで作成したコード

Sub save_pass3()
    Dim fileCnt 'カウント変数
    Dim Lrow
    
    Application.ScreenUpdating = False
    Lrow = Range("A" & Rows.Count).End(xlUp).Row 'A列最終行を変数Lrowに格納
    For fileCnt = 2 To Lrow
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyymmdd") & "_" & Range("A" & fileCnt).Value, Password:=Range("B" & fileCnt).Value
        ActiveWorkbook.Close
    Next
    Application.ScreenUpdating = True
End Sub

Sub save_pass3()

Dim fileCnt 'カウント変数

Dim Lrow

Application.ScreenUpdating = False

Lrow = Range("A" & Rows.Count).End(xlUp).Row 'A列最終行を変数Lrowに格納

For fileCnt = 2 To Lrow

Workbooks.Add.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyymmdd") & "_" & Range("A" & fileCnt).Value, Password:=Range("B" & fileCnt).Value

ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

Sub save_pass9()
    Dim cnt 'パスワード生成用カウント
    Dim pass '１文字格納用
    Dim pass8 '８文字格納用
    Dim cnt8  'シート書き出し用カウント
    Dim Lrow  '①
    
    Lrow = Sheets("pass管理").Range("A" & Rows.Count).End(xlUp).Row '①
    
    For cnt8 = 2 To Lrow '②シート書き出し用
        For cnt = 1 To 8 'パスワード８桁準備用
            pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)
            pass8 = pass8 + pass
        Next
        Sheets("pass管理").Range("B" & cnt8).Value = pass8 '③
        pass8 = ""
    Next
End Sub

Sub save_pass9()

Dim cnt 'パスワード生成用カウント

Dim pass '１文字格納用

Dim pass8 '８文字格納用

Dim cnt8 'シート書き出し用カウント

Dim Lrow '①

Lrow = Sheets("pass管理").Range("A" & Rows.Count).End(xlUp).Row '①

For cnt8 = 2 To Lrow '②シート書き出し用

For cnt = 1 To 8 'パスワード８桁準備用

pass = Mid(Sheets("計算元").Range("A1").Value, WorksheetFunction.RandBetween(1, Sheets("計算元").Range("A2").Value), 1)

pass8 = pass8 + pass

Sheets("pass管理").Range("B" & cnt8).Value = pass8 '③

pass8 = ""

End Sub

Sub save_pass_complete()
    Call save_pass9
    Call save_pass3
End Sub

Sub save_pass_complete()

Call save_pass9

Call save_pass3

End Sub

【記事一覧】エクセルでパスワード管理を徹底する方法

エクセルでパスワード管理を徹底する方法【VBA・関数使用】

エクセル関数を使ってパスワード管理をする方法

RANDBETWEEN関数を使ってパスワードを作る方法【エクセル関数】

VBAで新規ファイルを作成しパスワードを設定する方法

VBAで複数のファイルを作成しそのファイルにパスワードを設定する方法

エクセルVBAでRANDBETWEEN関数を使って８桁のパスワードを複数設定する方法

エクセルVBAでファイル作成とパスワード設定を同時に行う方法

エクセルVBAでファイル作成とパスワード設定を同時に行う方法

前回の復習

ファイル作成とパスワード設定を同時に行うには

パスワード管理シートを作ってそこで管理する

パスワードを生成して管理シートに書き出そう

コード実行ボタンを作成しよう

パスワード管理ファイルの管理方法

おまけ：２００兆分の１

まとめ

エクセルVBAでRANDBETWEEN関数を使って８桁のパスワードを複数設定する方法

[天才心理学者アダム・グラントに学ぶ]会社を辞めないですむ………かもしれない方法