Excel (VBA)

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

 
(Windows 10 Pro : その他)
パワークエリで操作している全テーブル名を取得できるか?
投稿日時: 21/10/19 11:55:40
投稿者: Alice

Microsoft Excel for Microsoft 365 MSOを使用しています。
 
ツールバーにある「データ」から「データの取得」を使用してPDFの表データをExcelに落とす動作をVBAで自動化できないかと考えています。
 
下記URLにある画面(=Display Options)から”テーブル名称”を全て取得し、それを配列に入れれば可能と考えていますが、VBAのコードでできるのか?
もし、出来るのであれば、コードをどうすれば良いか?と思い投稿しました。
 
アドバイスをお持ちの方がいらっしゃいましたら、書き込んで下さると有難いです。
宜しくお願い致します。
 
https://www.google.co.jp/search?q=%E3%83%91%E3%83%AF%E3%83%BC%E3%82%AF%E3%82%A8%E3%83%AA%E3%80%80PDF&source=lnms&tbm=isch&sa=X&ved=2ahUKEwjBoYqtuNXzAhWFdd4KHcAkDQsQ_AUoAXoECAEQAw&biw=1920&bih=979&dpr=1#imgrc=ge7zT6pH6GgxjM
 
※ テーブルの番号とページ番号に規則性がない場合でも対応できるようにと思ってです。
 
-------
ちなみに、マクロの自動記録をみたところコアになっているのは下記の3つなので、可変になる箇所を変数にすれば動くはずと考えています。
 
ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= "let" & Chr(13) & "" & Chr(10) & " ソース = Pdf.Tables(File.Contents(""C:\Users\FLC004\Desktop\10月定例.pdf""), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & " Table001 = ソース{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(Table001,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Colum" & _
"n7"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
""
、、、と
 
Workbooks("PowerQuery落とし込みテスト.xlsm").Connections.Add2 "クエリ - Table001 (Page 1)" , "ブック内の 'Table001 (Page 1)' クエリへの接続です。", "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table001 (Page 1);Extended Properties=" , """Table001 (Page 1)""", 6, True, False
 
、、、と
 
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("クエリ - Table001 (Page 1)"), Destination:=Range("$A$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Table001__Page_1"
        .Refresh
    End With

回答
投稿日時: 21/10/19 14:29:58
投稿者: Suzu

Alice さんの引用:
下記URLにある画面(=Display Options)から”テーブル名称”を全て取得し、それを配列に入れれば可能と考えていますが、VBAのコードでできるのか?
もし、出来るのであれば、コードをどうすれば良いか?と思い投稿しました。

 
引用:
テーブルの番号とページ番号に規則性がない場合でも対応できるようにと思ってです。

 
引用:
ツールバーにある「データ」から「データの取得」を使用してPDFの表データをExcelに落とす動作をVBAで自動化できないかと考えています。

 
 
パワークエリ側で、テーブル一覧を取得しているのですから、
パワークエリを操作すれば良いのでは?
 
引用:
Name:="Table001 (Page 1)", Formula:= "let" & Chr(13) & "" & Chr(10) & " ソース = Pdf.Tables(File.Contents(""C:\Users\FLC004\Desktop\10月定例.pdf""), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & " Table001 = ソース{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " 変更された型 = Table.TransformColumnTypes(Table001,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Colum" & _
"n7"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 変更された型" & _
""
 
この中身を理解されていますか?
M言語にて、ソースから、データ一覧を取得し、その中から、必要テーブルを選択し データ変更しています。
『その中から』 以降 を行わなきゃ良いです。
 
Formula:= "let" & Chr(13) & "" & Chr(10) & " ソース = Pdf.Tables(File.Contents(""C:\Users\FLC004\Desktop\10月定例.pdf""), [Implementation=""1.2""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " ソース"
 
こんな感じで、データを取得し、Excel シート上に取得してしまい、そこから値を得ては?

投稿日時: 21/10/20 15:24:41
投稿者: Alice

Suzu様
 
早速のアドバイスありがとうございます。
 
Suzu様のアドバイスを元に、対象PDFのId,Kind等の情報が取れたので、下記サイトも参考に一旦作業中のExcelのシートにId等の情報を書き出して、KindからTable記載をcountifで計算することで解決できました。
 
https://www.tekizai.net/entry/powerquery_1
 
なお、下記のようにコードを作成しました。
(正直、M言語は理解できていないので、参考になりそうなwebからコピペしトライ&エラーで解決に結びつけました。)
 
※ 変数のmyPDFは、あらかじめPDFのパスを取得済
 

conf = "let" & Chr(13) & "" & Chr(10) & "    ソース = Pdf.Tables(File.Contents(""" & myPDF & """), [Implementation=""1.2""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " ソース"
    
        ActiveWorkbook.Queries.Add Name:="kakunin", Formula:=conf
        
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=kakunin;Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [kakunin]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "kakunin"
            .Refresh BackgroundQuery:=False
            
        End With
        
    num = Application.WorksheetFunction.CountIf(Cells, "Table")
        
        ActiveWorkbook.Queries(1).Delete   'テーブル数を取得するのに使用したクエリを削除
        
        ActiveSheet.Cells.Select
        Selection.Delete