Solved

Possible to open all .pdf files through access vba that match specified criteria?

Posted on 2012-03-16
21
522 Views
Last Modified: 2012-03-26
I am creating a form where after a box is updated it takes the [jaDrawNum]  and another field [jaRevisionNum] and concatenates them with '*' and ".pdf" to select and open all files with the matching info from jaDrawNum and jaRevisionNum within a folder and it's subfolders.

I'm open to other suggestions as to how to do this as well.

So the filename string would look like 2S56634-5454*(A00A)*.pdf and would open all the pdf files in the folders that have 2S56634-5454 and (A00A) appearing in the file name. (Both criteria would have to meet per file name, not one or the other).

I was building off of another code where I opened up a specific file.. Here is what I just through together as an example of what I'm trying to accomplish;


Private Sub txtAssemblySeq_AfterUpdate()
Dim strLink As String

Me.Filter = "[jaJobNum] = " & "'" & Me.txtJobNum & "'" & " and " & "[jaAssemblySeq] = " & Me.txtAssemblySeq
Me.FilterOn = True

If IsNull([jaDrawNum]) Then

MsgBox "No Drawing was attached to this record."

Else
strLink = "C:\Drawings\*\" & [jaDrawNum] & "*" & [jaRevisionNum] & "*" & ".pdf"

FollowHyperlink strLink

End If

End Sub



Thanks in advance for the help
0
Comment
Question by:SeyerIT
  • 11
  • 10
21 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37731058
So what is your direct question?

How to open the multiple files?,  or how to set up a system to find the multiple files?, or both?, ...or neither?
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37737252
Hey boag,

Both. I'm trying to open up all .pdf files in a folder and it's sub folders that meet the criteria of 2S56634-5454*(A00A)*.pdf So open up any files that have 2S56634-5454 and (A00A) in the file name.

The system would find multiple files and then open them.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37738780
As always, anything is possible.

This task is a bit much for me to take on here as a volunteer.

Do a Google search of:
 "VBA Loop Files in a folder"
...and go form there...

Perhaps another Expert is willing to take this on.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37738867
I understand Boag.. Thanks for the assistance.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37738992
For ex:

Adapt the code found here:
http://www.zerrtech.com/content/excel-vba-open-all-files-a-directory, to something like this, ...perhaps...?:

Sub fileloop()
02          Dim MyDir As String
03          Dim strPath As String
04          Dim vaFileName As Variant
05          Dim i As Integer
06           
07          MyDir = ActiveWorkbook.Path ' current path
08          strPath = MyDir & "\files" ' files subdir
09       
10          With Application.FileSearch
11              .NewSearch
12              .LookIn = strPath
13              .SearchSubFolders = False
14              .Filename = ".pdf"
15       
16              If .Execute > 0 Then
17       
18                  For Each vaFileName In .FoundFiles
19                      ' open the workbook
20                      Application.FollowHyperlink vaFileName
21               
28                  Next
29              End If
30          End With
31      End Sub
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37739097
This definitely looks like it would work.. So I tweaked it slightly but haven't been able to test it yet because I'm getting an error on the .FileSearch function.

Do I need to reference another object in the references for this to work?

Here is the error I got;
error
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37739308
are you waitnig for me...?

A quick Google search says that FileSearch may have been deprecated...

Another simple Google search lead me to code like this:

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
   
    Set objFSO = CreateObject("Scripting.FileSystemObject")
     
    Set objFolder = objFSO.GetFolder("C:\YourFolder")

     'Loop through the Files collection
    For Each objFile In objFolder.Files
        If Right(objFile.Name, 3) = "xxx" Then
            'MsgBox objFile.Name
        End If
    Next
     
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37739313
I mean this is all stuff straight from Google...
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37739380
Sometimes it is difficult to get the key words correct when searching.

I did search google and noticed some links saying that filesearch was no longer availble but nothing seemed too consistent so I wasn't sure. Also again, I'm on here because I don't fully understand how to implement most code and seek guidance. My first source is always google and I try whenever I can to get my answers from them.

In any case, I greatly appreciate your time Boag as you are very knowledgeable in Access and can usually guide me through my questions.


Please note that I have had no official training in VBA or any other programming language what-so-ever and to this point am still working with my limited knowledge to accomplish my goals.

Most of the time I know what I want and have a vague clue what's required to get there but as far as actually getting there I usually need guidance and that includes help identifying what all aspects may affect the end result.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37739488
Hey Jeff,

