Excel

【エクセルVBA】ファイル内のエクセルデータを転記する

2020-06-15

フォルダ内にあるエクセルファイルのデータを転記する方法です。

転記するデータが多い場合は、手作業でコピー&ペーストを繰り返すと大変すぎです。VBAを使えば早く処理ができます。

Power Queryを使う方法もあり。

→ エクセルのクエリでデータ結合する方法

ここで記載しているコードは、自分のおぼえがきです。もし参考にする場合は、これ以外にも方法はありますので、ご自身でアレンジしてみてください。

エクセルのバージョン:Office Home & Business 2019

やりたいこと・転記元のデータと転記先データ

転記元のデータは3つ

  1. 購入品_1月.xlsx
  2. 購入品_2月.xlsx
  3. 購入品_3月.xlsx

シートは全て「購入品」のみ

転記元データ

この3つのデータを転記するデータ

シートは全て「購入品」

転記先エクセルデータ

転記後

エクセルVBA転記

データの保存先

エクセルの転記・VBA

エクセルVBA

コードを入力する場所とコードについて。

コード

  • データの選択は、ダイアログボックスを表示する方法
  • 転記元のデータのコピーは書式も含めてます
  • D列にファイル名を入力する
Sub f_tenki()
Dim fPath As String
Dim motoFile As String
Dim sakiSheet As Worksheet
Dim sakiLRow, motoLRow, sakiLRow2 As Long   
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            fPath = .SelectedItems(1)
        End If
    End With
    If fPath = "" Then End
    
    With Application
      .ScreenUpdating = False
      .DisplayAlerts = False
    End With
        
    Set sakiSheet = ActiveWorkbook.Sheets("購入品")
    
    fPath = fPath & "\"
    motoFile = Dir(fPath & "*.xlsx")
        
    Do While motoFile <> ""
    
       sakiLRow = sakiSheet.Cells(Rows.Count, 1).End(xlUp).Row
       
       Workbooks.Open Filename:=fPath & motoFile, UpdateLinks:=True, ReadOnly:=True
       motoLRow = Cells(Rows.Count, 1).End(xlUp).Row
        
       Range("A2:C" & motoLRow).Copy sakiSheet.Range("A" & sakiLRow + 1)
       
       sakiLRow2 = sakiSheet.Cells(Rows.Count, 1).End(xlUp).Row
       sakiSheet.Range("D" & sakiLRow + 1 & ":D" & sakiLRow2) = motoFile
              
       Workbooks(motoFile).Close
       motoFile = Dir()
       
     Loop
    
    Range("A1").Select
    
    With Application
      .ScreenUpdating = True
      .DisplayAlerts = True
    End With
  
End Sub

エクセルVBAについて

私は仕事でVBAを使うとき、モジュールは自分の管理しているエクセルファイルに保存しています。

エクセルの転記・VBA

VBAを実行するとき、自動化したいファイルとコードを入力しているエクセルファイルを開きます。

エクセルの転記・VBA

実行するときは、Alt + F8 キーより実行しています。

使わないコードは、「sub」の前に「private」を入れておき、「Alt + F8」で表示されないようにするなどの方法があります。

以下は、他の人とコードを共有しない理由です。

コードを共有しない理由1

自分が想定していた以外の操作をする可能性がある

コードを共有する場合は、いろんな問題が出てきます。例えば、

  • エラー処理等しなくてはならない
  • 操作説明が必要になり、早く処理することが目的なのに、時間がかかってしまう。

自分のみでVBAを使う場合は、シンプルなコードですみます。他の人が使うことになると、自分が想像していなかった操作をすることがあったりして、結果的にエクセルの基本機能で作業した方が早い。

コードを共有しない理由2

残されたコードの修正等は困ることになる可能性がかなり高い

自分が部署異動したり、退職した場合はに残されたコードは修正が大変になる。エラーが起きた時も困る。

また時間が経てば、作った当時と状況が変わっていいて、使えないものになっていることもある。

VBAの実行

VBAを実行するファイル以外にコードを入力している場合の方法例です。

(1)[ Alt ]  + [ F8 ] キーを押します。

(2)マクロ名より対象のコードを選択し、「実行」をクリックします。

エクセルVBAの実行

(3)転記元のデータを保存しているフォルダを選択します。

エクセルVBAの実行

(4)「OK」をクリックします。

エクセルVBAの実行

-Excel

© 2020 パソコンメモ