[[20200121175436]]　『マクロでcsvの取り込みからピポットテーブルの更吹x（あやとん）　ページの最後に飛ぶ

『マクロでcsvの取り込みからピポットテーブルの更新までを自動化』（あやとん）

マクロを使い以下の作業を自動化出来ないでしょうか？

1つのbookにcsvファイルを取り込む用シート1とその取り込んだデータをテーブル化し、ピポットテーブルを挿入したシート2があります。

デスクトップに落としたcsvファイルをbookの取込用シートに転記し、ピポットテーブルを更新するまでの作業をマクロで自動化できないでしょうか？

また、この作業を繰り返し行うのですが、前に取り込んだデータを削除し、新規で取り込むようにしたいです。

色々試したのですが、csvファイルを取り込むと前のデータは消され、新しいデータに変わるのですが、マクロを実行するたびにテーブルの範囲だけがどんどん広がり重くなっていきます。
テーブルの範囲もデータのある行まで自動で調整されないのでしょうか？

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

＞色々試したのですが
どのようなことを試しましたか？

＞マクロを実行するたびにテーブルの範囲だけがどんどん広がり重くなっていきます。
コードの提示がないので想像ですが、データをクリアしたあと、【挿入】してませんかね？
結果、どんどんデータ範囲が広がっているとか…

とりあえず、個人情報につながるようなデータは隠した上でコードを提示して、皆さんに添削を求めてみてはどうでしょうか？

（もこな２） 2020/01/21(火) 18:33

コードですが会社にデータがありますのでまた明日載せますね。
おっしゃる通りテーブルの2行目からクリアして新しく貼り付けるようにきています。
（あやとん） 2020/01/21(火) 19:46

＞マクロを実行するたびにテーブルの範囲だけがどんどん広がり重くなっていきます。

テキストファイルのインポートの機能を使って、
横方向に列がどんどん増えますか？
それなら、設定を変えるといいと思います。
どの設定かは忘れました＾＾；

テーブルを設定したら、テキストファイルとの接続が切れるので一工夫が必要なようです。
テーブルにする必要があるのですか？
ピボットテーブルの更新やデータのインポートの更新とは関係ない気がしますが？
（まっつわん） 2020/01/21(火) 22:13

まっつわん様

テーブル範囲の行が大量に増えてしまいます。

取り込むCSVファイルのデータの行数が毎回違うのでテーブルにすれば自動で範囲を合わせてくれると考えた次第です。他にも簡単な方法があれば知りたいです。

ちなみに作成したコードですが下記になります。
ネットに出てたのを参考に作成しました。

Sub 取り込み()

    Dim A_Sheet  
    Dim Csv_Import_File

    A_Sheet = ActiveSheet.Name

    Csv_Import_File = Application.GetOpenFilename("CSVファイル,*.csv")  
    If Csv_Import_File = "False" Then Exit Sub

    ThisWorkbook.Sheets("取り込み用").Range("A2:ZZ100000").ClearContents

    With Workbooks.Open(Csv_Import_File)
        .Sheets(1).Cells.Copy ThisWorkbook.Sheets("取り込み用").Range("A1")  
        .Close  'CSVファイルを閉じる'
    End With

    Worksheets("総勘定元帳").Activate    
    ActiveSheet.PivotTables("ピボットテーブル1").PivotCache.Refresh
End Sub

添削お願いします。

（あやとん） 2020/01/22(水) 12:24

■１

  .Sheets(1).Cells.Copy ThisWorkbook.Sheets("取り込み用").Range("A1")

なので、【挿入】ではないですね。
そうなると、私のスキルだと「テーブルの範囲だけがどんどん広がる」原因はちょっと分かりません。

■２
質問の解決とは関係ないでしょうけど「csv」とのことなので、外部データのインポート「QueryTables.Add」で取り込んだ方が良くないですか？

■３
問題があるわけではありませんが、提示されたコードですと「A_Sheet」「Csv_Import_File」いずれも型の指定がないのでValiant型になってます。
さらに「A_Sheet」は使っていません。