I worked with the code you most recently posted.. Here is my editted version;
Private Sub txtAssemblySeq_AfterUpdate()
Dim strLink As String
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Users\jmeyer\Desktop\Drawings")

Me.Filter = "[jaJobNum] = " & "'" & Me.txtJobNum & "'" & " and " & "[jaAssemblySeq] = " & Me.txtAssemblySeq
Me.FilterOn = True

For Each objFile In objFolder.Files
    If objFile.Name Like [jaDrawNum] & "(REV" & [jaRevisionNum] & ")" & ".pdf" Then
    MsgBox objFile.Name
    End If
    Next
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub


Currently the code is skipping the If statement completely and going straight to Set objFolder = Nothing. I ran through it in debug mode and even removed the if statement to see it the messagebox would come up. That did not come up either. It seems to just keep skipping everything and going straight to Set objFolder = Nothing.

Any ideas why it's not running through the code before Set objFolder = Nothing?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37739732
...Then the condition is not being met...

in your original post, you are using "Like"...
Is this still the case?
...then you probably need the wildcards in there...

Something like this perhaps:
Me.Filter = "[jaJobNum] LIKE " & "'" & Me.txtJobNum & "'" & "*" &  " and " & "[jaAssemblySeq]  Like " & Me.txtAssemblySeq & "*"

To be sure do this in a messagebox:
msgbox Me.Filter
...to see if it looks ok...

;-)

Jeff

Perhaps my syntax is not 100%, but I am sure you get the idea...
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37739790
Thanks Jeff. I'll give that a go first thing in the morning. I did find out before I left this afternoon that it wasn't searching through the sub folders. In my test folders I have the main folder which contains two sub folders with one file in each. I had to move the files from the subs to the main folder in order to loop through them. I'll have to look into the sub folders situation as well tomorrow.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37740657
Searching subfolders requires "Recursion".
see here:
http://www.cpearson.com/excel/recursionandfso.htm
...and here is how I modified it for a Q a while back.:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27563310.html
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37742002
Jeff,

I managed to get the first loop working which searches through the main folder for the files and opens them.. I couldn't however get the recursion to work.. I just tried to copy the example they had that was supposed to list out all the sub folders in excel but I couldn't get that working.. It would help if I understood it a little better I guess.

Perhaps I could open another question that would deal just with the recursion?

Thanks
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37742307
Jeff, just wanted to give you an update.. I downloaded your modified version and got it to work when I changed the location from C:\ to C:\Users\jmeyer\Desktop\Drawings... I'm guessing because there are hidden folders or something under c:\ that causes a problem?

In any case, now that I have that working I can try to figure out how to modify it to open .pdf files in the recursion.. I'll get back after I try this for a bit.

Thanks for the help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37742648
The Loop Logic would be:

Loop the folders
When each new folder is hit on, then loop the files in that folder.

The Pseudo code would be something like this:

'Code to loop Folders
For each folder in Root Folder
    'Loop Files in the folder
    For each File in Folder
        'Open the file
    Next File
Next Folder

So if you have working code to loop the folders, and you have code to loop files in a folder, you are basically combining the two.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37747134
I catch what your throwing Jeff.. Sorry I didn't get back to you on this yesterday. Keep getting pulled into different directions. Same old same old.. Anyhow, I'm looking at this right now and will hopefully make some headway.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37748789
try something like this for just PDFs

(you will probably have to load a reference to the windows scripting runtime in your VBE)
Access-EERecursion--Recursive-Fi.mdb
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37752267
Thanks for that Jeff.. That did what I was looking for.


Another question though, although, if there is not easy way to this then I'm not worried about it as I think I'm going to try and push to change how our folders are structured and go down a different route later.

The code is only running through the first set of subfolders. So in a structure like


Target Folder
   SubFolder
              SubSubFolder
   SubFolder
              SubSubFolder
              SubSubFolder

The vba is only running through the SubFolders and not the SubSubFolders..

Again, if this there is not an easy solution to this setup I'm going to close this out anyways with the answers you have given me as they pretty much do what I asked for very well:)

Thanks again Jeff!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37752657
Yes, the "Recursion" code gets complex if you want it to branch out to all levels.

If you are up for it, you could always then loop the sub-subfolders...
;-)

Jeff
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37765732
I think I'll wrap this one up at this level. Thinking I want to approach the final product of this through a different method.. Thinking I may set up a database to hold all of the information about all the .pdf files. It's really been a long time coming as it is.

Thanks for all the help though Jeff! I'll be able to use this method on my modified version as well:)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now