How to Open PDF Files Via Visual Basic Macro Code ?

[bxc2739]

How can I code a Macro in Excel to search in a preset directory and pull out, aka open/run
a certain Adobe Acrobat .pdf file automatically?

Is this even possible to code in Excel Macro or is it out of its scope of what Excel VB code can do?


Right now I have a very repetitive task where depending on the value of a certain field in Excel I have to search for the right/corresponding .pdf file and open it up do work in the .pdf as well as the Excel worksheet... I have to do like hundreds of this....

So is there a quick way to code in Excel that a certain value will open a certain file named .pdf acrobat file? Can other programs/files be called upon and ran/started/opened from within Excel Macro?

Thanks

[RobDog888]

Thread Moved

[RobDog888]

You can use the ShellExecute API to run any file in its associated program. So execute the pdf file and it opens in Acrobat Reader if you have AR installed.

'In a Module...
Option Explicit

Private Declare 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 Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Private Sub RunMe()
    ShellExecute Me.hwnd, "open", "C:\MyFile.pdf", vbNullString, "C:\", SW_SHOWNORMAL
End Sub

Then to get the files in some directory you will want to use the FindFirstFile and FinNextFile APIs (search the Forums fo r code examples).

[bxc2739]

You can use the ShellExecute API to run any file in its associated program. So execute the pdf file and it opens in Acrobat Reader if you have AR installed.

'In a Module...
Option Explicit

Private Declare 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 Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Private Sub RunMe()
    ShellExecute Me.hwnd, "open", "C:\MyFile.pdf", vbNullString, "C:\", SW_SHOWNORMAL
End Sub

Then to get the files in some directory you will want to use the FindFirstFile and FinNextFile APIs (search the Forums fo r code examples).

Thanks for the code. When I try to compine this it say's invalid use of the Me. word. - > "Me.hwnd" What doesn't Me stand for? Is it spelled right?


Thanks

[RobDog888]

Sorry, change that to "ThisWorkbook" (no quotes). Me is the VB 6 object that represents the current form or class that you are in with the code. In Excel the equilivalent is ThisWorkbook.

[bxc2739]

Sorry, change that to "ThisWorkbook" (no quotes). Me is the VB 6 object that represents the current form or class that you are in with the code. In Excel the equilivalent is ThisWorkbook.

Hello,

When I compile it it gives "Compile Error: Method or Data member not found"
and highlights ThisWorkbook.hwnd

When I compile it as just "ThisWorkbook" I get this error msg:

Runtime error 438
Object doesn't support this property or method



Do you know what I am doing wrong here?

Thanks

[RobDog888]

sorry, yet again, I was in too much of a hurry. Change ThisWorkbook.hwnd to Application.Hwnd

[veeeresh]

Many thanks for this code, which is really helpful.....!

Can you please let me know how to open multiple pdf files in the same folder

[RobDog888]

you would enumerate the files in the desired folder using FindFirstFile and FindNextFile API calls. Then with each result you would call the RunMe function but first make an edit to it to take an argument of the filepath with file name.

[JJJCR_FOX]

with the code of robdog... add this macro

Sub openfile()
Dim strpath As String
Dim openfile As Variant

ChDrive "C:\"
ChDir "C:\testing\"   'change to your own folder name
openfile = Application.GetOpenFilename _
(Title:="File to open", _
FileFilter:="PDF Files *.pdf (*.pdf),")

If openfile = False Then
MsgBox "No file specified.", vbExclamation, "Bamm!"
Exit Sub
Else
End If

    ShellExecute Application.hwnd, "open", openfile, vbNullString, "C:\", SW_SHOWNORMAL
End Sub

[RobDog888]

But that will make it manual as the user will have to select each file individually.

I read the post as opening all pdf files in a particular folder, not opening one by one with manual selection but we need to OP to clear it up

[JJJCR_FOX]

hmm..yup rob dog the user will do it manually, he need to loop on the folder if he wants to open multiple files .. but don't know why he need to open multiple files.... ???

[RobDog888]

Only veeeresh knows why and how he needs this to work. Still awaiting his reply.

[mikymouse]

sorry, yet again, I was in too much of a hurry. Change ThisWorkbook.hwnd to Application.Hwnd

the code works absolutely fine..!!!!!!!!!!!!!!!!!!!

just need to know... how to read data from that pdf ..or to export data from that pdf to an excel sheet? please .................it will be of great help!!!!!

thanks in advance.

[RobDog888]

One way would be to read the pdf file specification documents. Then open the pdf file in binary mode and look for the specification tags that identify what you want to read.

[mikymouse]

One way would be to read the pdf file specification documents. Then open the pdf file in binary mode and look for the specification tags that identify what you want to read.

please ..help with code..!!!!!!!