【Excel VBA】ファイルパスを設定したセルを参照してファイルを自動で開く

こんにちは、まりもです。

「Excel VBAの勉強をしたけど、実際に仕事で使えるような複雑なコードが書けない」

そんな悩みをお持ちではないでしょうか？

このブログでは、私が日々の仕事で実際に作成し活用しているコードを紹介します。

今回は、セルにファイルパスを設定しておき、それを参照して開くコードです。

GetOpenFilenameメソッドを用いてファイルを開く作業を自動化するコードは
こちらを参考にしてください。

【Excel VBA】ファイルを選択して開く実践的なコード(GetOpenFilenameメソッド)

こんにちは、まりもです。「Excel VBAの勉強をしたけど、実際に仕事で使えるような複雑なコードが書けない」そんな悩みをお持ちではないでしょうか？このブログでは、私が日々の仕事で実際に作成し活用しているコードを紹...

この記事を読んで出来ること

別ブックのファイル名を指定して開くことができる
指定して開いたファイルの内容を抽出することができる
エクセルのシート上で参照ファイルを変更できる

この記事で使うメソッドや関数、ステートメント、プロパティ

InStrRev
Mid
Workbook
Worksheet
ScreenUpdating
Copy
Close
Open

用意するもの
やること
実際のコード
解説
まとめ

用意するもの

このマクロを動かす為に、事前に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つの動作の中でも様々なメソッドを使用していることが
わかったかと思います。

「マクロを勉強したけどいまいち書き方がわからない」

そんな方の参考になれば幸いです。

ではまた。