このサイトはCocoonを使っています。現在「ミックスブルー [作者: y.hiroaki氏]」スキンを適用中です(^^)/

【Excel VBA】ファイルを開いてデータを取得する(その1)

スポンサーリンク

仕事でExcelを使っています。作業能率を上げるためvbaを使って効率化しています。せっかくなので自分が使っているvbaを公開していこうと思います
ただ、web上にはvbaに関する優れた解説サイトが多数あるので今更そこに参入していくつもりはありません。解説の部分はそちらにお任せするようにします
いつも汎用でだれでも使えて活用できるように考えてvbaを使っていますので、そのポリシーで書いていこうと思います

まず初回(1)ではこんな感じから書いてみます

・ファイルを開いてデータを取得する作業の自動化
・手作業で行った場合と比較してどれだけ効率化できるのか
・汎用で使えるように考えるとこんな感じ

スポンサーリンク

ファイルを開いてデータを取得する作業の自動化

目的のファイルを開くにはいくつか方法があります

ファイルをダイアログから選択指定して開く

・指定フォルダに目的のファイル以外のファイルも保存されている場合はこれ
・ApplicationオブジェクトのGetOpenFilenameメソッドを使っています

'別ブックからデータ取得する
Sub Open_Files_Test()
    Dim selectFileName As Variant
    Dim xls As New Excel.Application '新規にExcelを起動
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim Mysh As Worksheet
    Dim shData As Variant
    Dim n As Long
    
    On Error GoTo ErrHandler
    Set Mysh = ThisWorkbook.ActiveSheet
    With Mysh
        'ファイル選択ダイアログを表示(ExcelとCSVをフィルタにセット)
        selectFileName = _
            Application.GetOpenFilename( _
                FileFilter:="Microsoft Excel ブック,*.xls*,CSVファイル,*.csv", _
                FilterIndex:=1, _
                Title:="ファイルを選択してください", _
                MultiSelect:=False)
        If selectFileName <> "" Then
            n = 1
            '取得する情報の見出しを列先頭に記入
            .Cells(n, 1).Value = "ファイル名"
            .Cells(n, 2).Value = "シート名"
            .Cells(n, 3).Value = "取得したデータ"
            'ファイル(ブック)をリードオンリーで開く
            Set wb = xls.Workbooks.Open(fileName:=selectFileName, _
                                            UpdateLinks:=0, ReadOnly:=True)
            'シートはshに
            Set sh = wb.Worksheets(1) 'シート名を指定する場合()内に記述
            shData = sh.Range("A3").Value
            '変数のデータを書き込む
            n = n + 1
            .Cells(n, 1).Value = wb.name    'ファイル名
            .Cells(n, 2).Value = sh.name    'シート名
            .Cells(n, 3).Value = shData     'セルのデータ
            wb.Close savechanges:=False         '開いたファイルを閉じる
        Else
            MsgBox ("ファイルを選択しないで終了")
            Exit Sub
        End If
    End With
    MsgBox "選択したファイルの処理が終了しました", vbOKOnly + vbInformation, "ファイル処理"
    xls.Application.Quit
    Set xls = Nothing
    Exit Sub

ErrHandler:
    MsgBox "「" & selectFileName & "」の処理中にエラーが発生しました" & vbCrLf & _
                                Err.Description, vbExclamation, "ファイル処理"
    xls.Application.Quit
    Set xls = Nothing
End Sub

手作業で行った場合と比較してどれだけ効率化できるのか

それでは実際にやってみて比較してみましょう

まず手作業で1つの別ブックからセルのデータを取得する時間は?

実際にストップウォッチで計ってみました
1つの別ブックを開いてセルのデータをコピー(Ctrl+V)
貼り付け先のブックに戻って貼り付け(Ctrl+V) これを10回やってみた
結果=「平均で21秒」

最初に書いたコードで実行してみた時間は?

こちらは、タイマーをコード内にセットして計りました
ダイアログでファイルを選択する作業に時間をとられましたが10回の平均は
結果=「3.3秒」 コピペの部分は一瞬で終わる

ファイルが100個なら約30分の作業時間短縮!

汎用で使えるように考えるとこんな感じ

ファイルをダイアログから(複数選択可)指定して開く

・最初のコードのファイル選択ダイアログ部分を複数選択可能に変更
・複数選択された場合のループ処理を追加
これで一つずつファイルを選択する作業を短縮できます

'別ブックからデータ取得(複数ファイルの選択可能で処理する)
Sub Open_Files_DataFetch()
    Dim selectFileName As Variant
    Dim OpenFileName As Variant
    Dim xls As New Excel.Application '新規にExcelを起動
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim Mysh As Worksheet
    Dim shData As Variant
    Dim n As Long
    
    On Error GoTo ErrHandler
    Set Mysh = ThisWorkbook.ActiveSheet
    With Mysh
        'ファイル選択(複数可)ダイアログを表示(ExcelとCSVをフィルタにセット)
        selectFileName = _
            Application.GetOpenFilename( _
                FileFilter:="Microsoft Excel ブック,*.xls*,CSVファイル,*.csv", _
                FilterIndex:=1, _
                Title:="ファイルを選択してください(複数可)", _
                MultiSelect:=True)
        n = 1
        '取得する情報の見出しを列先頭に記入
        .Cells(n, 1).Value = "ファイル名"
        .Cells(n, 2).Value = "シート名"
        .Cells(n, 3).Value = "取得したデータ"
        
        '選択されたファイルに対する処理
        If IsArray(selectFileName) Then
            '全てのファイルに繰り返し処理を行う
            For Each OpenFileName In selectFileName
                n = n + 1
                'ファイル(ブック)をリードオンリーで開く
                Set wb = xls.Workbooks.Open(fileName:=OpenFileName, _
                                            UpdateLinks:=0, ReadOnly:=True)
                Set sh = wb.Worksheets(1) 'シート名を指定する場合()内に記述
    '//////////ここに開いた別ブックからデータを取得する処理を入れる//////////
                '開いた別ブックのデータを変数に保存
                shData = sh.Range("A3").Value
                '変数のデータを書き込む
                .Cells(n, 1).Value = wb.name    'ファイル名
                .Cells(n, 2).Value = sh.name    'シート名
                .Cells(n, 3).Value = shData     'セルのデータ
    '//////////ここまで開いた別ブックからデータを取得する処理を//////////////
                wb.Close savechanges:=False         '開いたファイルを閉じる
            Next
        Else
            MsgBox ("ファイルを選択しないで終了")
            Exit Sub
        End If
    End With
    MsgBox "選択ファイルの処理終了!", vbOKOnly + vbInformation, "ファイル一括処理"
    xls.Application.Quit
    Set xls = Nothing
    Exit Sub

ErrHandler:
    MsgBox "「" & OpenFileName & "」の処理中にエラーが発生しました" & vbCrLf & _
                                Err.Description, vbExclamation, "ファイル一括処理"
    xls.Application.Quit
    Set xls = Nothing
End Sub

‘//////////ここに開いた別ブックからデータを取得する処理を入れる//////////
・この部分に開いた別ブックのデータを使いまわす作業を書いていきます
・ただ、あまり複雑で長い処理をさせる場合は、その部分は別のプロシージャを呼び出して処理させるようにした方が良いでしょう
今後、具体例を別途書いていくようにしたいと思います
・次回は、ファイルを開く別の方法を紹介します

サンプルファイルをダウンロードできます(下記リンク先へ)

記事で使用したサンプルファイルがダウンロードできるページを設置しました
こちらからご利用ください