open a with vba
Post on 26-Oct-2015
73 Views
Preview:
DESCRIPTION
TRANSCRIPT
Written by Christos Samaras on Tuesday, 30 April 2013 at 01:11
Judging from the blog stats, the subject of opening PDF files using VBA code is really popular. Having a discussion with a
blog reader (Jean-Sébastien) in the comments of my previous post VBA Macro To Open A PDF File, I thought that I should
try to write a more generic code that will work with both Adobe Reader and Professional.
In comparison with my previous attempt the VBA code below doesn’t use the Adobe object system or the sendkeys
method. Instead, a combination of various API functions is used in order to find and manipulate the page number and
page zoom textboxes. The general idea behind this code can be divided in four steps:
Check the file path and if is valid, use the FollowHyperlink method in order to open the PDF file.1.
With the FindWindow API function find the window of Adobe Reader or Adobe Professional that contains the opened
PDF file and bring it to foreground using the SetForegroundWindow API function.
2.
Find the subsequent child windows with the FindWindowEx API function.3.
Use the SendMessage and PostMessage API functions in order to send the desired page number and window zoom
value to the corresponding textboxes.
4.
Like 112 people like this. Be the first of yourfriends.
We're on
Follow @MyEnginWorld 19 followers
Your e-mail address... SubmitSubmit
with Google Friend Connect
Members (16)
Already a member? Sign in
Search this blog...
Excel VBA – Read And Write Text Files
VBA Macro To Open A PDF File
VBA Macro To Convert PDF Files Into Different Format
Draw A Polyline In AutoCAD Using Excel VBA
Open A Google Earth File (kmz) With Google Maps
Fatigue Analysis In Turbomachinery
Απάτες Με Θεωρητικό Περιτύλιγµα
Solutions Of Colebrook & White Equation
AutoCAD – Calculate The Total Length Of Lines
Open Channel Flow - Trapezoidal Cross Section
Tweet
1
2
1
7
Like
Home Blog Contents About ERTAT Add-In Work With Me Advertise Here Suggested Books Disclaimer
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
1 of 6 10/9/2013 4:53 PM
Similarly to my previous post, the Spy++ software was used in order to specify the windows hierarchy in Adobe
Reader/Professional. The picture above shows the window tree of a sample PDF document.
The VBA code in this post can be used with almost all the office programs. I have tested it with Access, Excel, Word and
Power Point (both 2010 and 2003 versions) and works like charm. Since it doesn’t require any reference to Adobe library
and no sendkeys are used is probably much easier to use and more reliable than my previous codes.
VBA code
Option Explicit
'Retrieves a handle to the top-level window whose class name and window name match the specified strings.
'This function does not search child windows. This function does not perform a case-sensitive search.
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'Retrieves a handle to a window whose class name and window name match the specified strings.
'The function searches child windows, beginning with the one following the specified child window.
'This function does not perform a case-sensitive search.
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
'Brings the thread that created the specified window into the foreground and activates the window.
'Keyboard input is directed to the window, and various visual cues are changed for the user.
'The system assigns a slightly higher priority to the thread that created the foreground
'window than it does to other threads.
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
'for the specified window and does not lParenturn until the window procedure has processed the message.
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
'Places (posts) a message in the message queue associated with the thread that created the specified
'window and lParenturns without waiting for the thread to process the message.
Public Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
'Constants used in API functions.
Public Const WM_SETTEXT = &HC
Public Const VK_RETURN = &HD
Public Const WM_KEYDOWN = &H100
Private Sub OpenPDF(strPDFPath As String, strPageNumber As String, strZoomValue As String)
'Opens a PDF file to a specific page and with a specific zoom
'using Adobe Reader Or Adobe Professional.
'API functions are used to specify the necessary windows
'and send the page and zoom info to the Adobe window.
1
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
2 of 6 10/9/2013 4:53 PM
'By Christos Samaras
'http://www.myengineeringworld.net
Dim strPDFName As String
Dim lParent As Long
Dim lFirstChildWindow As Long
Dim lSecondChildFirstWindow As Long
Dim lSecondChildSecondWindow As Long
Dim dtStartTime As Date
'Check if the PDF path is correct.
If FileExists(strPDFPath) = False Then
MsgBox "The PDF path is incorect!", vbCritical, "Wrong path"
Exit Sub
End If
'Get the PDF file name from the full path.
On Error Resume Next
strPDFName = Mid(strPDFPath, InStrRev(strPDFPath, "\") + 1, Len(strPDFPath))
On Error GoTo 0
'The following line depends on the apllication you are using.
'For Word:
'ThisDocument.FollowHyperlink strPDFPath, NewWindow:=True
'For Power Point:
'ActivePresentation.FollowHyperlink strPDFPath, NewWindow:=True
'Note that both Word & Power Point pop up a security window asking
'for access to the specified PDf file.
'For Access:
'Application.FollowHyperlink strPDFPath, NewWindow:=True
'For Excel:
ThisWorkbook.FollowHyperlink strPDFPath, NewWindow:=True
'Find the handle of the main/parent window.
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lParent = 0
DoEvents
'For Adobe Reader.
'lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Reader")
'For Adobe Professional.
lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Acrobat Pro")
If lParent <> 0 Then Exit Do
Loop
If lParent <> 0 Then
'Bring parent window to the foreground (above other windows).
SetForegroundWindow (lParent)
'Find the handle of the first child window.
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lFirstChildWindow = 0
DoEvents
lFirstChildWindow = FindWindowEx(lParent, ByVal 0&, vbNullString, "AVUICommandWidget")
If lFirstChildWindow <> 0 Then Exit Do
Loop
'Find the handles of the two subsequent windows.
If lFirstChildWindow <> 0 Then
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lSecondChildFirstWindow = 0
DoEvents
lSecondChildFirstWindow = FindWindowEx(lFirstChildWindow, ByVal 0&, "Edit", vbNullString)
If lSecondChildFirstWindow <> 0 Then Exit Do
Loop
If lSecondChildFirstWindow <> 0 Then
'Send the zoom value to the corresponding window.
SendMessage lSecondChildFirstWindow, WM_SETTEXT, 0&, ByVal strZoomValue
PostMessage lSecondChildFirstWindow, WM_KEYDOWN, VK_RETURN, 0
1
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
3 of 6 10/9/2013 4:53 PM
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lSecondChildSecondWindow = 0
DoEvents
'Notice the difference in syntax between lSecondChildSecondWindow and lSecondChildFir
'lSecondChildSecondWindow is the handle of the next child window after lSecondChildFi
'while both windows have as parent window the lFirstChildWindow.
lSecondChildSecondWindow = FindWindowEx(lFirstChildWindow, lSecondChildFirstWindow, "
If lSecondChildSecondWindow <> 0 Then Exit Do
Loop
If lSecondChildSecondWindow <> 0 Then
'Send the page number to the corresponding window.
SendMessage lSecondChildSecondWindow, WM_SETTEXT, 0&, ByVal strPageNumber
PostMessage lSecondChildSecondWindow, WM_KEYDOWN, VK_RETURN, 0
End If
End If
End If
End If
End Sub
Function FileExists(strFilePath As String) As Boolean
'Checks if a file exists.
'By Christos Samaras
'http://www.myengineeringworld.net
On Error Resume Next
If Not Dir(strFilePath, vbDirectory) = vbNullString Then FileExists = True
On Error GoTo 0
End Function
Sub TestPDF()
OpenPDF ThisWorkbook.Path & "\" & "Sample File.pdf", 6, 143
End Sub
Code results
The short video below demonstrates how the above VBA code can be used with Access, Word, Power Point and Excel
2010.
Sample files
1
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
4 of 6 10/9/2013 4:53 PM
1
Newer Post Older Post
The rar file contains the following files:
A folder with two VBA modules with the above code for Adobe Reader and Adobe Professional. You can import them
to any office application you want.
1.
A folder which contains an Access database, a Word document, a Power Point presentation and an Excel workbook,
as well as a PDF sample that are used to demonstrate the usage of the same VBA code in different applications (see
the video above).
2.
Similar to 2, but the files are for office 2003.3.
Download it from here
These files can be opened with Office 2007 - 2010, as well as with Office 2003. Please, remember to enable macros
before using them.
Read also
VBA Macro To Open A PDF File
Export Excel Charts As TIFF images Using Adobe Professional
VBA Macro To Convert PDF Files Into Different Format
Did you enjoy this post? If yes, then like and share it!
Like 7 Tweet 12
StumbleUpon
Categories: Office Tips
Christos Samaras
Mechanical Engineer, M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece
Communication: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Lnkedin.
Home
1
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
5 of 6 10/9/2013 4:53 PM
2 comments
Register & Unregister A DLL FileThrough VBA
•
Avatar — Hi Jetul, I just sawyour email. I will answer you soon. At themoment I am quite busy with some …
Retrieve Time From An Internet Server(VBA Function)
•
Avatar — Hi Vikram, Thankyou for your kind comment.I hope thatyour problem will be eventually solved. …
Search Your VBA Code (Excel Add-In)•
Avatar — Which software youuse? With Adobe Professional you cansimply convert the excel file into pdf …
Add Description To A Custom VBAFunction
•
Avatar — Hi Carlos, You havean interesting page with many usefulExcel files, which I have to look in …
Newest Community Share
Brian Dailey •
Thanks for the code, it was a great help to me. However, I ran into a minor bug. Thescript never ends due to this line:
Do Until dtCurrentTime > dtCurrentTime + TimeValue("00:00:05")
dtCurrentTime never changes so this loop just runs until you escape. I added adtStartTime and update dtCurrentTime = Now() inside the loop then changed the lineto:Do Until dtCurrentTime > dtStartTime + TimeValue("00:00:05")
1
Christos Samaras • Admin
Brian, thank you very much for your correction. Sometimes loops can be littletricky. I have updated the code above and I performed a quick test: if the"AVUICommandWidget" changes to "AVUICommandWidgets") the sub afterthe 5 seconds exits the loop. So, the bug was corrected.
Thank you again for your help!
0
Avatar
Avatar
Copyright 2013 Copyright 2013 Christos SamarasChristos Samaras Follow us on Follow us on Facebook Facebook , , Google+ Google+ & & TwitterTwitter
1
Open A PDF File With VBA ~ My Engineering World http://www.myengineeringworld.net/2013/04/open-pdf-file-with-vba.h...
6 of 6 10/9/2013 4:53 PM
top related