Copying from PDF without opening them.

[Darmlucht]

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,

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

 

[Gokhan Aycan]

Not sure if this would help but, tried adding the following at the beginning and end of the macro respectively?

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]

Not sure if this would help but, tried adding the following at the beginning and end of the macro respectively?

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]

It is alright, I used to select too.

Example: Instead of

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

you can use

        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]

It is alright, I used to select too.

Example: Instead of

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

you can use

        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]

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]

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!

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:

Open pdf file from excel vba?

I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes:confused:Sub OpenPDF() 'Dim pdf As String On Error Resume Next 'pdf file to open pdf = "K:\PDF\mypdf.pdf" 'open the...

www.mrexcel.com

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!