Office TANAKA - Excel VBA Tips[パスワードありブックを開く]

パスワード関連ネタです。ブックにパスワードを設定する方法は、下記ページをご覧ください。2種類のやり方を解説しています。

本稿では、読み取りパスワードが設定されているブックを、マクロで開くやり方を解説します。まずは、やってみましょう。すでにパスワードを設定しているブックを手動で開いて、その操作をマクロ記録します。開くブックは"C:\Work\東京.xlsx"で、設定しているパスワードは"Tokyo"です。

下記のコードが記録されました。

Sub Macro1()
    Workbooks.Open Filename:="C:\Work\東京.xlsx"
End Sub

パスワードは、開く途中で表示されたダイアログボックスに手入力したのですが、その件に関しては記録されていません。ちなみに、上記のコードをそのまま実行すると、当然ですけど[パスワード]ダイアログボックスが表示されたところでマクロが一時停止します。パスワードが分かっていれば、本稿の下記で解説する方法で開けるのですが、もしパスワードが分からなかったら。あるいは、マクロで開こうとしているブックに、そもそもパスワードが設定されているかどうかが分からなかったら、マクロが停まってしまうかもしれません。もし「パスワードが設定されていたら開くのをやめて、マクロは正常に進めたい」のでしたら、下記ページで解説している方法で対処してください。ちょっとした裏技を使っています。

パスワード入力画面をスキップする

さて今回は、パスワードが分かっているとしましょう。ブックを開くとき、パスワードも一緒に指定するには、次のようにします。

Sub Macro2()
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Tokyo"
End Sub

パスワードは、引数Passwordに指定します。[パスワード]ダイアログボックスは表示されません。

とりあえず成功しました。ではさらに、あれこれと実験してみましょう。まずは「間違ったパスワードを指定したら」です。

Sub Macro3()
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Osaka"
End Sub

マクロがエラーになりました。まぁ、しかたないですね。エラーで止まらないようにしましょう。

Sub Macro3()
    On Error Resume Next
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Osaka"
End Sub

ただし、これでは開けたかどうかが分かりません。なので、直後のアクティブブックで判定しましょうか。

Sub Macro3()
    On Error Resume Next
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Osaka"
    If ActiveWorkbook.Name = "東京.xlsx" Then
        MsgBox "ブックを開けました"
    Else
        MsgBox "ブックを開けませんでした"
    End If
End Sub

Openメソッドでブックを開いた直後は、必ず開いたブックがアクティブブックになります。ですから、ファイル名を調べればいいでしょう。もちろん、次のようにErrオブジェクトを使う手もあります。お好きな方法で対応してください。

Sub Macro4()
    On Error Resume Next
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Osaka"
    If Err.Number = 0 Then
        MsgBox "ブックを開けました"
    Else
        MsgBox "ブックを開けませんでした"
    End If
End Sub

では次に「開こうとしたブックに、そもそもパスワードが設定されていなかったら」を試してみましょう。"東京.xlsx"のパスワードを解除して、下記のコードを実行してみます。

Sub Macro2()
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Tokyo"
End Sub

何も問題なく開けました。パスワードが設定されていないブックを開くとき、引数Passwordに何かを指定しても無視されるということです。

複数のブックを一気に処理する

ブックのパスワードに関する質問って、実はこうしたケースが多いです。ひとつのブックに対して云々ではなく、フォルダ内にある複数のブックで～みたいな。なので、いくつかの考え方をご紹介します。

1.複数の新規ブックにパスワードを設定して保存する

これは、本稿のテーマである「パスワードありブックを開く」件ではありませんが、そもそも複数のパスワードありブックが存在しないと話が進みませんので、まずは複数のパスワードありブックを作成します。実は、先日のセミナーで渡邊さんから質問されたテーマです。実際には、オートフィルタで特定のデータだけを抽出し、それぞれを"パスワードあり"ブックとして保存したいという質問でしたが、今回オートフィルタ関係の部分は割愛して、ただ新規ブックに名前をつけて保存します。

今回は、次のようなブックを"C:\Work"フォルダに作成します。

ファイル名	パスワード
東京.xlsx	Tokyo
大阪.xlsx	Osaka
広島.xlsx	Hiroshima
福岡.xlsx	Fukuoka

こうしたケースで、最も重要なポイントは「ファイル名とパスワードの対応リストは、どこにあるのか」です。これにつきます。よく、こうした質問のとき「ファイル名とパスワードは決まってるんです♪」みたいに軽く言われることが多いのですが、その対応リストが紙に印刷されていたり、あなたの頭の中にあるのでしたら、マクロで自動化なんて無理です。あったり前です。マクロの中で、ファイル名とパスワードを動的に生成して自動処理を実現するには、対応リストがデータとして存在していなければなりません。そして、そのデータの形式やフォーマットなどによって、処理の方向性などが決まります。最も重要なポイントは、ここです。パスワードつけて保存するのなんて、オマケみたいなもんです。

