Copying from PDF without opening them.

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
19
Hello dear fellow VBA WIzards!

I am currently working on a project that has a lot of PDF-files that need to be read and some data copied.
I don't have access to the full Adobe suite, so I can't use the reference-relying codes I've found here and on the www.
I currently have a working program, but there is one awfully inconvenient thing that is making me a bit mad.

The code goes through a selected folder, finds all the PDF-files in it, copies the content to an Excel sheet, then finds the values I am looking for and copies them
to another Excel sheet.
So far so good. Only problem, before opening the PDF's, I get a warning about opening hyperlinks...That means I still need to sit here and click every time.
I tried sending SendKeys, but to no avail.

Is there a way to copy data from PDF without opening it? Or a way to bypass this warning message(without making changes to the register/keys)?
With maybe a sendkey-like command that is accepted by this warning-message?
I will add my code. Don't get mad about my coding skills, I am still 'new' and don't know much about coding ethics.. I just figure things out by trial and error, and copying found code..

I am open to any suggestion, about my problem or my code in general.

Thank you in advance!
And if this question has been posted somewhere else, please let me know! I tried tolook for a similar question, but didn't find anything really fitting..

Kind regards,

VBA Code:
Sub OpenPDFInFolder()
   
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    Dim ws As Worksheet
    Dim PDF_path, PDF_P As String
    Dim Kolom As String
    Dim inputFileDialog As FileDialog
    Dim folderChosenPath As Variant
    Dim Mol As Range
    Dim cell As Range
    Dim Mw As String
    Dim strAddress As String
    Dim Mols As String
    Dim Eind As Range
    Dim Laatst As Variant
    Dim Rij As String
    Dim i As Integer
    Dim Molss As Range
    Dim Final As String
   
    Set inputFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

    With inputFileDialog
        .Title = "Select Folder to Start with"
        .AllowMultiSelect = True
        If .Show = False Then Exit Sub
        folderChosenPath = .SelectedItems(1)
    End With
Rij = 5
PDF_path = folderChosenPath
   
    'Check for back slash
    If Right(PDF_path, 1) <> "\" Then
        PDF_path = PDF_path & "\"
    End If
      
    sFileName = Dir(PDF_path & "*.pdf")
   
    Do While Len(sFileName) > 0
       
        'Display file name in immediate window
        'MsgBox (sFileName)
        Final = PDF_path & sFileName
        Debug.Print sFileName
        Debug.Print PDF_path
        Debug.Print Final
   
            Kolom = "A"
                'open the pdf file
            ActiveWorkbook.FollowHyperlink Final
            SendKeys "y", True
       
            SendKeys "^a^c", True
            'SendKeys "^a", True
            'SendKeys "^c", True
            SendKeys "{NUMLOCK}", True
           
 
             Call Shell("TaskKill /F /IM AcroRd32.exe", vbHide)
            Application.ScreenUpdating = True

            Set ws = ThisWorkbook.Sheets("Sheet1")
            ws.Activate
            ws.Range("A:A").ClearContents
            ws.Range(Kolom & 1).ClearContents
            ws.Range(Kolom & 1).Select
            ws.Paste
            'SendKeys "^v", True
            'ws.Activate
            'ws.Range("A1").ClearContents
            'ws.Range("A1").Select
            'ws.Paste
            'SendKeys "^v", True
                Application.ScreenUpdating = True
   
            Set cell = Range("A:A").Find("> 4297317")
            If cell Is Nothing Then
        MsgBox ("Not found")
        Else
        MsgBox (cell.Address)
    End If
            Mx = cell.Address
           
           
            Mw = Right(Mx, 2)
            Ms = Mw + 2
            Mols = Range(Kolom & Ms)
            MsgBox (Mols)
            Range("C5:C5").Activate
            Cells(1, 6) = Mols
   
        Set Eind = Range("A:A").End(xlDown)
        ' MsgBox (Eind)
       
        Laatst = Split(Eind)
        Set wss = ThisWorkbook.Sheets("MW average and fractions")
        'MsgBox (Laatst(1))
        wss.Activate
        wss.Range("C" & Rij).Select
        Range("C" & Rij) = Mols / 1000
  
        wss.Range("D" & Rij).Select
        Range("D" & Rij) = Laatst(1)
        wss.Range("E" & Rij).Select
        Range("E" & Rij) = Laatst(2)
        wss.Range("F" & Rij).Select
        Range("F" & Rij) = Laatst(3)
        wss.Range("G" & Rij).Select
        Range("G" & Rij) = Laatst(4)
        wss.Range("H" & Rij).Select
        Range("H" & Rij) = Laatst(5)
       
       
       
        'Set the fileName to the next available file
    sFileName = Dir
    Rij = Rij + 1
   
