EXCEL VBA PDFファイルデータをEXCELシートに読み込む（文字列データ・表データ・自動収集・テクニック）

2021年11月13日

EXCEL VBA PDFファイルデータをEXCELシートに読み込む（文字列データ・表データ・自動収集・テクニック）

●はじめに

今回説明するプログラムは、PDFファイルの内容をEXCELシートに読み込む方法の説明を致します。通常PDFファイルを直接EXCELにデータとして読み込む事は出来ませんが（PowerOueryは可能）しかし、Word経由する事で、PDF⇒Word⇒EXCELと順番にデータを移行する事で、文字列データや表データも取り込む事が出来ます。正し、取り込むPDFファイルが元々EXCELやWordファイル等から作成されており、PDFファイルに変換したPDFファイルのみ読み込む事が可能です。それでは、順番にサンプルプログラムを交えて説明いたします。

●【EXCEL VBA からWordを操作・制御（新規作成・ファイルを開く・データ転記・一覧表の貼り付け・ファイルの保存）】については、下記を参照して下さい。

EXCEL VBA からWordを操作・制御（新規作成・ファイルを開く・データ転記・一覧表の貼り付け・ファイルの保存）

【全体的な動作説明】
●下記、サンプルプログラムの全体的な動作説明を致します。通常利用のEXCEL（エクセル）からは、直接PDFファイルを読み込む事が出来ないので、間接的に一度Wordで、PDFファイルを読み込みます。Word文書に取り込んだPDFファイルを更に、EXCELファイルへ転記する事で、PDFファイルを読み込む事ができます。この一連作業は、もちろん手動でも可能ですが、VBAを利用する事で、下図の通り自動的にPDFファイルをEXCELシートに読み込む事が出来ます。

【注意事項】VBE Word Object Library 参照設定方法

①以下のサンプルプログラムを実行するためには、以下の設定を必ず行って下さい。
※EXCEL VBAを使って他のアプリケーションを利用する場合は、下記の設定が必要になります。今回は、EXCEL VBAを使ってWordアプリケーションを操作しますので、設定する必要があります。

②　以下のプログラム実行時に、【PDFから編集なWord文書に変換します。・・・】のメッセージが表示（下記）されます。メッセージのチェックボックスに【レ点】を付ける事により、次回からは表示されなくなります。最後に「OK」ボタンをクリックします。
なお、「キャンセル」ボタンをクリックしますとプログラムが中断されます。

EXCEL VBA PDFファイルデータをEXCELシートに読み込む（PDF文字列データ）

●プログラム説明　（サンプル①）

下記のサンプルプログラムは、PDFファイル（文字列データ）をEXCELシートに読み込む方法を説明します。下図の様な、PDF文字列データをEXCELシートに読み込みます。今回のPDF⇒EXCEL転記データとしては、文字列データのみ転記対象としています。

【プログラムの流れ】
①　Wordアプリケーションを起動します。
②　Wordアプリケーションから指定したPDFファイルを開きます。
③　Wordアプリケーションで起動したPDF（文字列データ）の最終行を取得します。
④　WordアプリケーションにPDF（文字列データ）を1行目から最終行まで順番に１件づつEXCELのセルA列に転記します。
⑤　WordアプリケーションのPDF（文字列データ）の最終行まで繰り返す④へ

【プログラム実行条件】
①　ディスクトップに「サンプル１.pdf」ファイルを用意する。
②　事前にWordアプリケーションを操作するEXCEL VBEの参照設定を行う。（上記参照）
※Micosoft WordXX.0 Object Library にチェックを付ける。XXは、バージョンにより異なります。

※PDFファイル「サンプル１.pdf」

'
'
Sub PDF_WORD_EXCEL01() 'FDFデータをWordで開きEXCELへ転記します。

    Dim WordDoc
    Dim WordApp As Object
    Dim I As Long
    
    Set WordApp = CreateObject("Word.Application")
    
    WordApp.Visible = True 'Wordアプリケーションを起動します。
    
    Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル１.pdf")
    '指定したPDFファイルをWordで開きます。
    For I = 1 To WordDoc.Paragraphs.Count  'Word（PDF）の文字列の最終行まで繰り返す。
    
        With ActiveSheet  'EXCELアクティブシートにWordの文字列をA列に一行ずつ転記します。
            .Cells(I, 1).Value = WordDoc.Range.Paragraphs(I).Range.Text
        End With
    
    Next I

    WordApp.Quit 'Wordアプリケーションを閉じる
    
    