最も簡単なケースは、対応のリストがワークシートに入力されている状態です。

Sub Macro5()
    Dim i As Long
    With ThisWorkbook.Sheets("Sheet1")
        For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            Workbooks.Add
            ActiveWorkbook.SaveAs Filename:="C:\Work\" & .Cells(i, 1), _
                                  Password:=.Cells(i, 2)
            ActiveWorkbook.Close False
        Next i
    End With
End Sub

パスワードを設定している「Password:=」部分に関しては、下記ページで解説していますのでご覧ください。

パスワードをつけて保存する

このマクロのポイントは、"保存するファイル名とパスワードの対応リスト"が入力されているシートを、どう指定するのかです。一般的に、その手の対応リストがシート上に存在しているときは、「その対応リストが存在するシートをアクティブシートにしてマクロを実行する」という考え方が一般的であり、最も簡単です。それだけでしたら、上記のコードも次のようにシンプルに書けます。

Sub Macro5()
    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:="C:\Work\" & Cells(i, 1), _
                              Password:=Cells(i, 2)
        ActiveWorkbook.Close False
    Next i
End Sub

しかし、このマクロは失敗します。Workbooks.Add を実行すると新しいブックが挿入され、必ずアクティブシートが移動するからです。複数のブックを扱うようなマクロって、苦手に感じているビギナーが多いです。おそらく、その原因のひとつは、コードを読んだり書いたりしているとき「今、Excelがどうなっているか」をイメージできないからでしょう。イメージの世界ですから、これはもう、がんばるしかないです！がんばってイメージできるようになってください。

ということで、最初にお見せしたコードでは、Withステートメントでシートを特定しています。ここでは、ThisWorkbook.Sheets("Sheet1")としました。その後に記述している「Cells(i, 1)」と「Cells(i, 2)」の前にドット(.)がついている点に留意してください。点だけに・・・

2.複数のパスワードありブックを全部開いて処理する

これも、よく質問される処理ですね。基本的には、先の「パスワードをつけて保存する」と考え方は同じです。冒頭で書いたように、次のコードでパスワードを指定して開けます。

Sub Macro2()
    Workbooks.Open Filename:="C:\Work\東京.xlsx", Password:="Tokyo"
End Sub

このファイル名やパスワード部分に、セルの値や変数などを指定すればいいです。実務では、こうした処理って2パターンに分類できます。

リストに書かれているブックをすべて順番に開いて処理する
フォルダ内に存在するブックをすべて順番に開いて処理する

似ていますが、考え方が異なります。1.は、リストに書かれているブックだけを開いて処理します。リスト中心の考え方ですね。一方の2.は、リストに書かれているかどうかはともかく、とにかくフォルダ内に存在するブック全部を開きます。いわば、ファイル中心の発想です。それぞれでポイントが異なりますので、順番に解説します。まずは「1.リストに書かれているブックをすべて順番に開いて処理する」です。

対応リストが上図のようになっていたとします。ここに記載されている4つのブックだけを開きます。ファイル名とパスワードが分かっているのですから、まずは次のように考えられます。

Sub Macro6()
    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Workbooks.Open Filename:="C:\Work\" & Cells(i, 1), Password:=Cells(i, 2)
        ''開いたブックに対しての処理
        ActiveWorkbook.Close False
    Next i
End Sub

対応リストが入力されているシートをアクティブにして実行します。先に注意したように、対応リストのシートを特定していない理由が分かりますか？開いたブックの処理が終わり、開いたブックを閉じると、最初の"対応リストが入力されているシート"がアクティブシートになるからです。基本的に、アクティブブックやアクティブシートって、マクロの中で動かす必要はありません。ブック - シート - セルという階層構造を、きっちりと指定すればいいです。でも、もし、何らかの事情があって、ブックを開いた後でアクティブブックを動かさなければいけないとしたら。そうなると、最後の ActiveWorkbook.Close は危険です。そのときは、何らかの方法で、それぞれのブックを特定しなければいけません。先は、私がよくやる Withステートメントの書き方をご紹介したので、今度は変数を使ってやってみます。いろんな方法を思いつくようにしてください。

Sub Macro7()
    Dim i As Long, F As String, P As String
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        F = ThisWorkbook.Sheets("Sheet1").Cells(i, 1)
        P = ThisWorkbook.Sheets("Sheet1").Cells(i, 2)
        Workbooks.Open FileName:="C:\Work\" & F, Password:=P
        ''開いたブックに対しての処理
        Workbooks(F).Close False
    Next i
End Sub

