【Excel】VBAで「テーブルをセル範囲に変換してエクスポート」のサンプル

Excelの機能であるテーブル機能やPower Queryはデータ集計業務を効率的にします。しかし、使い慣れていない方にとっては戸惑いを感じることが多い機能のように思います。

例えば、テーブルでは数式が構造化参照に変更になったり、Power Queryではクエリの仕組が把握できない等の理由で操作方法の説明が必要になることもあります。

PowerQueryでは、クエリの結果をシートに表示させるとテーブル形式になります。また、データソースをどこに保存するかによりますが、データソースのエラーやセキュリティ、ファイル容量の大きさが課題になることもあります。

このような理由で、テーブル機能やPowerQueryを含むファイルを他者に渡すことが妥当でないケースが時折発生します。

そこで、VBAで「テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録。

「テーブルをセル範囲に変換してエクスポート」のVBAサンプル

前提は、VBAがあるファイルが次の状態です。

「設定」という名前のシートがあり、セル$B$2に保存ファイル名をフルパスで記載している。
　例：C:\Users\●●\△△\〇〇.xlsx

「更新データ」という名前のシートに、"テーブル_更新データ"という名前のテーブルがある。

この状態のファイルで、シート「更新データ」をコピーし、新しいファイルに保存後、テーブルの書式を削除、セル範囲に変換する。また、エクスポートしたファイルにある余分な「クエリと接続」も全て削除する。

VBAコードのサンプルは次のとおり。

Sub 更新データのエクスポート()

    Dim フルパス名 As String
    フルパス名 = ThisWorkbook.Sheets("設定").Cells(2, 2).Value
    
    Sheets("更新データ").Copy
    ActiveWorkbook.SaveAs Filename:=フルパス名
    
    ActiveSheet.ListObjects("テーブル_更新データ").TableStyle = ""
    ActiveSheet.ListObjects("テーブル_更新データ").Unlist 'テーブルを範囲に変換'
    
    'クエリと接続も全て削除'
    Dim クエリと接続 As Object
    For Each クエリと接続 In ActiveWorkbook.Queries
        クエリと接続.Delete
    Next
    
    ActiveWorkbook.Close SaveChanges:=True
    ThisWorkbook.Activate
        
End Sub

以上、VBAで「テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録でした。