Loop

End Sub
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Not sure if this would help but, tried adding the following at the beginning and end of the macro respectively?

VBA Code:
Sub OpenPDFInFolder()

    Application.DisplayAlerts = False
    ' code here
    Application.DisplayAlerts =True

End Sub

For better coding, try to avoid selecting cells whenever you can. Most things don't require you to activate sheets, selecting cells, so experiment. If you don't actually need to turn ScreenUpdating, EnableEvents and the sort while running the code, use them outside loops. Those things do waste time while toggling as well.

Also, you can just paste an array to a range and copy the range to an array directly. Just make sure range is same size as the array.
 

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
19
Not sure if this would help but, tried adding the following at the beginning and end of the macro respectively?

VBA Code:
Sub OpenPDFInFolder()

    Application.DisplayAlerts = False
    ' code here
    Application.DisplayAlerts =True

End Sub

For better coding, try to avoid selecting cells whenever you can. Most things don't require you to activate sheets, selecting cells, so experiment. If you don't actually need to turn ScreenUpdating, EnableEvents and the sort while running the code, use them outside loops. Those things do waste time while toggling as well.

Also, you can just paste an array to a range and copy the range to an array directly. Just make sure range is same size as the array.
Thanks for the reply!
I hadn't tried this approach, but it doesn't solve the popping up of the warning.
About your tips for better coding, the screenupdating and alike, I mostly do because of other people's code that I am using.
I am not really familiar with what these commands do, but sometimes if I remove them, it stops working ,so usually I just leave it there.

The thing with selecting cells, is that I don't really know another way to get the value from a certain cell.
I tried some different things/approaches, but this one works 'the best' for now.

I don't know why, but sometimes when I run this, I get an error, and sometimes it works like a charm..
So probably there is room for improvement.
If I make significant changes to it, I'll post it!

Thanks again for replying!

Kind regards,
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
It is alright, I used to select too.

Example: Instead of

VBA Code:
        wss.Range("C" & Rij).Select
        Range("C" & Rij) = Mols / 1000

you can use

VBA Code:
        wss.Range("C" & Rij).Value = Mols / 1000

Here, .Value is redundant as it is the default property for a range, I have habit of adding them regardless.

On usage of SendKeys, they are not really reliable. Another app might have focus at that moment for whatever reason.

Edit: This looks like the same issue from StackOverflow. Might be too much for you though.
 

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
19

ADVERTISEMENT

It is alright, I used to select too.

Example: Instead of

VBA Code:
        wss.Range("C" & Rij).Select
        Range("C" & Rij) = Mols / 1000

you can use

VBA Code:
        wss.Range("C" & Rij).Value = Mols / 1000

Here, .Value is redundant as it is the default property for a range, I have habit of adding them regardless.

On usage of SendKeys, they are not really reliable. Another app might have focus at that moment for whatever reason.

Edit: This looks like the same issue from StackOverflow. Might be too much for you though.
Aha, I see what you mean! Indeed your code is nicer to look at and probably faster to run!
I will adapt my code to this, thank you!

The Stackoverflow link I already came across, but that makes use of editing the register/registery keys.
I tried multiple times but I can't add or change registry entries on my work 'pc'. It's a small client pc running on a network..
If I can make a change, it reverts back to previous after reboot/restart.

Again, thank you for your input, much appreciated!

Kind regards,
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
There is a link in the comments section of the SO which talks about an API solution. This one.
Much better perhaps but as I said might be a little too much.
 

Darmlucht

New Member
Joined
Nov 23, 2017
Messages
19
Update!

I've found a piece of code that lets me open PDF-files without having to click Yes on a warning!

These 2 pieces of code I added to my existing code, and, with some changes it worked perfectly!


VBA Code:
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub EVN()
Filename = "C:\Users\Yourfile.pdf"
ShellExecute 0, "Open", Filename, "", "", vbNormalNoFocus
End Sub

It has been taking from this topic:

and the answer is by Tarkan Vural.

So for now, my problems are solved!
Thanks to anyone who read and thought about this!

Kind regards, and until next time!
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top