ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化　について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

トップ > VBAの勉強 > 【VBA】大量のエクセルシートにテーブルを設定する｜Power BI対策でテーブルを設定

【VBA】大量のエクセルシートにテーブルを設定する｜Power BI対策でテーブルを設定

VBAの勉強

このブログの記事一覧にもどる

Power BIというMicrosoft社のBIツールを利用するに当たり、大量にエクセルファイルにテーブルをセットする必要が生じたので、それを解決するVBAのサンプルコードをご紹介します。より深く理解したい方は、関連記事やブログ後半の解説を併せてご覧下さい。

＜関連記事＞

目次

大量のエクセルシートにテーブルを設定する

単刀直入にサンプルコードを入手したい人の為に、いきなりサンプルコードを書きます。まずはコアとなるテーブルをセットする部分のコードです。

次々にテーブルをセットするサンプルコード

Sub テーブルをセットする()
    
    Dim rw As Long '最終行を取得する
    Dim clm As Long '最終列を取得する
    Dim rng As Range 'テーブルをセットする範囲を取得する
    
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    clm = Cells(1, Columns.Count).End(xlToLeft).Column
    rng = Range(Cells(1, 1), Cells(rw, clm))
    
    ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "テーブル-" & ActiveSheet.Index
    
    ActiveWorkbook.Close SaveChanges:=True
    
End Sub

左端、1行目から表が始まっている前提です。次にデスクトップに保存してあるエクセルファイルに次々にテーブルを設定するように変更します。

Sub テーブル作成の処理の流れをコントロールする()
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'このプロシジャはデスクトップに保存してある全てのエクセルファイルの全てのシートに
'テーブルを設定するための処理の流れを記述する
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    Const folder As String = "C:\Users\hogehoge\Desktop\" 'ここは実際のフォルダ名にする
    Dim temp_file As String '順にファイルをチェックしていくときに、ファイル名を一時的に格納する
    Dim ws As Worksheet '順にファイル内のシートを操作していくときにシートを一時的に格納する
    
    temp_file = Dir(folder & "")  '初期設定
    
    Do While temp_file <> "" 'Dirが何も返さなくなるまで、つまりファイルを全てチェックし終わるまで繰り返す
        
        Workbooks.Open Filename:=folder & temp_file
        
        For Each ws In Worksheets
            ws.Activate
            Call テーブルをセットする
        Next ws
        
        temp_file = Dir() '引数をとらなければ次の未検索のファイルを探す。未検索のファイルがなくなると””を返す
    Loop
    
End Sub

Dir（）関数を使って次々に特定のフォルダ内のファイル名を取得してそのファイルを開いていきます。実際にこのサンプルコードを使う時には、「ここは実際のフォルダ名にする」の部分を実際のフォルダ名に変更します。元のデータをバックアップとして残すために、テーブルセット用のフォルダを用意して、そこにテーブルをセットしたいファイルをコピーして作成しておくのがオススメです。VBAで加工したファイルはCtrl+Zで戻せないので、バックアップを忘れないようにしましょう。

ちょっとだけ解説

自分で自在にこういったコードを作れるようになりたい人や、上記のサンプルコードを改造したい人の為に少し解説します。

テーブルをセットする部分

テーブルをセットする部分のコアとなるコードは下記の通りです。ちなみに手作業でテーブルを設定するにはCtrl+Tです。

ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "テーブル-" & ActiveSheet.Index

ひとつ目の引数はおまじないです。ふたつ目の引数はテーブルを設定する範囲です。みっつ目の引数は空白です。よっつ目の引数が大事で、ここをxlYesにしておくと一行目がヘッダーとしてテーブルを生成します。xlNoにすると全てをデータだとしてテーブルを生成します。BIツールに取り込むのであれば、一行目はヘッダになっているはずなのでxlYesにしています。省略するとエクセルさんが判断してくれますが、明確に決まっているのであればxlYesを指定すべきです。

テーブルを設定すると同時にテーブル名を設定する必要があるので、右辺で代入します。テーブル名がダブらないように「テーブル」という文字列ににシートのインデックス番号を結合したものをテーブル名としました。

次々にブックとシートを呼び出す部分

テーブルをセットする部分を別のプロシジャにしたことによって、それを呼び出す部分に特化したプログラムにしています。
実際、このように特定の機能を別のプロシジャにしないと一つのプロシジャが長くなりすぎますし、ループの中にループがあってさらにその中に処理があるので、可読性が落ちます。

明確に機能が分離できるときはこのように機能と処理の流れを分離するのがオススメです。

VBA フォルダの中をループで検索して、きょうの日付のファイルを開くで解説したように、Dir()関数を使ってフォルダ内検索を行っています。Dir()関数は引数を省略すると、未検索のファイル名を取得する、という挙動を利用しています。

以上、大量の作業や毎日の提携作業に便利なエクセルVBAを使って、業務の効率化を図っていきましょう。

＜関連記事＞