User Select PDF import to specific Excel sheet

[cboyce44]

I have an issue. My company moved to Windows 11, and Excel changed a few things to the point where the original code no longer works. I'm wondering if someone can help me. It's getting stuck when it opens up the user selected pdf file (See bold in code below). I currently have it where after the operator selects the pdf document, the pdf document opens. Then I send the keys to PDF document to open up under Main Menu "File", then select "Export a Pdf", then select Microsoft Excel, finally select "Microsoft Excel Workbook". When this happened the newly opened Workbook used to have the extension "(userSelectedFile.pdf).xlsx" after it. Now the current way Excel opens a new Workbook using the method I have, it just has the "(userSelectedFile).xlsx". It is missing the .pdf. I don't know how to make it so when the User selects the .pdf file, the program can see the newly opened Workbook with just the Name of the file they selected and the .xlsx after it? I tried using "*.xlsx" but it errors out. I'm hoping someone can help me out.

Here is the current code I'm trying to use. If you know of a MUCH easier way to import PDF file, then please let me know. I'm a beginner at all of this, and have just self taught myself what little I know.

Private Sub CommandButton2_Click()

Dim userSelectedFile As Variant
Dim windowTitle As String
Dim fileFilter As String
Dim fileFilterIndex As Integer
Dim OpenBook As Workbook
Application.ScreenUpdating = False
windowTitle = "Choose your Gage Block Cert pdf file"

fileFilter = "PDF Files (*.pdf),*.pdf" 'Allows user to select pdf files only
'fileFilter = "PDF or Text Files (*.pdf;*.txt),*.pdf;*.txt" 'Allows user to select pdf or text files

fileFilterIndex = 1 ' For fileFilter to allow user to select pdf files only
'fileFilterIndex = 2 ' For fileFilter to allow user to select pdf or text files

userSelectedFile = Application.GetOpenFilename(fileFilter, fileFilterIndex, windowTitle)

If userSelectedFile = False Then
MsgBox "No File selected."
Exit Sub
Else
MsgBox "File selected: " & userSelectedFile
ThisWorkbook.FollowHyperlink (userSelectedFile)
End If

'Sends Commands in Adobe to create data from pdf file
Application.SendKeys "%{f}", True
Application.SendKeys "d", True
Application.SendKeys "x", True
Application.SendKeys "e", True
Application.SendKeys userSelectedFile, True
Application.SendKeys "^{ENTER}", True
Application.SendKeys "y", True
Application.Wait Now + 0.00005
Application.SendKeys "{numlock}%s", True
Application.Wait Now + 0.0001

'Opens Workbook that is created. Copies and pastes data into GageBlockData tab
Set OpenBook = Application.Workbooks.Open(userSelectedFile & ".xlsx")
OpenBook.Sheets(1).Range("A1:P100").Copy

ThisWorkbook.Worksheets("GageBlockData").Range("A1").PasteSpecial xlPasteValues


Dim wsSrc As Worksheet, wsDest As Worksheet
Dim rngSrc As Range, rngDest As Range, rCell As Range
Dim rowSrc As Long
Dim colLast As Long
Dim cntItem As Long
Dim arrDest As Variant
Dim i As Long

Set wsSrc = GageBSh ' If you know the sheet name use - Worksheets("Sheet_Name")
Set wsDest = ThisWorkbook.Worksheets("Nominal Error Calculations")
Set rngDest = wsDest.Range("A67")

' Look for Nominal Size
Set rngSrc = wsSrc.UsedRange.Find(What:="Nominal Size", LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If rngSrc Is Nothing Then Exit Sub

' Data range is a fixed size
rngDest.Offset(0).Resize(25).Value = rngSrc.Offset(0).Resize(25).Value
rngDest.Offset(0, 1).Resize(25).Value = rngSrc.Offset(0, 9).Resize(25).Value


Set pApp = CreateObject("AcroExch.App")
pApp.GetActiveDoc.Close True
Set pApp = Nothing

CutCopyMode = False

Application.CutCopyMode = False

Application.ScreenUpdating = True




End Sub

 

[cboyce44]

John. Never mind! I figured it out with the pasting. Thank you so much with all of your help! It works perfectly now.

 

[John_w]

Now it passes that, but comes up with this error now "Run-Time error '1001': NotAllowedError: Security settings prevent access to this property or method."

I click "debug", and now it's highlighting this line in the code: (I put it in bold)

pdfPDDoc.GetJSObject.SaveAs ExcelFile, "com.adobe.acrobat." & ext

See https://answers.acrobatusers.com/No...gs-prevent-access-property-method-q33221.aspx

The solution is to use an Acrobat trusted function, but I've no idea how to implement one in the context of an Acrobat object called from a VBA process, or whether it would even work.

If you can't get the Acrobat API code to work then you'll have to revert to my first idea in posts 2, 8 and 18.

If you're interested, and for the benefit of other people doing similar with the Acrobat API, I've fixed this issue by calling a trusted function instead of the standard 'SaveAs' method.

1. Create the subfolder path \Privileged\11.0\JavaScripts in the Acrobat installation folder, %appdata%\Adobe\Acrobat (for example for user name User123 the full installation folder is C:\Users\User123\AppData\Roaming\Adobe\Acrobat. The 11.0 subfolder is for Acrobat Pro XI, so this part is probably different for other Acrobat versions.

2. Using Notepad, create and save Trusted_SaveAs.js in the JavaScripts folder with the following contents:

Trusted_SaveAs = app.trustedFunction(
   function (outputFile, fileType)
   {
       app.beginPriv();
       this.saveAs(outputFile, fileType);
       app.endPriv();
   }
);

3. In the VBA code, replace:

    pdfPDDoc.GetJSObject.SaveAs ExcelFile, "com.adobe.acrobat." & ext

with:

    pdfPDDoc.GetJSObject.Trusted_SaveAs ExcelFile, "com.adobe.acrobat." & ext