Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : その他)
クエリの削除ができてもファイルサイズが大きいまま
投稿日時: 21/12/17 15:01:03
投稿者: Alice

Microsoft Excel for Microsoft 365 MSOを使用しています。
 
VBAでPDFのデータを取り込むのにパワークエリを使用しています。
データ取込後に、クエリを下記コードで削除しているのですが、クエリは削除されているもののファイルサイズが大きいままです。
下記コードになる前に一旦止めて、手動でクエリを削除するとファイルサイズが小さくなります。
下記コードのどこに、誤りがあるのか特定できず、投稿した次第です。
 
毎回、こちらのサイトを頼って恐縮ですが、アドバイスを下さる方がいらっしゃいましたら幸いです。
宜しくお願い致します。
 
※「接続」も↓この記述を入れて削除しています。
 
ActiveWorkbook.Connections("接続").Delete
 

Dim wb As Workbook

 〜〜途中割愛〜〜

  Set wb = ActiveWorkbook
                
      For Each qry In wb.Queries
                    
          qry.Delete
                    
     Next qry
                    
  Set wb = Nothing

 
ちなみに、クエリ削除方法は下記サイトを参考にしました。
https://excel-excellent-technics.com/excel-vba-breaklinks-2-11486

回答
投稿日時: 21/12/17 21:17:28
投稿者: simple

引用:
データ取込後に、クエリを下記コードで削除しているのですが、
クエリは削除されているもののファイルサイズが大きいままです。

想像で申し上げますが、クエリーそのものはさして大きなものではなく、
重要なのはExcelに展開されたPDFの内容が残っているか否か、ではないですか?
 
クエリは削除しても、PDFの内容がExcelに残っている限り、
さして小さくはならないのでは?
 
マクロによらずにクエリーを削除した場合、
どのようにされたのか具体的な記載がありませんが、
PDFの内容そのものが削除されているのではないですか?
同じ条件で比較する必要があると思います。
 
そのあたり、もう少し詳細に説明されると、回答が得られやすいと思います。

投稿日時: 21/12/20 09:17:51
投稿者: Alice

sample様
 
早速のコメントありがとうございます。
 
イメージを捉えやすいように、web上にUPされているPDFを用いてどのような操作をしたか改めて記載したいと思います。
 
【本投稿ように使用したPDFのサイト(他のサイトでも良いですが)】
三井住友銀行の「公表相場」サイトの12/17分
 
https://www.smbc.co.jp/market/backnumber/fixing/pdf_daily/now/fixing2021121720211217.pdf
 
【excelの操作手順(クエリ削除前は、手動もマクロも同様の動きをしているので以下手動手順を記載)】
@新規でExcel起動
A「データ」タブ →「データの取得」→「ファイルから」→「PDFから」→上記のPDFを選択
B「ナビゲーター」画面で「Table001」、「Table002」をチェックし、「読み込み」の下向き▼をクリック→「読み込み先」をクリック
C「データのインポート」画面で「テーブル」のラジオボタンにチェック→「OK」クリック
 ※「このデータをデータモデルに〜」はチェックを入れたままにしている
DテーブルごとにシートごとにPDFのデータが落とし込まれる
 
〜〜〜 ここで保存。ファイルサイズ⇒ 303KB 〜〜〜
 
【手動でクエリ削除】
「データ」タブ→「クエリと接続」をクリックし、画面右側に表示されるクエリ2つを右クリック→削除
 
〜〜〜 ここで保存。ファイルサイズ⇒ 20KB 〜〜〜
 
【マクロでクエリ削除】
冒頭で記載したファイルに、暫定的に「標準モジュール」を一つ挿入して先日記載したコードをコピペし実行。
マクロ実行後、処理用に挿入した「標準モジュール」削除
 
〜〜〜 ここで保存。ファイルサイズ⇒ 300KB 〜〜〜
 
