将数据从PDF提取到Excel

sd2nnvve  于 2022-11-18  发布在  其他
关注(0)|答案(1)|浏览(213)

我使用以下VBA将PDF数据复制到Excel工作表,并使用Word转换该数据:

Private Sub CommandButton3_Click()  '(load pdf)
    Dim o As Variant
    Set appWord = CreateObject("Word.Application")
    o = Shell("C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe C:\Users\User Profile Name\Desktop\Book1.pdf2", vbNormalFocus)
    Application.Wait (Now + TimeSerial(0, 0, 2))
    SendKeys ("^a")
    SendKeys ("^c")
    SendKeys "%{F4}"
    Application.Wait Now + TimeValue("00:00:01")
    Set appWord = CreateObject("Word.Application")
    appWord.Visible = False
    appWord.Documents.Add.Content.Paste
    With appWord
        .Selection.WholeStory
        .Selection.Copy
        .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
        .Quit
    End With

    MsgBox " pdf is loaded "
    MsgBox " Paste to EXCEL "

    Set wkSheet = ActiveSheet
    wkSheet.Range("A1").Select
    wkSheet.Paste
End Sub

如我们所见,这将从“C:\Users\User Profile Name\Desktop\Book1.pdf2”中获取PDF。
我需要帮助将其更改为打开“选择文件”框,以便我可以选择要转换的PDF?

bgibtngc

bgibtngc1#

根据我的理解,下面的代码可能对你有帮助。

'    
Private Sub CommandButton3_Click()  '(load pdf)
Dim useSel As Integer
Dim file As String
Dim o As Variant


'allow to select only one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'Remove all filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'Add a custom filter to select pdf file only
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("PDF Files Only", "*.pdf")
'make the file dialog visible to the user
useSel = Application.FileDialog(msoFileDialogOpen).Show
'user choice
If useSel <> 0 Then

file = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

'if choice is false then close sub
Else
Exit Sub
End If

'your code
Set appWord = CreateObject("Word.Application")

 o = Shell("C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe " + file, vbNormalFocus)
 Application.Wait (Now + TimeSerial(0, 0, 2))
   SendKeys ("^a")
SendKeys ("^c")
 SendKeys "%{F4}"
Application.Wait Now + TimeValue("00:00:01")
 Set appWord = CreateObject("Word.Application")
 appWord.Visible = False
 appWord.Documents.Add.Content.Paste
With appWord

       .Selection.WholeStory
       .Selection.Copy
       .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
       .Quit
End With

MsgBox " pdf is loaded "
MsgBox " Paste to EXCEL "

   Set wkSheet = ActiveSheet
    wkSheet.Range("A1").Select
    wkSheet.Paste

End Sub

相关问题