opening multiple files

[stephenaa5]

Good day. Our company must keep many client signed documents, which we store in PDF. My interface form has a field which is doubleclicked to bring up the corresponding document. All good, until there are multiple documents. The system stores these as the same filename with an extension of "-1" "-2" etc.

Below is the code I use to open the single file. Any suggestions as to modifying or rewriting it to open the extension files as well would be greatly appreciated.

Stephen



begin code........................

Private Sub ControlNumber_DblClick(Cancel As Integer)
Dim TheFile As String
Dim FileExt As String
Dim thepath As String

thepath = "Q:\Data\ServiceDatabase\worktickets\"
TheFile = ControlNumber.value
FileExt = ".pdf"

Application.FollowHyperlink thepath & TheFile & FileExt
End Sub

[RuralGuy]

So if the Control Number is 111111 and the file is 111111.pdf, what would be the 2nd filename?

[Dal Jeanis]

I'm assuming that you mean the -1 or -2 is part of the filename, rather than replacing the .pdf extension. (myname-1.pdf rather than myname.-1 or myname.pdf.-1)

Private Sub ControlNumber_DblClick(Cancel As Integer)
Dim TheFile As String
Dim TheNumber As String
Dim FileExt As String
Dim thepath As String
thepath = "Q:\Data\ServiceDatabase\worktickets\"
TheFile = ControlNumber.value
TheNumber = "-" & {{{{This is where you code which of the files you want to get... 1, 2, 3 etc}}}}}
FileExt = ".pdf"
Application.FollowHyperlink thepath & TheFile & TheNumber & FileExt
End Sub

[stephenaa5]

The "-x" extension will add to the filename, not the .pdf. So it will be 1111.pdf; 1111-1.pdf; 1111-2.pdf, etc. An unknown quantity of extensions, no more than 4 or 5, but as shown in this example there could be NO "-x" as well.

[RuralGuy]

Do you want to open all of them at the same time or one at a time? Can you take it from here with what Dal supplied or would you like some additional code?

[stephenaa5]

I'd like to open all at once. The code makes sense, I have to figure out how to loop through, and end the loop, if there is or is not a -x. Any help would be great. Unfortunately, I'm an electrician and not a programmer, but I'm learning....

Thanks,
Stephen.

[Dal Jeanis]

Is your user really going to want to always open every PDF associated with the account? Or is this the time to create a small table that gives a short description of each document, so they can choose the right one?

[stephenaa5]

Actually, the need is to open all. This is an invoicing function, and the documents are field drawings, etc that must be reviewed to determine invoicing amounts. Typically, there are 1 or two. Sometimes there are more, but that is rare.

Stephen.

[RuralGuy]

Give this a try. I expanded Dal's code a bit but it is UNTESTED!

Private Sub ControlNumber_DblClick(Cancel As Integer)
   Dim TheFile As String
   Dim TheNumber As String
   Dim FileExt As String
   Dim thepath As String
   Dim LoopCounter As Integer
   LoopCounter = 0
   Dim Looping As Boolean
   Looping = True
   thepath = "Q:\Data\ServiceDatabase\worktickets\"
   TheFile = ControlNumber.Value
   FileExt = ".pdf"
   TheNumber = ""
   Do While Dir(thepath & TheFile & TheNumber & FileExt) <> ""
      Application.FollowHyperlink thepath & TheFile & TheNumber & FileExt
      LoopCounter = LoopCounter + 1
      TheFile = Me.ControlNumber & "-" & LoopCounter
   Loop
End Sub

[Dal Jeanis]

Minor modification in red.

   TheNumber = ""
   Do While Dir(thepath & TheFile & TheNumber & FileExt) <> ""
      Application.FollowHyperlink thepath & TheFile & TheNumber & FileExt
      LoopCounter = LoopCounter + 1
      TheNumber = "-" & LoopCounter
  Loop
End Sub

[RuralGuy]

Oops! Thanks Dal.

[stephenaa5]

Awesome, guys. I'm away until Sunday, but will try it, and report as solved once I can confirm. Many, many thanks.

[Dal Jeanis]

Your way would have worked, I think, but TheNumber would have been redundant. This fix changed less lines.

[RuralGuy]

It is what I had intended to do. I just lost track of which variable I was changing. Getting old sucks. Happy New Year btw.

[stephenaa5]

I finally got a chance to implement this. It worked like a charm. Thank you, guys.