■４
上記を踏まえて

 (1) 取り込むcsvファイルをユーザーに指定してもらう
 (2) 「取り込み用」シートのタイトル行以外をクリアする
 (3)　　　 〃      のA2セルを起点としたセル範囲に、指定したCSVファイルの２行目から取り込む
 (4)　　　 〃      のA1が含まれる表のセル範囲を取得する
 (5) 「総勘定元帳」シートの「ピボットテーブル1」のソースを(4)の範囲に置き換える

と考えてみると、

    Sub さんぷる()
        Dim 取込ファイルパス As String
        Dim MyRNG As Range

        取込ファイルパス = Application.GetOpenFilename("CSVファイル,*.csv")
        If 取込ファイルパス = "False" Then Exit Sub

        Stop ' ←ブレークポイントの代わり

        With ThisWorkbook.Worksheets("取り込み用")
            '▼既存データのクリア
            .UsedRange.Offset(1).Clear

            '▼csvファイルを取り込む
            With .QueryTables.Add(Connection:="TEXT;" & 取込ファイルパス, Destination:=.Range("A2"))
                .TextFileCommaDelimiter = True      ' カンマ区切り
                .RefreshStyle = xlOverwriteCells    ' セルに上書き
                .TextFileStartRow = 2               'データの読込開始行をセット
                .Refresh                            ' データを表示
                .Delete                             ' CSV との接続を解除
            End With

            Set MyRNG = .Range("A1").CurrentRegion
        End With

        ThisWorkbook.Worksheets("総勘定元帳").PivotTables("ピボットテーブル1").ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="取り込み用!" & MyRNG.Address)

    End Sub

のような感じで行けそうに思います。
実データが手元にありませんので本当に成功するかは不明ですが、とりあえず↑をステップ実行して動きを確認しつつ「テーブルの範囲だけがどんどん広がる」現象が再現されるか確認してもらえませんか？

（もこな２） 2020/01/22(水) 16:56

もこな2様
ありがとうございます。
ご提示いただきだいたコードでうまく行きました！
助かりました！
（あやとん） 2020/01/23(木) 21:38

＞うまく行きました！
う～ん、そうなるとなんで、テーブルの範囲が広がっちゃってたんですかね・・・・
怪しいのは「PivotCache.Refresh」ですけど、普段ピボットテーブルをマクロで操作しないので、私にはよく分かりません。

また、提示したコードを見直したらコメント付け忘れていた部分があったのとRange型変数を使ってて分かりづらいかなと思う部分があったので↓に訂正します。

    Sub さんぷる_改()
        Dim 取込ファイルパス As String
        Dim データ範囲 As String

        取込ファイルパス = Application.GetOpenFilename("CSVファイル,*.csv")
        If 取込ファイルパス = "False" Then Exit Sub

        With ThisWorkbook.Worksheets("取り込み用")

            '▼既存データのクリア
            .UsedRange.Offset(1).Clear

            '▼csvファイルを取り込む（外部データのインポート）
            With .QueryTables.Add(Connection:="TEXT;" & 取込ファイルパス, Destination:=.Range("A2"))
                .TextFileCommaDelimiter = True      ' カンマ区切り
                .RefreshStyle = xlOverwriteCells    ' セルに上書き
                .TextFileStartRow = 2               'データの読込開始行をセット
                .Refresh                            ' データを表示
                .Delete                             ' CSV との接続を解除
            End With

            '▼データ範囲をシート名も含めて文字列で取得する
            データ範囲 = Split(.Range("A1").CurrentRegion.Address(External:=True), "]")(1)
        End With

        '▼ピボットテーブルのデータソースを変更
        ThisWorkbook.Worksheets("総勘定元帳").PivotTables("ピボットテーブル1").ChangePivotCache _
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=データ範囲)

    End Sub

（もこな２） 2020/01/24(金) 12:20

コメント返信：

[ 一覧(最新更新順) ]

[[20200121175436]] 『マクロでcsvの取り込みからピポットテーブルの更吹x（あやとん） ページの最後に飛ぶ

[[20200121175436]]　『マクロでcsvの取り込みからピポットテーブルの更吹x（あやとん）　ページの最後に飛ぶ