こんにちは、まりもです。
「Excel VBAの勉強をしたけど、実際に仕事で使えるような複雑なコードが書けない」
そんな悩みをお持ちではないでしょうか?
このブログでは、私が日々の仕事で実際に作成し活用しているコードを紹介します。
今回は、セルにファイルパスを設定しておき、それを参照して開くコードです。
GetOpenFilenameメソッドを用いてファイルを開く作業を自動化するコードは
こちらを参考にしてください。

用意するもの
このマクロを動かす為に、事前に2つのファイルを用意しておきます。
マクロを書くExcelファイル
このExceファイルの標準モジュールにコードを書いていきます。
ファイル名は何でもOK。
「設定」と名前を付けたシートのA3セルに、開きたいファイルのファイルパスを入力します。

指定して開くExcelファイル
今回はファイル名を「testdata.xlsx」とします。
1シート目の内容をコピペしてくるコードなので、1シート目に何か入力しておきます。
やること
マクロを書くExcelファイルから「testdata.xlsx」を開き、
「testdata.xlsx」にあるデータをコピーし、
マクロを書くExcelファイルの1シート目にペーストします。
実際のコード
Sub ファイルパスをセルに設定しておく()
Dim WB(1) As Workbook
Dim WS(1) As Worksheet
Dim MyFile, myFilename As String
Application.ScreenUpdating = False
myFilename = ThisWorkbook.Worksheets("設定").Range("A3").Value
Workbooks.Open Filename:=myFilename
MyFile = Mid$(myFilename, InStrRev(myFilename, "\") + 1)
Set WB(0) = Workbooks(MyFile)
Set WB(1) = ThisWorkbook
Set WS(0) = WB(0).Worksheets(1)
Set WS(1) = WB(1).Worksheets(1)
WS(0).Cells.Copy _
Destination:=WS(1).Cells(1, 1)
Workbooks(MyFile).Close
Application.ScreenUpdating = True
End Sub
解説
どのような動きになるか解説していきます。
ScreenUpdatingプロパティ,Openメソッド
Dim WB(1) As Workbook
Dim WS(1) As Worksheet
Dim MyFile, myFilename As String
Application.ScreenUpdating = False
myFilename = ThisWorkbook.Worksheets("設定").Range("A3").Value
Workbooks.Open Filename:=myFilename
ScreenUpdatingプロパティで画面の更新を停止しておきます。
処理の高速化にもなりますし、画面のちらつきが無くなってシステム感が出ます。
myFilenameに、先ほどセルに入れておいたファイルパスの値を入れます。
OpenメソッドでmyFilenameを開きます。
Mid関数、InStrRev関数
MyFile = Mid$(myFilename, InStrRev(myFilename, "\") + 1)
myFilenameから、Mid関数とInstrRev関数を組み合わせてファイル名だけを取得し、
MyFileに格納します。
InstrRev関数は文字列の中から指定した文字列を最後の文字位置から検索を開始し、
最初に見つかった文字位置を返す関数です。
Workbookオブジェクト、Worksheetオブジェクト
Set WB(0) = Workbooks(MyFile)
Set WB(1) = ThisWorkbook
Set WS(0) = WB(0).Worksheets(1)
Set WS(1) = WB(1).Worksheets(1)
Dimで変数宣言しておいたWorhbookとWorksheetに変数を格納します。
WB(0)にはMyFileを、WB(1)にはThisWorkbookを入れます。
WS(0)にはWB(0)の1シート目を、WS(1)にはWB(1)の1シート目を格納します。
Copyメソッド、Closeメソッド
WS(0).Cells.Copy _
Destination:=WS(1).Cells(1, 1)
Workbooks(MyFile).Close
Application.ScreenUpdating = True
Copyメソッドを用いてWS(0)のセルをWS(1)のCells(1, 1)にコピーペーストします。
Closeメソッドを用いてMyFileを閉じます。
ScreenUpdatingプロパティをTrueにして画面更新を再開します。
まとめ
今回は、ファイルパスを設定したセルを参照してファイルを自動で開いて内容を抽出する
一連の流れを説明しました。
1つの動作の中でも様々なメソッドを使用していることが
わかったかと思います。
「マクロを勉強したけどいまいち書き方がわからない」
そんな方の参考になれば幸いです。
ではまた。
コメント