How to open a PDF file from multiple subfolders based on Field criteria with vba (1 Viewer)

[bholmes9]

Hi Everyone,

I am looking to create a button that opens a pdf document. I need some vba code because there is multiple subfolders that I need to look for and I cannot use Application.FollowHyperlink because the path is not the same for each file. So far I have a field in a form that has various numbers all 7 digits for example "0115553" and "0115554". I want to search for the right 6 digits in a folder that has multiple subfolders. The main folder is "Z:\2016-SHIPPING DOCUMENTS". Within this folder there are multiple sub folders such as "9-2016", "10-2016". Within the subfolders are pdf files like "115553", "115554" etc. The field on my form will usually have the format "0115553" and "0115554". I want to be able to search for just the right 6 digits in the date subfolders and open it with the click of a button. There is only one file across all subfolders (no duplicates). I am using access 2007 and I am a beginner.

 

[The_Doc_Man]

We're going to need a little bit more than that. When you open subject PDF file, what did you intend to do with it? I.e. did you want Access to manipulate the file once it is open, or did you want to open a window running some variant of Adobe and let that program work with the file?

To find a file, you will need to open a File System Object (FSO) that contains search primitives. The result of the search comes back as a collection of matching file specs (which theoretically could be empty). So two things you will need to research online would be FSO and elaborating the members of a collection. The good news is that FSO is well-behaved, so if you give it exact filename and type, your collection might contain just one member.

 

[bholmes9]

Thanks for the reply The Doc Man, and great question! I intend to open the file in Adobe Acrobat Reader DC to simply view the document. The file does not need to be altered as it is a scanned document. I will do some research into FSO and elaborating the members of a collection to isolate just one result. Sometimes finding the answer is just a matter of knowing what to search for so thanks for the tip.

 

[The_Doc_Man]

The next part of the tip is then to be sure to make a reference to your version of Adobe Acrobat Reader DC by opening a VBA code window, then clicking menu path Tools >> References. Use the "Browse" feature or just scan through the list to see if Adobe kindly installed a usable object library or DLL file for you to work with .PDF files. If so, just check the box to add it to your usable list of references.

In order to launch Acrobat, you might wish to create an Acrobat Application Object (another concept to look up) and then use the methods exposed by that object. Once it is referenced from access, you can use the Object Viewer to call up the items exposed by the reference, which should include some methods that would allow you to open a file by device/path/name.type (which you would get from FSO). Typically, you would be able to force that application to be "on top" by looking up the topic "bring window to top." If you have the app object, one of its properties is the hwnd handle (window handle) that is the argument for "bring to top."

OR you could choose to launch Acrobat in a shell knowing the location of Acrobat itself and the device/path/name.type of the file. I rarely use shells myself because I prefer the control implied via app objects, but shells ARE valid approaches to what you want to do.

 

[bholmes9]

Okay so here is where I'm at, I've done some research however I cannot get the following code attempt to work. To preface, I did discover the shell way of going about this and it would work great however the issue is the subfolder isn't always the same and I don't know how to account for that with the shell command. I also looked into adding the adobe references (and added them) however I think for my purposes if I can get the application.followhyperlink to work it will accomplish what I am trying to do. Take a look below and let me know what you think! Currently, when I click the button (command33), access goes non-responsive and I have to close it out and reopen) Any direction as to where I might be missing something would be great! Also to mention, each of the subfolders that would contain the file have over 1,200 pdf files and there are right now 10 subfolders. Not sure if that makes a difference.

Private Sub Command33_Click()

Dim fso As Object
Dim folder As Object
Dim subfolders As Object
Dim bol As String
Dim PO_TRACK As Form
Dim bolfile As Object


Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("Z:\2016-SHIPPING DOCUMENTS\")
Set subfolders = folder.subfolders
bol = Right([AlternateKey].Value, 6) & ".pdf"

For Each subfolders In subfolders

Set bolfile = subfolders.Files

For Each bolfile In bolfile
If bolfile = bol Then
Application.FollowHyperlink (subfolders.Path & "\" & bol)
End If
Next
Next

Set fso = Nothing
Set folder = Nothing
Set subfolders = Nothing

End Sub

 

[The_Doc_Man]

Can't help with FollowHyperlink; never had to use it that way. I've always used a hyperlink that was copy/pasted from some other source, never one that I synthesized.

Syntax-wise, this line can NEVER work:

For Each subfolders In subfolders

The for-each variable (which is a named variable) is being used to step through a collection (which is also a variable of the same name). Oh, semantically, this will work because you can make any non-specific object become another, different non-specific object. But this is really not going to fly. You need two separate and differently named variables to be the objects of the "For Each" portion and the "In" portion.

You would have to research or otherwise find help on Adobe's VBA offering to the Component Object Model, which I suspect might be in their "Broker" module that has the various "BrokerFS_xxxx" methods - but don't bet money on it. I know the general theory but haven't worked THAT much with Adobe.

 

[bholmes9]

Quick update: the application.followhyperlink does work. I tested the following and it works great pulling the right number from my form as well.

Private Sub Command33_Click()

Dim bol As String
bol = Right([AlternateKey].Value, 6)

Application.FollowHyperlink ("Z:\2016-SHIPPING DOCUMENTS\09-2016\" & bol & ".pdf")

End Sub

Now I just need to figure out a way to filter through the subfolders for the "bol" and direct the link with the appropriate subfolder. I'll keep trying!