こんにちは、まりもです。
「Excel VBAの勉強をしたけど、実際に仕事で使えるような複雑なコードが書けない」
そんな悩みをお持ちではないでしょうか？
このブログでは、私が日々の仕事で実際に作成し活用しているコードを紹介します。
今回は、GetOpenFilenameメソッドを用いてファイルを開く作業を自動化するコードです。
ファイルパスを設定したセルを参照してファイルを自動で開いて内容を抽出するコードは
こちらを参考にしてください。
用意するもの
このマクロを動かす為に、事前に2つのファイルを用意しておきます。
マクロを書くExcelファイル
このExceファイルの標準モジュールにコードを書いていきます。
ファイル名は何でもOK。
指定して開くExcelファイル
呼び出す用のExcelファイルは、ファイル名のどこかに「data」を入れてください。
また、1シートめのA1セルに「No」と入れておいてください。
私は「testdata.xlsx」という名前をつけて以下のようなデータセットを作成しました。
やること
マクロを書くExcelファイルから「testdata.xlsx」を開き、
「testdata.xlsx」にあるデータをコピーし、
マクロを書くExcelファイルにペーストします。
実際のコード
Sub ファイルを選択して開く()
Dim myFilename As String
Dim myStr As String
myFilename = Application.GetOpenFilename(Title:="ファイルを選択してください")
'キャンセルボタンを押した場合
If StrConv(myFilename, vbUpperCase) = "FALSE" Then
Exit Sub
End If
'誤ったファイルを開いた場合
If Not myFilename Like "*data*" Then
MsgBox "dataファイルではない？", vbOKOnly, "Information"
Exit Sub
End If
myStr = Mid$(myFilename, InStrRev(myFilename, "\") + 1) 'book名取得
Application.ScreenUpdating = False
ThisWorkbook.Worksheets(1).Cells.ClearContents
Workbooks.Open Filename:=myFilename, ReadOnly:=True
'誤ったファイルではないか再度確認
If Not Workbooks(myStr).Worksheets(1).Cells(1, 1).Value Like "*No*" Then
MsgBox "該当ファイルではありません", vbOKOnly, "Information"
Workbooks(myStr).Close
Exit Sub
End If
Workbooks(myStr).Worksheets(1).Cells.Copy _
Destination:=ThisWorkbook.Worksheets(1).Cells(1, 1)
Workbooks(myStr).Close
Application.ScreenUpdating = True
End Sub解説
どのような動きになるか解説していきます。
GetOpenFilenameメソッド
myFilename = Application.GetOpenFilename(Title:="ファイルを選択してください")このGetOpenFilenameメソッドを使うと【ファイルを開く】ダイアログボックスが表示されます。
Titleで指定した文言が左上に表示されます。
StrConv関数、Ifステートメント、Exitステートメント
'キャンセルボタンを押した場合
If StrConv(myFilename, vbUpperCase) = "FALSE" Then
Exit Sub
End If【ファイルを開く】ダイアログボックスでキャンセルボタンを選択した場合、
戻り値は「False」となります。
StrConv関数のvbUpperCaseで大文字変換した文字列が「FALSE」となった場合、
Exit subで処理を終了します。
Ifステートメント(Not条件)、Like演算子、MsgBox関数
'誤ったファイルを開いた場合
If Not myFilename Like "*data*" Then
MsgBox "dataファイルではない？", vbOKOnly, "Information"
Exit Sub
End If誤ったファイルを開いた場合に処理を進めないようにします。
ファイル名はLike演算子で文字列比較します。
ファイル名が違う場合はMsgBoxを出して、Exit subで強制終了します。
Mid関数、InStrConv関数
myStr = Mid$(myFilename, InStrRev(myFilename, "\") + 1) 'book名取得ファイルを選択すると、myFilenameにはGetOpenFilenameメソッドの戻り値として
ファイルのフルパスとファイル名が格納されています。
どのように格納されているかMsgBoxで表示させてみます。
ここからMid関数とInstrRev関数を組み合わせてファイル名だけを取得し、
myStrに格納します。
InstrRev関数は文字列の中から指定した文字列を最後の文字位置から検索を開始し、
最初に見つかった文字位置を返す関数です。
ScreenUpdatingプロパティ
Application.ScreenUpdating = Falseこれ以降、セルのコピーペースト操作等をするので、ScreenUpdatingプロパティで
画面の更新を停止しておきます。
処理の高速化にもなりますし、画面のちらつきが無くなってシステム感が出ます。
ClearContentsメソッド
ThisWorkbook.Worksheets(1).Cells.ClearContents別ブックからコピーしてくる前に現在のシートのデータを消しておきます。
ClearContentsメソッドは数式と文字のみを消すメソッドです。
Openメソッド
Workbooks.Open Filename:=myFilename, ReadOnly:=True先ほどGetOpenFilenameでmyFilenameに格納したファイルを指定します。
ReadOnly:=Trueとして読み取り専用モードで開きます。
Closeメソッド
'誤ったファイルではないか再度確認
If Not Workbooks(myStr).Worksheets(1).Cells(1, 1).Value Like "*No*" Then
MsgBox "該当ファイルではありません", vbOKOnly, "Information"
Workbooks(myStr).Close
Exit Sub
End Ifファイルを開いた後、再度該当シートに抽出対象の値があるか確認します。
無い場合はCloseメソッドでブックを閉じ、処理を終了します。
Copyメソッド
Workbooks(myStr).Worksheets(1).Cells.Copy _
Destination:=ThisWorkbook.Worksheets(1).Cells(1, 1)
Workbooks(myStr).Close
Application.ScreenUpdating = True開いたmyStrファイルの1シート目にある値をコピーし、
現ブックの1シート目にペーストします。
コピー後、用の無くなったmyStrファイルは閉じます。
ScreenUpdatingプロパティをTrueにして画面更新を再開します。
まとめ
今回は、ファイルを指定して開いて内容を抽出する一連の流れを説明しました。
1つの動作の中でも様々なメソッドを使用していることが
わかったかと思います。
「マクロを勉強したけどいまいち書き方がわからない」
そんな方の参考になれば幸いです。
ではまた。
コメント