Link to home
Create AccountLog in
Avatar of Scott Palmer
Scott PalmerFlag for United States of America

asked on

In Microsoft Access open up PDF files

I would like to open up all of the PDF files in a folder using VBA.


The path would be C:\AnalystFiles\2023\Jan\01202023


In the folder there are about 10 PDF files.


Is there a way to do this?


Thanks,

Scott


Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Yes.   You can use the follow hyperlink command:

https://learn.microsoft.com/en-us/office/vba/api/access.application.followhyperlink

Jim
Create a module, say, ShowDocumentFile, and paste this into it:

Option Compare Database
Option Explicit

' API declarations for OpenDocumentFile.
' Documentation:
'   https://docs.microsoft.com/en-us/windows/win32/api/shellapi/nf-shellapi-shellexecutea
'
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

Private Declare PtrSafe Function GetDesktopWindow Lib "USER32" () _
    As Long
    
' Enum for ShowWindow constants (selection).
' Documentation:
'   https://docs.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-showwindow
'
Public Enum SwShowCommand
    swShowNormal = 1
    swShowMinimized = 2
    swShowMaximized = 3
End Enum
   
' Open a document file using its default viewer application.
' Optionally, the document can be opened minimised or maximised.
'
' Returns True if success, False if not.
' Will not raise an error if the path or file is not found.
'
' 2022-03-12. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function OpenDocumentFile( _
    ByVal File As String, _
    Optional ByVal ShowCommand As SwShowCommand = swShowNormal) _
    As Boolean

    Const OperationOpen     As String = "open"
    Const MinimumSuccess    As Long = 32
    ' Shall not have a value for opening a document.
    Const Parameters        As String = ""
    
    Dim Handle      As Long
    Dim Directory   As String
    Dim Instance    As Long
    Dim Success     As Boolean
    
    Handle = GetDesktopWindow
    Directory = Environ("Temp")

    Instance = ShellExecute(Handle, OperationOpen, File, Parameters, Directory, ShowCommand)
    ' If the function succeeds, it returns a value greater than MinimumSuccess.
    Success = (Instance > MinimumSuccess)
    
    OpenDocumentFile = Success

End Function

Open in new window

Now, create a small function that will loop your folder returning and opening each PDF file:

Public Function ListPdfFiles()

    Dim Filename    As String
    
    Filename = Dir("c:\test\*.pdf")
    While Filename <> ""
        Debug.Print Filename
        OpenDocumentFile Filename
        Filename = Dir
    Wend
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Daniel Pineault
Daniel Pineault

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account