実際にしようしているPDFはページ数が多いのでファイルサイズも、もっと大きいのですが現象としては同様です。
(マクロでクエリ削除すると、削除前のファイルサイズとほとんど変わらない。手動でクエリ削除するのと同様のファイルサイズが軽くしたいのです。ファイルサーバーが逼迫する為。。)
 
[/b]

回答
投稿日時: 21/12/20 11:10:42
投稿者: simple

私の環境は2019です。
PDFそのものは105KBでした。
PDF内の二つのテーブルをPQのクエリーで読み込んで
保存したExcelブックの大きさは31KBでした(クエリーは削除はしていません)。
とても303KBにはなりそうもなく、同じ前提に立てませんでした。
他の方からの回答をお待ちください。

投稿日時: 21/12/20 11:52:22
投稿者: Alice

simple様
 
試して下さり、ありがとうございます。
 
本日記載した手順Cで下記の箇所のチェックを外すと26KBになりました。
                    ↓
※「このデータをデータモデルに〜」はチェックを入れたままにしている
 
★「このデータをデータモデルに〜」にチェックを入れた方が、右側へ順次テーブルの順番通りにシート追加と書込みがされて都合が良いこともあり、チェックを入れています。
 
ここで記載したファイルを元に手動でのクエリ削除と、マクロでのクエリ削除をしましたが、やはりマクロでの処理はファイルサイズが大きいです。
(元々のファイルサイズが小さいものであれば、気にしなくて良いですが。。)
 
他の方からのコメントも参考にしようと思います。

回答
投稿日時: 21/12/20 18:25:10
投稿者: sk

引用:
VBAでPDFのデータを取り込むのにパワークエリを使用しています。
データ取込後に、クエリを下記コードで削除しているのですが、
クエリは削除されているもののファイルサイズが大きいままです。

引用:
C「データのインポート」画面で「テーブル」のラジオボタンにチェック→「OK」クリック
 ※「このデータをデータモデルに〜」はチェックを入れたままにしている

(標準モジュール)
------------------------------------------------------------------
Sub DeleteAllConnections()
     
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lst As ListObject
    Dim qtb As QueryTable
    Dim qry As WorkbookQuery
    Dim cn As WorkbookConnection
     
    Set wb = ActiveWorkbook
     
    '全てのワークシートを順次参照
    For Each ws In wb.Worksheets
        '全てのリストオブジェクト(テーブル)を順次参照
        For Each lst In ws.ListObjects
            'リンク解除
            lst.Unlink
        Next
        '全ての外部データ範囲の定義の削除
        For Each qtb In ws.QueryTables
            qtb.Delete
        Next
    Next
     
    '全てのブッククエリーの削除
    For Each qry In wb.Queries
        qry.Delete
    Next
     
    '全ての接続の削除
    For Each cn In wb.Connections
        'データモデルへの接続は直接削除できない
        If cn.Type <> xlConnectionTypeMODEL Then
            cn.Delete
        End If
    Next
     
    Set wb = Nothing
     
End Sub
------------------------------------------------------------------
 
以上のようなコードを実行なさりたい、ということでしょうか。

投稿日時: 21/12/21 09:18:58
投稿者: Alice

sk様
 
コードを書いて下さりありがとうございます。
昨日挙げた素材に対して書いて下さったコードを全てコピペして実行したところ、ファイルサイズが20KB(=手動での操作と同様サイズ)になりました‼
 
どこが大きく影響しているのか、書いて下さったコードをきざんで実行してファイルサイズを見たところ、下記箇所を実行させるとグンっとファイルサイズが小さくなりました。
 

引用:

'全ての接続の削除
For Each cn In wb.Connections
     'データモデルへの接続は直接削除できない
     If cn.Type <> xlConnectionTypeMODEL Then
         cn.Delete
     End If
Next

 
PDFのデータがExcelへ取り込まれてしまえば、接続とかは全く不要なので余計なものが残らずに解決できて大感謝です。
 
sk様が記載して下さった上記コードで接続も消せるので、自分が入れていた下記コードは消しておこうと思います。

ActiveWorkbook.Connections("接続").Delete
 
神対応、本当にありがとうございました<m(__)m>。