パスワードを指定しているところの「:=P」が顔文字のように見えるかもしれませんが、決して狙ったわけではありません(キリッ

さて、本題はここからです。このように、指定したブックを開くようなマクロがエラーになるのは、どんなケースだと思いますか？おそらく、エラー原因の第一位は「開こうとしたブックが存在しなかった」です。なので、別のファイルを開くようなマクロでは、必ず"ファイルの存在確認"をする習慣を身につけましょう。

Sub Macro8()
    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Dir("C:\Work\" & Cells(i, 1)) = "" Then
            ''ブックが存在しなかったときの処理
        Else
            Workbooks.Open FileName:="C:\Work\" & Cells(i, 1), Password:=Cells(i, 2)
            ''開いたブックに対しての処理
            ActiveWorkbook.Close False
        End If
    Next i
End Sub

あるいは、

Sub Macro8()
    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Dir("C:\Work\" & Cells(i, 1)) <> "" Then
            Workbooks.Open FileName:="C:\Work\" & Cells(i, 1), Password:=Cells(i, 2)
            ''開いたブックに対しての処理
            ActiveWorkbook.Close False
        End If
    Next i
End Sub

みたいに。

さて、次は「2.フォルダ内に存在するブックをすべて順番に開いて処理する」です。こちらは、フォルダ内のファイルが中心ですから、基本的な考え方は次のようになりますね。

Sub Macro9()
    Dim A As String
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Workbooks.Open FileName:="C:\Work\" & A, Password:=パスワード
        ''開いたブックに対しての処理
        ActiveWorkbook.Close False
        A = Dir()
    Loop
End Sub

先にも出てきましたが、この手の「別ファイルを扱う」マクロで、Dir関数は必須中の必須です。よく分からない…という方も多いようなので、YouTubeにメチャクチャ詳しい動画をあげています。自信がない方は必ず見てください。絶対に理解できます。

■実務では必須のDir関数

さて、今回はフォルダ内に存在している全ブックを開きます。先のようにリストに記載されているブックだけを処理するのではありません。もし、ファイル名とパスワードの対応リストが

のようになっていたら、Dir関数で取得したファイル名を、対応リストのファイル名(ここではA列)内で探さなければなりません。だって、必ずしも対応リストの順番で、ファイル名を取得できるとは限りませんからね。いや、もっと言えば、Dir関数で取得したファイル名が、対応リスト内に存在するかどうかが問題です。ファイル中心の処理では、これが最も重要なポイントでしょう。位置を調べたり、存在を確認するには、いろいろな方法があります。とはいえ今回は、もしファイル名がA列で見つかった場合、その右隣(B列)にあるパスワードを取得しなければなりません。まぁ、一般的にはこんなとき、Findメソッドで検索するのがセオリーでしょうね。

Sub Macro10()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            ''リストに存在しないときの処理
        Else
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            ''開いたブックに対しての処理
            ActiveWorkbook.Close False
        End If
        A = Dir()
    Loop
End Sub

Findメソッドで文字列を検索するときは、万が一の誤動作を避けるためにも、"完全一致"で検索するか"部分一致"で検索するかを毎回指定するようにしましょう。今回は「Lookat:=xlWhole」なので"完全一致"です。参考までに「リストに存在しないとき」は何も処理をしないのでしたら、次のようにします。

Sub Macro10()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If Not B Is Nothing Then
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            ''開いたブックに対しての処理
            ActiveWorkbook.Close False
        End If
        A = Dir()
    Loop
End Sub

この Not の使い方は、ご存じない方が多いので、知らなかったのでしたら覚えましょう。ここで注目すべきは、最後に実行する「A = Dir()」の位置です。この「A = Dir()」を、If～End Ifの中に入れてしまう間違いが多いです。

Sub Macro10()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If Not B Is Nothing Then
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            ''開いたブックに対しての処理
            ActiveWorkbook.Close False
            A = Dir()    ''←間違い
        End If
    Loop
End Sub

この「A = Dir()」というのは、"次のファイル名を取得する"という処理です。直前のIf～End Ifで判定しているのは"取得したファイル名が対応リストに存在するか"です。よく考えてください。取得したファイル名が、対応リスト内に存在しようが、存在しなかろうが、いずれにしても、次のファイル名を取得しなければなりません。ですから、"存在するか"を判定している条件分岐の外に書かなければなりません。もし、上記のように書いてしまうとどうなるか、動作を考えてください。恐ろしいことが起こります。

3.すべてのブックからパスワードを解除する

これも以前、実際に質問された内容です。ここまでの解説を組み合わせるとできます。今回は、次のようなケースを想定しましょう。

ブックが保存されているフォルダは C:\Work\ である
フォルダ内の全ブックを対象とする
ファイル名とパスワードの対応リストはアクティブシートにある
ファイル名が対応リストに存在しないかもしれない
対応リストのパスワードが間違っているかもしれない
無事に開けたらパスワードを解除して上書き保存する

まず、フォルダ内の全ファイルを処理するのですから、大外はこうなります。

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

Dir関数で取得したファイル名が、対応リストのA列に存在するかどうかを判定します。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        
        A = Dir()
    Loop
End Sub

判定した結果で処理を分岐します。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            ''リストに存在しないときの処理
        Else
            ''開いたブックに対しての処理
        End If
        A = Dir()
    Loop
End Sub

今回は「リストに存在しなかったファイル名はイミディエイトウィンドウに出力する」としましょう。まぁ、ここはケースバイケースです。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            ''開いたブックに対しての処理
        End If
        A = Dir()
    Loop
End Sub

Dir関数で取得したファイル名がリストに存在したら、パスワードを指定して開きます。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
        End If
        A = Dir()
    Loop
End Sub

このとき、もしリストのパスワードが間違っていたらエラーになりますので回避します。エラーにならなかったら、無事に開けたということです。On Error Resume Nextはエラーを無視しろという命令です。もし何らかのエラーが発生すると、ErrオブジェクトのNumberプロパティに、0より大きい数値が格納されます。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            On Error Resume Next
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            If Err.Number > 0 Then
                ''パスワードが間違っていたときの処理
            Else
                ''開いたブックに対しての処理
            End If
        End If
        A = Dir()
    Loop
End Sub

ここもケースバイケースですが、今回は「パスワードが間違っていたファイル名はイミディエイトウィンドウに出力する」とします。

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            On Error Resume Next
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            If Err.Number > 0 Then
                Debug.Print A & "はパスワードが違います"
            Else
                ''開いたブックに対しての処理
            End If
        End If
        A = Dir()
    Loop
End Sub

さあ、最後に、無事に開けたブックのパスワードを解除するのですが、このとき「開いたブックが必ずアクティブブックになっている」というイメージを持ってください。しつこいようですけど、そうしたイメージを持てないとプログラミングはできません。アクティブブックのパスワードを解除するには、Passwordプロパティに空欄("")を指定します。このへんの話は下記ページをご覧ください。

パスワードをつけて保存する

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            On Error Resume Next
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            If Err.Number > 0 Then
                Debug.Print A & "はパスワードが違います"
            Else
                ActiveWorkbook.Password = ""
            End If
        End If
        A = Dir()
    Loop
End Sub

パスワードを解除したら、開いたブックを閉じます。ただし、ここで注意が必要です。パスワードを解除したということは「ブックに変更を加えた」ということです。したがって、普通にCloseメソッドで閉じようとすると、変更を保存するかどうかの確認が表示されます。

こんなときは、加えた変更を保存して閉じるように、Closeメソッドの引数SaveChangesにTrueを指定します。引数SaveChangesは先頭の引数ですから「SaveChanges:=」は省略できます。引数の省略に関しては、下記ページで詳しく解説していますので、なぜ省略できるのかが分からない方はご覧ください。

引数名って書かなくていいの？

Sub Macro11()
    Dim A As String, B As Range
    A = Dir("C:\Work\*.xlsx")
    Do While A <> ""
        Set B = Range("A:A").Find(What:=A, Lookat:=xlWhole)
        If B Is Nothing Then
            Debug.Print A & "は存在しません"
        Else
            On Error Resume Next
            Workbooks.Open FileName:="C:\Work\" & A, Password:=B.Offset(0, 1)
            If Err.Number > 0 Then
                Debug.Print A & "はパスワードが違います"
            Else
                ActiveWorkbook.Password = ""
                ActiveWorkbook.Close True
            End If
        End If
        A = Dir()
    Loop
End Sub

完成です。イレギュラーケースを作ってテストしてみました。もちろん、無事に開けたブックのパスワードはすべて解除されていました。

最後に雑感です。パスワードに関する質問って、今までにたくさん受けてきました。それだけ悩んでいる方が多いのでしょう。ただ、今回ご紹介したように「パスワードを設定する」「パスワードを解除する」という処理自体は、それほど難しくありません。確かに、最近のExcelではマクロ記録で記録されませんけど、調べれば分かりそうなものです。にもかかわらず、パスワードを設定/解除するようなマクロで悩んでいるということは、要するに難しいのは"パスワードに関すること"ではなく、別ブックを開いて操作するときに不可欠の「今、Excelがどうなっているか」を"イメージすること"です。こればかりは、何度も何度もマクロを書いて動かして、イメージ力を磨くしかありません。間違っても、ネットにあるコードをコピペして、マクロを作った気になっているだけでは、絶対に身につきません。だから、そうしたマクロを作れない方が多いのではないかと。そんな気がします。