【超初心者向け】Excel VBAでファイルの入力処理を実装する【Queries・ListObjects】

この記事は、Excel VBAを使って業務を自動化するための、VBAプログラミングを紹介していきます。

この記事は下記のUdemy（オンライン学習サービス）の講座を参考にして、実施しました。

Excel VBAのスキルを向上させたい人は、是非参考にしてみてください。

因みに下記の内容は、私が実際にエンジニアの業務の中で作成した自動化プログラムを参考に作成してあります。その為、細かい所をぼかして書いてある部分もありますが、基本的な考え方は変えてませんので、参考にして頂ければ幸いです。

ファイル入力処理について

今回は、テキストファイルやExcelファイルをExcelのワークブックに取り込む箇所を実装します。

下記のAPIを使用して実装します。

ファイル入力に使用するAPI

今回は、下記のAPIを使用して実装しました。

Queries.Add メソッド (Excel)

新しい WorkbookQuery オブジェクトを Queries コレクションに追加 します。

構文

Querie.Add (Name, Formula, Description)

パラメーター

名前	必須 / オプション	データ型	説明
Name	必須	String	クエリの名前
Formula	必須	String	新しいクエリの Power Query M 式
Description	オプション	Variant	クエリの説明

ListObjects.Add メソッド (Excel)

新しいリスト オブジェクトを作成します。

構文

ListObjects.Add (SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName)

名前	必須 / オプション	データ型	説明
SourceType	オプション	XlListObjectSourceType	クエリで使用されるソースの種類を指定します。
Source	オプション	Variant	SourceType = xlSrcRange : データ ソースを表す Range オブジェクト
LinkSource	オプション	Boolean	外部データ ソースを ListObject オブジェクトにリンクするかどうかを指定します。 SourceType が xlSrcExternal の場合、既定値は True です。 SourceType が xlSrcRange の場合は無効で、省略しない場合はエラーを返します。
XlListObjectHasHeaders	オプション	Variant	インポートするデータに列ラベルが含まれたかどうかを示す XlYesNoGuess 定数。 Source に ヘッダー が含まれている場合、Excelヘッダーが自動的に生成されます。 既定値: xlGuess.
Destination	オプション	Variant	新 しい リスト オブジェクトの左上隅の移動先として単一セル参照を指定する Range オブジェクト。 Range オブジェクトが複数のセルを参照する場合は、エラーが生成されます。 SourceType が xlSrcExternal に設定されている場合 、Destination 引数を指定する必要があります。 SourceType が xlSrcRange に設定されている場合 、Destination 引数は無視されます。 変換先の範囲は、式で指定された ListObjects コレクションを含むワークシート上にある必要 があります。 新しい列は、新しいリストに合わせて Destination に挿入されます。 したがって、既存のデータは上書きされません。
TableStyleName	オプション	文字列型 (String)	TableStyle の名前。たとえば”TableStyleLight1″。

ファイルの入力フォームを作成する

　まず、取込処理の前提として、ファイルの入力元をプログラムに渡してあげる必要があります。

プログラムの起動トリガー

　まず、VBAプログラムを起動させるためのトリガーが必要です。今回は、分かり易くユーザーがボタンを押下して、プログラムを起動する事にします。下記のようなボタンを用意して、これを押すと処理が走るような形で実装してみます。

入力フォームについては、Excelの[開発]タブ-[挿入]から選択します。今回は、”ボタン(フォームコントロール)”を選択しました。

ファイルパスの指定

VBAプログラムに渡す、ファイルのパスを指定します。今回は、2ファイルを同時に渡すので、Excelのワークシートに直接入力します。下記のように入力フォームを指定して、セルの内容を直接VBAプログラムに渡します。

ファイル入力を実装する

今回は、テキストファイルとExcelファイルからデータを読み込む処理をVBAの関数として実装しています。処理のパラメータなどは、画面からマクロ登録し、その内容をベースにしています。

メイン関数からこれらの関数を呼び出し、それぞれのファイル内容をワークシートに登録します。

テキストファイルをワークシートにインポートする

テキストファイルから指定したワークシートにデータをインポートする関数です。

Sub InputSE_exe(strFolder As String)
' Input_SE_exe Macro

    Dim ws_se As Worksheet
    Set ws_se = Sheets("Input_SE")
    ws_se.Cells.Clear

    ws_se.Select
    ActiveWorkbook.Queries.Add Name:="1_SE_structure", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Table.FromColumns({Lines.FromBinary(File.Contents(" & strFolder & "), null, null, 932)})," & Chr(13) & "" & Chr(10) & "    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{""O """"モデル"""""", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    変更された型"

    With ws_se.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=1_SE_structure;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [1_SE_structure]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "_1_SE_structure"
        .Refresh BackgroundQuery:=False
    End With   
End Sub

Excelファイルをワークシートにインポートする

Excelファイルから指定したワークシートにデータをインポートする関数です。取込元のExcelファイルから必要なカラムを選択し、ワークシートに登録しています。

Sub InputNX_exe(strNXFile As String)
' ImportNX_exe Macro
    Dim ws_nx As Worksheet
    Set ws_nx = Sheets("Input_NX")
    ws_nx.Cells.Clear

    ws_nx.Select
    ActiveWorkbook.Queries.Add Name:="Sheet1 (3)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Excel.Workbook(File.Contents(" & strNXFile & "), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = ソース{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{""オブジェクト"", type text}, {""番号"", type text}, {""リビジョン"", Int64.Type}, {""情報""," & _
        " type text}, {""名前"", type text}, {""説明"", type text}, {""修正済み"", type text}, {""量"", Int64.Type}, {""プロジェクト"", type text}})," & Chr(13) & "" & Chr(10) & "    削除された最初の行 = Table.Skip(変更された型,1)," & Chr(13) & "" & Chr(10) & "    削除された列 = Table.RemoveColumns(削除された最初の行,{""情報"", ""名前"", ""説明"", ""修正済み"", ""量"", ""プロジェクト""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    削除された列" & _
        ""
    With ws_nx.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sheet1 (3)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1 (3)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1__3"
        .Refresh BackgroundQuery:=False
    End With
End Sub

最後に

この記事は、UdemyのVBAプログラミング学習講座で基礎的な知識を勉強した上で、作成しています。

VBAの知識を高めるために有用な講座なので、VBAに興味があるなら受けてみることをおすすめします。

こちらの記事でUdemyの紹介もしているので、プログラミングを学習している方はぜひ参考にしてみてください。