End Sub
'

Sub PDF_WORD_EXCEL01() 'FDFデータをWordで開きEXCELへ転記します。

Dim WordDoc

Dim WordApp As Object

Dim I As Long

Set WordApp = CreateObject("Word.Application")

WordApp.Visible = True 'Wordアプリケーションを起動します。

Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル１.pdf")

'指定したPDFファイルをWordで開きます。

For I = 1 To WordDoc.Paragraphs.Count 'Word（PDF）の文字列の最終行まで繰り返す。

With ActiveSheet 'EXCELアクティブシートにWordの文字列をA列に一行ずつ転記します。

.Cells(I, 1).Value = WordDoc.Range.Paragraphs(I).Range.Text

End With

Next I

WordApp.Quit 'Wordアプリケーションを閉じる

End Sub

●実行前～実行後　※プログラム実行後、EXCEL VBAからWordアプリケーションを起動させて、PDFファイルをWordで開きます。WordからEXCELへ文字列データ（PDF）を転記されました。

EXCEL VBA PDFファイルデータをEXCELシートに読み込む（PDF全でのデータ・表・文字列含む）

●プログラム説明　（サンプル②）

下記のサンプルプログラムは、PDFファイル（表データ）をEXCELシートに読み込む方法を説明します。下図の様な、PDF（表データ）をEXCELシートに読み込みます。今回のプログラムは、PDFファイルの全てが読み込む対象になりますので、表だけではなく文字列データも罫線もEXCELへ転記されます。

下記のサンプルプログラムは、Aパターン・Bパターンのプログラムを作成しております。どちらも同じ動作で実行しますが、Aパターンは、VBE Word Object Library 参照設定無しで動作する。Bパターンは、参照設定が必須の条件で作成しております。

【プログラムの流れ】
①　Wordアプリケーションを起動します。
②　Wordアプリケーションから指定したPDFファイルを開きます。
③　Wordアプリケーションで起動したPDFデータを全選択してコピーします。
④　EXCELアクティブシートのA1を元に、貼り付けます。

【プログラム実行条件】
①　ディスクトップに「サンプル２.pdf」ファイルを用意する。
②　Aパターンのプログラムは、参照設定無しで動作する事ができます。
③　Bパターンのプログラムは、参照設定が必須となりますので、必ず設定して下さい。
【参照設定につきましては、上記設定方法を参照して下さい】

※PDFファイル「サンプル２.pdf」

Aパターン【※VBE Word Object Library 参照設定無し】

'
'
Sub PDF_Word_EXCEL02() 'PDFデータをEXCELシートに貼り付ける（Word）

    
    Dim WordDoc
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
        
    
    WordApp.Visible = True   'Wordアプリケーションを起動します。
    
    Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル２.pdf")
    '指定したPDFファイルをWordで開きます。
        
    With WordApp 'PDFで開いたWordデータを全選択コピーします。
        .Selection.WholeStory
        .Selection.Copy
    End With

    With ActiveSheet  'EXCELの選択シート（アクティブ）に対して貼り付けます。
        .Range("A1").Select  '貼り付け元をセル「A1」に指定する。
        .Paste
    End With
    
    WordApp.Quit  ''Wordアプリケーションを閉じる

End Sub
'

Sub PDF_Word_EXCEL02() 'PDFデータをEXCELシートに貼り付ける（Word）

Dim WordDoc

Dim WordApp As Object

Set WordApp = CreateObject("Word.Application")

WordApp.Visible = True 'Wordアプリケーションを起動します。

Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル２.pdf")

'指定したPDFファイルをWordで開きます。

With WordApp 'PDFで開いたWordデータを全選択コピーします。

.Selection.WholeStory

.Selection.Copy

End With

With ActiveSheet 'EXCELの選択シート（アクティブ）に対して貼り付けます。

.Range("A1").Select '貼り付け元をセル「A1」に指定する。

.Paste

End With

WordApp.Quit ''Wordアプリケーションを閉じる

End Sub

Bパターン【※VBE Word Object Library 参照設定必須】

