GASで、OCR処理しても、いまいちなので、
会社で無料で使えるツールとして、パワークエリについても検討しておきたくて、試している。
PDFデータ抽出・Excel自動書き出し 完全版設定ガイド
⚠️ 重要な注意事項
このソリューションは、PDFに埋め込まれたテキストデータを読み取るものです。
- ✅ 対応: テキストデータが埋め込まれたPDFファイル（デジタル文書、Wordから変換したPDFなど）
- ❌ 非対応: スキャンした画像のみのPDFファイル（OCR処理が必要なもの）
画像ベースのPDFを処理する場合は、事前にOCRソフト（Adobe Acrobat、Google Drive、専用OCRツールなど）でテキスト化する必要があります。
概要
このガイドでは、Power Queryで抽出したPDFの全テキストを、Excelシートを終わりに保存し、処理完了後に自動でファイルを保存・クローズするまでの全手順を解説します。
このソリューションは、Power Query (M言語)による安定したデータ抽出と、VBAによる高速かつ安定したデータ展開・ファイル保存を組み合わせたものです。
前提条件
- Excelファイルと同じ階層に①データ_PDF フォルダ (PDF格納)と②データ_PDF→Excel フォルダ (出力先)が存在すること
- ファイル形式はマクロ有効ブック (.xlsm) であること
ステップ 1: Power Query (M言語)の設定
データ欠落を防ぐため、Power Queryは「全カラムをテキストとして取得し、結合する」ロジックを採用します。
A. カスタム関数 fnGetPdfData の設定
このコードをPower Queryエディタで「新しいソース」→「空のクエリ」に貼り付け、クエリ名を fnGetPdfData に変更してください。
let
// FileContent: PDFファイルのバイナリデータ (binary型)
Source = (FileContent as binary) as table =>
let
// PDFを読み込む (オプションとヘッダー昇格を排除)
PDF = Pdf.Tables(FileContent),
// 処理の安定性を向上させるため、抽出された null 値を空文字列に変換し、テキスト型に変換する
TableList = List.Transform(PDF[Data], each Table.TransformColumns(
_, List.Transform(Table.ColumnNames(_), (name) => {name, each if _ = null then "" else Text.From(_)}))),
// すべてのテーブル(ページ)を縦に結合する
Combined = if List.IsEmpty(TableList) then #table({"Column1"}, {}) else Table.Combine(TableList),
// ヘッダー昇格を削除し、安定した列名にする
PromotedHeaders = Combined
in
PromotedHeaders
in
Source
B. メインクエリ CombinedPDFData の設定
このコードをPower Queryエディタで「新しいソース」→「空のクエリ」に貼り付け、クエリ名を CombinedPDFData に変更し、パスを修正してください。
let
// --- 実行環境に依存するパスの設定(お客様指定のフルパス) ---
SourcePath = "L:\共有ドライブ\SO_GAS\GAS_注文書_Xing\①データ_PDF",
// --- ファイルの読み込みとフィルタリング ---
Source = Folder.Files(SourcePath),
FilteredPDFs = Table.SelectRows(Source, each Text.EndsWith([Name], ".pdf", Comparer.OrdinalIgnoreCase)),
// --- カスタム関数の適用 ---
InvokedFunction = Table.AddColumn(FilteredPDFs, "PdfData", each fnGetPdfData([Content])),
// --- データの整形と結合 (全カラムのテキストを結合) ---
CombinedRows = Table.AddColumn(InvokedFunction, "CombinedText", each
Table.AddColumn([PdfData], "CombinedTextRow", each
Text.Combine(
List.Transform(
Record.FieldValues(_),
(v) => if v is text then v else "" // テキスト値のみを処理
),
"#(tab)" // カラム間の区切り文字
)
)
),
// 5. 不要なPdfData列を削除し、ファイル名と結合済みテーブルのみを残す
RemovedPdfData = Table.RemoveColumns(CombinedRows,{"PdfData"}),
// 6. CombinedText列のテーブル(行のリスト)を展開
ExpandedCombined = Table.ExpandTableColumn(RemovedPdfData, "CombinedText", {"CombinedTextRow"}, {"CombinedTextRow"}),
// 7. ファイル名でグループ化し、全行のテキストを改行文字で結合 (1ファイル = 1行のデータにする)
GroupedRows = Table.Group(ExpandedCombined, {"Name"}, {
{"ExtractedText", each Text.Combine([CombinedTextRow], "#(lf)"), type text}
}),
// 8. データの重複を削除
DistinctRows = Table.Distinct(GroupedRows, {"Name", "ExtractedText"})
in
DistinctRows
C. クエリの出力設定
- CombinedPDFData を右クリックし、「読み込み先」→「テーブル」かつ「新しいワークシート」を選択します。
- これにより、CombinedPDFData という名前のシートが作成されます。
ステップ 2: VBAマクロの設定
このVBAコードが、Excelファイルを開いた瞬間にクエリを更新し、個別のExcelファイルに展開・保存します。
A. 標準モジュールへの貼り付け
VBAエディタ（Alt + F11）で「標準モジュール」を挿入し、以下のコード全体を貼り付けます。
' Excel書き出しVBAマクロ (高速化/付帯処理・保存してクローズ版)
' Power Queryの更新完了を待ち、新規件数を表示後、ファイルを保存して閉じます。
'-----------------------------------------------------------------------
Sub ExportPdfDataToJson()
' --- 設定 ---
Const TARGET_SHEET_NAME As String = "CombinedPDFData"
Const OUTPUT_FOLDER_PATH As String = "L:\共有ドライブ\SO_GAS\GAS_注文書_Xing\②データ_PDF→Excel"
Const START_ROW As Long = 2
' --- 変数宣言 ---
Dim ws As Worksheet
Dim lastRow As Long
Dim filePath As String
Dim fileName As String
Dim extractedText As String
Dim fso As Object
Dim newFileCount As Long
Dim outputFileName As String
Dim qt As Object ' クエリオブジェクト
Dim queryName As String
Dim wbNew As Workbook
Dim wsNew As Worksheet
' テキスト展開用変数
Dim rowArray As Variant
Dim colArray As Variant
Dim rowIndex As Long
Dim rowData As Variant
' 画面更新とイベントを一時停止 (高速化)
Application.ScreenUpdating = False
Application.EnableEvents = False
' エラーハンドリングの開始
On Error GoTo ErrorHandler
Set ws = ThisWorkbook.Sheets(TARGET_SHEET_NAME)
queryName = TARGET_SHEET_NAME
' 1. Power Queryの更新 (高速化と付帯処理)
On Error Resume Next
If ws.ListObjects.Count > 0 Then
Set qt = ws.ListObjects(queryName)
ElseIf ws.QueryTables.Count > 0 Then
Set qt = ws.QueryTables(queryName)
End If
On Error GoTo ErrorHandler
If Not qt Is Nothing Then
' 【付帯処理】バックグラウンドでのクエリ実行を停止し、完了するまで待機させる
If TypeName(qt) = "ListObject" Then
qt.QueryTable.BackgroundQuery = False
qt.Refresh
ElseIf TypeName(qt) = "QueryTable" Then
qt.BackgroundQuery = False
qt.Refresh
End If
Else
ThisWorkbook.RefreshAll
End If
' 2. データの読み込みと書き出し準備
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(OUTPUT_FOLDER_PATH) Then
fso.CreateFolder OUTPUT_FOLDER_PATH
End If
newFileCount = 0
' --- データ行のループ処理 ---
For i = START_ROW To lastRow
fileName = Trim(ws.Cells(i, "A").Value)
extractedText = ws.Cells(i, "B").Value
outputFileName = Replace(fileName, ".pdf", "-.xlsx")
filePath = OUTPUT_FOLDER_PATH & Application.PathSeparator & outputFileName
If extractedText = "" Then GoTo NextLoop ' 空データはスキップ
If fso.FileExists(filePath) Then GoTo NextLoop ' 既存ファイルはスキップ
' 3. 新規Excelブックの作成とデータ書き出し
Set wbNew = Application.Workbooks.Add
Set wsNew = wbNew.Sheets(1)
' ★★★ 結合されたテキストをセルに展開する処理 (Split関数改によよる安定版) ★★★
' 1. テキストを改行コード (Chr(10)) で行に分割
rowArray = Split(extractedText, Chr(10))
rowIndex = 1
' 2. 行配列をループし、タブコード (Chr(9)) で列に分割して書き込む
For Each rowData In rowArray
If Trim(CStr(rowData)) <> "" Then
colArray = Split(CStr(rowData), Chr(9)) ' タブコード (Chr(9)) で列に分割
' 行全体に配列を書き込み
wsNew.Cells(rowIndex, 1).Resize(1, UBound(colArray) - LBound(colArray) + 1).Value = colArray
rowIndex = rowIndex + 1
End If
Next rowData
' 4. ファイルを保存
wbNew.SaveAs Filename:=filePath, FileFormat:=xlOpenXMLWorkbook
wbNew.Close SaveChanges:=False
Set wsNew = Nothing
Set wbNew = Nothing
newFileCount = newFileCount + 1
NextLoop:
Next i
' 5. 処理完了メッセージを表示
MsgBox "PDFデータのExcelファイル書き出しが完了しました。" & vbCrLf & vbCrLf & _
"[新規処理件数: " & newFileCount & "件]" & vbCrLf & _
"保存先: " & OUTPUT_FOLDER_PATH, vbInformation
GoTo CleanUp
' --- エラー処理 ---
ErrorHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
If Not wbNew Is Nothing Then wbNew.Close SaveChanges:=False
MsgBox "エラーが発生しました。" & vbCrLf & _
"[エラー番号: " & Err.Number & vbCrLf & _
"説明: " & Err.Description, vbCritical
' --- クリーンアップ ---
CleanUp:
Application.ScreenUpdating = True
Application.EnableEvents = True
Set ws = Nothing
Set fso = Nothing
Set qt = Nothing
Set wsNew = Nothing
Set wbNew = Nothing
On Error GoTo 0
' 6. 処理完了後、自動でファイルを保存して閉じる
ThisWorkbook.Close SaveChanges:=True
End Sub
B. 自動実行トリガーの設定
VBAエディタで「ThisWorkbook」をダブルクリックし、以下のコードを貼り付けてください。
Private Sub Workbook_Open()
' Excelファイルを開いた瞬間にマクロを自動実行
Call ExportPdfDataToJson
End Sub
まとめ
このソリューションを使用することで、以下の処理が完全自動化されます:
- PDFデータの抽出 - Power Queryが全PDFファイルからテキストを安定的に抽出
- データの整形 - 全カラムを結合し、ファイルごとに1行のデータとして整理
- Excelファイルの自動生成 - VBAマクロが各PDFデータを個別のExcelファイルとして保存
- ファイルの自動保存とクローズ - 処理完了後、自動的にファイルを保存して閉じる
注意点
-
パスの修正が必要です。
SourcePathとOUTPUT_FOLDER_PATHを実際の環境に合わせて変更してください。 - ファイル形式は必ずマクロ有効ブック (.xlsm) で保存してください。
- 初回実行時はセキュリティ警告が表示される場合があります。「コンテンツの有効化」をクリックしてください。
トラブルシューティング
Power Queryがエラーになる場合
- PDFファイルのパスが正しいか確認してください
- PDFファイルが破損していないか確認してください
VBAマクロがエラーになる場合
- 出力フォルダのパスが正しいか確認してください
- マクロのセキュリティ設定を確認してください
- VBAの参照設定で「Microsoft Scripting Runtime」が有効になっているか確認してください
ファイルが生成されない場合
- 既に同名のファイルが存在する場合はスキップされます
- PDFデータが空の場合もスキップされます
このガイドにより、PDFからExcelへのデータ変換プロセスを完全に自動化できます。