VBA/Macro for loading a PDF (1 Viewer)

robimi · Jan 23, 2015

Hello all,

I have 1900+ records, of where each record is represents a PDF Document in my folders.

Each document in my folders is named in the same format "2014-001a" for example. This is the combination of two fields in my Access Database - Document Number and Current Revision.

I am looking for some Macro/VBA where I can put the same command on every records form, that will then recognise the Document Number and Current Revision of that record, and then "On Click" able to directly load up that Pdf.

I hope someone can help,

Thank you,

Rob

Minty · Jan 23, 2015

You can open a pdf by simply using
Application.Followhyperlink "c:\Your full path here\2014-001a.pdf"

All you need to do is pull the field names into a string to pass to the path;
If the current form has two controls called DocNo and RevNo then

Code:

Dim sFilePath as String

sFilePath = "Yourpathhere" & Me.DocNo & "-" & Me.RevNo & ".pdf"

Application.Followhyperlink sFilepath

And please don't post the same question in two places, it makes life difficult for those trying to help.

robimi · Jan 23, 2015

Firstly, apologies: I will delete the other one straight away.

I've uploaded the error message I am receiving

This is the code I have entered:

..........................................................

Private Sub Command472_Click()

Dim sFilePath As String

sFilePath = "W:\\Production Documents\1970" & "Me.Document Number" & "Me.Current Revision" & ".pdf"

Application.FollowHyperlink sFilePath

..............................................................................

The Document Number and Current Revision are fields in my Database.
My document is 1970-002a in the 1970 folder.
Document Number = 1970-002
Current Revision = a

Am I missing something here?

Thank you for your help!

Minty · Jan 23, 2015

Okay a couple of points;

robimi said:
Code:

Private Sub Command472_Click() Dim sFilePath As String sFilePath = "W:\[COLOR="Red"]\[/COLOR]Production Documents\1970[COLOR="red"]\[/COLOR]" & [COLOR="Red"]"[/COLOR]Me.Document Number[COLOR="red"]"[/COLOR] & [COLOR="Red"]"[/COLOR]Me.Current Revision[COLOR="red"]" [/COLOR]& ".pdf" [COLOR="green"]Debug.Print sFilePath[/COLOR] Application.FollowHyperlink sFilePath

The Document Number and Current Revision are fields in my Database.
My document is 1970-002a in the 1970 folder.
Document Number = 1970-002
Current Revision = a

Am I missing something here?

a)You have added quotes " around your fields, as they are already strings this won't work.
b)Your path cannot be correct w:\\ is incorrect it will either be w:\ or \\ depending on its it is a mapped drive or a share
c)You are missing the final \ from the path.
d) Spaces in control and field names, you will need to add square brackets around these - don't use spaces or any other special characters - remove them to keep your life simpler in future. it will need to be Me.[Document Number] to work

I've highlighted most of the changes in green or red above.

I've also added a way for you to see the issue in the immediate window.
This is a great help when debugging problems.

robimi · Jan 26, 2015

Thanks for your help - With that debug it has cleared the issue up.

However, I am still having the same problem when I click on the button:

"The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or Active X Control"

I have no idea. Sorry?

Minty · Jan 26, 2015

Can you post up you complete code highlighting where it thinks the error is?

robimi · Jan 26, 2015

Hello,

This is the code:

Private Sub Command472_Click()

Dim sFilePath As String

sFilePath = "W:\Production Documents\1970\" & [Me.[Document Number]] & [Me.[Current Revision]] & [.pdf] \ ""

Debug.Print sFilePath

Application.FollowHyperlink sFilePath

On VBA, it says there is no problems. But when I click on Command 472 (Button on the Form) it does not work.

In this example:
Document Number is 1970-002
Current Revision is e

And it is trying to open the file: W:\Production Documents\1970\1970-002e.pdf

Minty · Jan 26, 2015

The file path doesn't include .pdf, so it doesn't know what it's trying to open.

You have W:\Production Documents\1970\1970-002e[.pdf]\

Where [.pdf] doesn't mean anything to anyone, it thinks its a field as it's in brackets.

This is where the spaces in your field names are causing you a problem seeing the issue.
It should be this;
sFilePath = "W:\Production Documents\1970\" & [Me.[Document Number]] & [Me.[Current Revision]] & ".pdf"

Which should give you;
W:\Production Documents\1970\1970-002e.pdf
As the file you are trying to open.

Imagine how much easier it would be to program this without all those brackets...
sFilePath = "W:\Production Documents\1970\" & Me.DocNumber & Me.CurrRev & ".pdf"

robimi · Jan 26, 2015

Still having a problem with that. Same error message - failing to connect with OLE or Active X.

How do I approach getting rid of all the brackets?

Minty · Jan 26, 2015

Firstly what exactly are you getting in your debug window, and again - repost your code.

Secondly to remove the spaces , ideally you need to remove them from your field names at table design stage. It will be a bit painful to start with as all your codes and anything else will need changing.
But both as a short term fix and good practice simply change the control names on your form to something like txtDocRev and txtDocNo.

Then your code will be Me.txtDocRev etc etc

hashemi · Jan 27, 2015

write Declaration of API Function in a Module as:
Public Declare Function apiShellExecute 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
use this Function to Open a file as followd:
Dim Lret As Long, LshowHow As Long
Dim stFile As String
LshowHow = 1
stFile = "C:\WO-Status Count8605.pdf"
Lret = apiShellExecute (hWndAccessApp, vbNullString, stFile,
vbNullString, vbNullString, LshowHow)

Minty · Jan 27, 2015

hashemi said:
write Declaration of API Function in a Module as:.....

This won't help if the OP can't construct the file string correctly, which is where we are at the moment.

I believe this is a straight copy of Dev Ashish's API call code from here http://access.mvps.org/access/api/api0018.htm which should retain the copyright.

VBA/Macro for loading a PDF (1 Viewer)

robimi

New member

Minty

AWF VIP

robimi

New member

Attachments

Minty

AWF VIP

robimi

New member

Minty

AWF VIP

robimi

New member

Minty

AWF VIP

robimi

New member

Minty

AWF VIP

hashemi

New member

Minty

AWF VIP

Similar threads

Users who are viewing this thread