'
'
Sub PDF_Word_EXCEL03()  '参照設定必要　'PDFデータをEXCELシートに貼り付ける
    
    Dim WordDoc As Document
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
    
    WordApp.Visible = True   'Wordアプリケーションを起動します。
    
    Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル２.pdf.pdf")
    '指定したPDFファイルをWordで開きます。
        
    With WordDoc 'PDFで開いたWordデータを全選択コピーします。
        .StoryRanges(wdMainTextStory).Copy
    End With
    
    With ActiveSheet  'EXCELの選択シート（アクティブ）に対して貼り付けます。
        .Range("A1").Select  ' '貼り付け元をセル「A1」に指定する。
        .Paste
    End With
    
    WordApp.Quit  ''Wordアプリケーションを閉じる

End Sub
'

Sub PDF_Word_EXCEL03() '参照設定必要　'PDFデータをEXCELシートに貼り付ける

Dim WordDoc As Document

Dim WordApp As Object

Set WordApp = CreateObject("Word.Application")

WordApp.Visible = True 'Wordアプリケーションを起動します。

Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル２.pdf.pdf")

'指定したPDFファイルをWordで開きます。

With WordDoc 'PDFで開いたWordデータを全選択コピーします。

.StoryRanges(wdMainTextStory).Copy

End With

With ActiveSheet 'EXCELの選択シート（アクティブ）に対して貼り付けます。

.Range("A1").Select ' '貼り付け元をセル「A1」に指定する。

.Paste

End With

WordApp.Quit ''Wordアプリケーションを閉じる

End Sub

●実行前～実行後　※プログラム実行後、EXCEL VBAからWordアプリケーションを起動させて、PDFファイルをWordで開きます。Wordで起動したPDFファイルデータを全選択してコピーを行い、EXCELシートに貼り付けました。

EXCEL VBA PDFファイルデータをEXCELシートに読み込む（数字は、数値データとして取り扱う・EXCELで2次加工可能）

●プログラム説明　（サンプル③）

下記のサンプルプログラムは、PDFファイル（表データ）をEXCELシートへ読み込むサンプルプログラムですが、サンプルプログラム②で説明したPDFファイルを読み込む（全選択⇒貼り付け）方法ですと、意図せず、数値データも文字列として読み込まれEXCELでは、計算式が使えなくなってしまいます。今回のサンプルプログラムでは、テキストは文字列としてそのまま転記（Word⇒EXCEL）、数値は、数値データとして、計算式が使えるように（Word⇒EXCEL）転記させます。PDFの文字列データを一件ずつ数値か文字列か判定させてEXCELシートに転記させます。転記対象が文字列（数値を含む）のみのデータを転記させますので、罫線等はEXCEL側でデータ転記後、PDFと同じように罫線を作成します。

【プログラムの流れ】
①　Wordアプリケーションを起動します。
②　EXCELの転記先（アクティブシート）をクリアーします。
③　表の最終行を取得・表の最終列＋１を取得
④　表データの行・列を順番に取り込みます。
⑤　表のデータが数値なら数値データに変換します。
⑥　EXCELシート（アクティブシート）へ表のデータを転記します。
⑦　表の対象データを次の表に移す。
⑧　全ての表データが終わるまで繰り返します。
⑨　EXCELシート（アクティブシート）で表を作成します。
⑩　最終行＋１にて合計行を作成します。
⑪　Wordアプリケーションを閉じます。

【プログラム実行条件】
①　デスクトップに「サンプル３.pdf」ファイルを用意する。
②　このプログラムは、参照設定が必須となりますので、必ず設定します。【VBE Word Object Library 参照設定必須】

※PDFファイル「サンプル３.pdf」

※Range Paragraph メソッド (Word)　Microsoft社様　参照ページ

https://docs.microsoft.com/ja-jp/office/vba/api/word.range.insertparagraph

