サイトアイコン エントレ|演劇動画ニュース

エクセルマクロ(EXCEL VBA) その21 別ファイルを参照してコピー

エクセルマクロ(EXCEL VBA)

別ファイルを参照してコピー

ここまでで、セルからセルへのコピー、別シートのセルからのコピーなどをやってきましたが、別ファイルにあるセルからコピーすることはできるのでしょうか?

結論としては「できる」ので、そのやり方についてご紹介します。

こんなエクセルファイルがあったとします。(その20の宿題で使ったファイルです。)

エクセルファイルをダウンロード

これをダウンロードして、一つのフォルダの中に入れておきます。
同じフォルダで、新規のマクロ有効ブック yomikomi.xlsm を作っておきます。

yomikomi.xlsm の方を開いて、いつものように開発→Visual Basic、
挿入→標準モジュールからプロシージャを作りましょう。

2つのファイルを指定する

今回は2つのエクセルファイルを扱うので、区別するために名前を付けておきましょう。
今開いているエクセルファイルをwb1とするためには、以下のように書いてください。

Set wb1 = ThisWorkbook

エクセルファイルを変数に入れる場合には、Setというのが必要です。

同様に、参照先のファイルを指定しましょう。
参照先のファイルはまだ開いていない閉じてる状態なので、開く(Open)必要があります。
ちょっと長いですが、こんな風に書いてください。

Set wb2 = Workbooks.Open(ThisWorkbook.Path & "¥hw20_sakuhin_list.xlsx")

赤線が「エクセルファイルを開く」という命令、
青線が「このマクロファイルがあるフォルダ」という部分で、緑線は、新たに開きたいファイル名を「&」でつないでいるという部分です。¥は、フォルダの階層を降りるときの記号ですので、ファイル名の前につけておく必要があります。

今回はThisWorkbook.Pathという風に書きましたが、フルパスで書くこともできます。
ファイルを右クリックしてプロパティを開くと、場所: というところにそのファイルのフルパス(完全な住所)が書いてあるのでそれをコピペしても大丈夫です。その場合も、パスとファイル名は「¥」でつないでください。

 

ファイル間でコピー

これで2つのファイルを扱う準備ができたので、hw20_sakuhin_list.xlsx の「観劇リスト」のA8にある「リチャード三世」を、
今のファイルのA1に入れてみましょう。

wb1.Sheets("sheet1").Cells(1, 1) = wb2.Sheets("観劇リスト").Cells(8, 1)

と書いてください。

これを実行すると、

参照先のファイルから「リチャード三世」を取り出すことができました!

 

ファイルを閉じておく

参照元のファイルは閉じておきましょう。

wb2.Close

こちらを追加してください。

これで、参照元のファイルは一度開いて、その後閉じられるようになります。

 
今回の最終的なコード

Sub yomikomi()
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(ThisWorkbook.Path & "¥hw20_sakuhin_list.xlsx")

wb1.Sheets("sheet1").Cells(1, 1) = wb2.Sheets("観劇リスト").Cells(8, 1)
wb2.Close
End Sub

※追記

Mac版エクセルでこのコードを実行してみたところ、パスがうまく通りませんでした。
「¥」となっているところを「:」に置き換えると上手くいきましたのでMacユーザーの方は修正してお使いください。
 
 

宿題-21

先程の hw20_sakuhin_list.xlsx を参照し、
「観劇リスト」のA列を新規ファイルのA列にコピーし、
「良かった公演」のA列を新規ファイルのB列にコピーしてください。

▼ クリックで解説を表示

解説

wb2の列(Column)をコピー(Copy)して、wb1の列に貼り付ける(PasteSpecial)という作業なので、
A列は、

wb2.Sheets("観劇リスト").Columns("A").Copy
wb1.Sheets("Sheet1").Columns("A").PasteSpecial

と書きます。

B列も同様に、

wb2.Sheets("良かった公演").Columns("A").Copy
wb1.Sheets("Sheet1").Columns("B").PasteSpecial

とすれば、OKです。

ちなみに、ファイルが開いたり閉じたるする動きが見えていると思いますが、コードの冒頭に、

Application.ScreenUpdating = False

とすると、開いたり閉じたりして見えなくなってちょっと快適です。

最終的なコードはこちらです。

Sub otherBook()
'ファイルを開いているの表示しない
Application.ScreenUpdating = False

'エクセルファイルを指定
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(ThisWorkbook.Path & "¥hw20_sakuhin_list.xlsx")

'参照先の列をコピーして、作業ファイルに貼り付け
wb2.Sheets("観劇リスト").Columns("A").Copy
wb1.Sheets("Sheet1").Columns("A").PasteSpecial

wb2.Sheets("良かった公演").Columns("A").Copy
wb1.Sheets("Sheet1").Columns("B").PasteSpecial

'参照先のファイルを閉じる
wb2.Close

End Sub

答えファイルのダウンロード

エクセルマクロ(EXCEL VBA) その22 今日の日付を取得(NowとDate)

 
(文:森脇孝/エントレ)

モバイルバージョンを終了