'
'
Sub PDF_WORD_EXCEL04() '読み込むPDFファイルをテキストデータは、文字列として、数値は、数値データとして2次利用出来るように貼り付ける。

    Dim WordDoc
    Dim WordApp As Object
    Dim I, L, T, lRow, lCol As Long
    Dim TableData As String
    
    Set WordApp = CreateObject("Word.Application")
    
    WordApp.Visible = True 'Wordアプリケーションを起動します。
    
    Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル３.pdf")
    '指定したPDFファイルをWordで開きます。
    
    T = 1　’表の初期値（1行目）
    
    ActiveSheet.Cells.Clear 'アクティブシートをクリアします。
    
    With WordDoc.tables(1)
    
    lRow = .Rows.Count  '表の最終行を代入
    lCol = .Columns.Count + 1  '表の最終列＋１を代入
 
        For L = 1 To .Rows.Count  '表の最終行まで繰り返す。
            For I = 1 To .Columns.Count + 1  '表の最終列＋１まで繰り返す。
                TableData = Replace(WordDoc.Range.Paragraphs(T).Range.Text, Chr(7), "")
                  '取り込んだテキストの末尾に「・」（改行）付いてくるのでChr(7)で削除します。
                  
                  If IsNumeric(TableData) = True Then  '数値データならば（文字列⇒数値）へ変換
                         TableData = CLng(TableData) '数値データに変換する
                  End If
                
                  ActiveSheet.Cells(L, I).Value = TableData  'EXCELシートのセルごとに表データを転記します。
                
            T = T + 1  '表の次のデータに移す（位置を加算）
    
            Next I
        Next L
    End With
    
    With ActiveSheet '表を作成します。
        .Range(Cells(1, 2), Cells(1, lCol)).Interior.ColorIndex = 34  '先頭行の見出し（背景色：水色）
        .Range(Cells(1, 2), Cells(lRow + 1, 2)).Interior.ColorIndex = 35  '列（支店）見出し（背景色：薄緑）
        .Range(Cells(1, 2), Cells(lRow + 1, lCol)).Borders.LineStyle = xlContinuous
        .Cells(lRow + 1, 2) = "合計"
    End With
    
    
    With Range("C" & lRow + 1) '合計の作成
        .Formula = "=SUM(C2:C" & lRow & ")"   'C列の最終行にSUM関数「C2～最終行」を代入
        .AutoFill Destination:=.Resize(1, lCol - 2)  'AutoFillを使い横４列分複写する
    End With
    
    WordApp.Quit 'Wordアプリケーションを閉じる
    
End Sub
'

Sub PDF_WORD_EXCEL04() '読み込むPDFファイルをテキストデータは、文字列として、数値は、数値データとして2次利用出来るように貼り付ける。

Dim WordDoc

Dim WordApp As Object

Dim I, L, T, lRow, lCol As Long

Dim TableData As String

Set WordApp = CreateObject("Word.Application")

WordApp.Visible = True 'Wordアプリケーションを起動します。

Set WordDoc = WordApp.Documents.Open("C:\Users\akira\OneDrive\デスクトップ\サンプル３.pdf")

'指定したPDFファイルをWordで開きます。

T = 1　’表の初期値（1行目）

ActiveSheet.Cells.Clear 'アクティブシートをクリアします。

With WordDoc.tables(1)

lRow = .Rows.Count '表の最終行を代入

lCol = .Columns.Count + 1 '表の最終列＋１を代入

For L = 1 To .Rows.Count '表の最終行まで繰り返す。

For I = 1 To .Columns.Count + 1 '表の最終列＋１まで繰り返す。

TableData = Replace(WordDoc.Range.Paragraphs(T).Range.Text, Chr(7), "")

'取り込んだテキストの末尾に「・」（改行）付いてくるのでChr(7)で削除します。

If IsNumeric(TableData) = True Then '数値データならば（文字列⇒数値）へ変換

TableData = CLng(TableData) '数値データに変換する

End If

ActiveSheet.Cells(L, I).Value = TableData 'EXCELシートのセルごとに表データを転記します。

T = T + 1 '表の次のデータに移す（位置を加算）

Next I

Next L

End With

With ActiveSheet '表を作成します。

.Range(Cells(1, 2), Cells(1, lCol)).Interior.ColorIndex = 34 '先頭行の見出し（背景色：水色）

.Range(Cells(1, 2), Cells(lRow + 1, 2)).Interior.ColorIndex = 35 '列（支店）見出し（背景色：薄緑）

.Range(Cells(1, 2), Cells(lRow + 1, lCol)).Borders.LineStyle = xlContinuous

.Cells(lRow + 1, 2) = "合計"

End With

With Range("C" & lRow + 1) '合計の作成

.Formula = "=SUM(C2:C" & lRow & ")" 'C列の最終行にSUM関数「C2～最終行」を代入

.AutoFill Destination:=.Resize(1, lCol - 2) 'AutoFillを使い横４列分複写する

End With

WordApp.Quit 'Wordアプリケーションを閉じる

End Sub

●実行前～実行後　※プログラム実行後、EXCEL VBAからWordアプリケーションを起動させて、PDFファイルをWordで開きます。Wordで起動したPDFファイルデータを一件ずつ、数値か文字列かを判定させてEXCELへ転記しました。PDFファイルに無かった表の最終行に、SUM関数を埋め込み数字は、数値として転記されたので、合計値が計算されました。