Open multiple files 2

[PALman]

The following code finds and opens a document Ok.
However I need to add code to make it loop and search/open others when there is more than one document in the folder.
At the moment the code just opens the first it finds.
Each file in the folder has a common job number as the first part of its filename and the second part of filename would differ.
Also would it be possible to make a list of the documents found as well as opening them.

Path04_ConsAccept = "C:\Quality\ConsAcceptances\"
filename = Dir(Path04_ConsAccept + Me.JobNo & "*")
strFile = Path04_ConsAccept + filename
FollowHyperlink strFile

Any help much appreciated.

 

[Remou]

Some notes:

Dim strFile, Filename, astrFile
Path04_ConsAccept = "C:\Quality\ConsAcceptances\"
Filename = Dir(Path04_ConsAccept) ' + Me.JobNo & "*")
Do While Filename <> ""
    strFile = strFile & Path04_ConsAccept & Filename & "|"
    Filename = Dir
Loop
'Debug.Print strFile
astrFile = Split(strFile, "|")
MsgBox UBound(astrFile) + 1 & " files found."
For i = 0 To UBound(astrFile)
    FollowHyperlink strFile
    'Debug.Print astrFile(i)
Next

 

[PALman]

Thanks Remou,
Coding is now opening multiple documents, however it opens the first it finds multiple times and ignores the others in the folder. I shall check to see that I have copied your code correctly ASAP.

 

[Remou]

No, that is my fault, I cut and pasted the FollowHyperlink, it should be:
FollowHyperlink astrFile(i)

 

[PALman]

Thanks Remou,
Code is almost there. At the moment it loads up everything in the folder which amounts to 4 documents. It counts that it has found 5 documents and seems to ignore Me.JobNo as it loads one file with a different JobNo which it should ignore.
Any further help very much appreciated.

 

[Remou]

Ok

Dim strFile, Filename, astrFile
If Trim(Me.JobNo & " ") = "" Then
    MsgBox "No Job No."
    Exit Sub
End If
Path04_ConsAccept = "C:\Quality\ConsAcceptances\"
Filename = Dir(Path04_ConsAccept + Me.JobNo & "*")
Do While Filename <> ""
    strFile = strFile & Path04_ConsAccept & Filename & "|"
    Filename = Dir
Loop
'Debug.Print strFile
astrFile = Split(strFile, "|")
MsgBox UBound(astrFile) & " files found."
For i = 0 To UBound(astrFile)
    FollowHyperlink astrFile(i)
    'Debug.Print astrFile(i)
Next

 

[PALman]

Sorry Remou,
Coding still not using Me.JobNo correctly.
It still counts that it has found 5 documents intead of 4, and ignores Me.JobNo as it loads one file with a different JobNo which it should ignore.
Thanks

 

[Remou]

What are the 5 file names?

 

[PALman]

The filnames are...
226537-CONS-51339.htm
226537-CONS-51340.htm
226537-CONS-51341.pdf
226537-CONS-51342.pdf
and the fifth file is
226556-CONS-52668.pdf which is the file it should ignore

 

[PHV]

You may try to either replace this:
astrFile = Split(strFile, "|")
with this:
astrFile = Split(Left(strFile, Len(strFile) - 1), "|")

Or replace this:
For i = 0 To UBound(astrFile)
with this:
For i = 0 To UBound(astrFile) - 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Thanks PHV,
But both your suggestions do not give required result.
Me.JobNo still picks up two different Job No's... 226537 the active JobNo and 226556 the inactive JobNo.

 

[PHV]

Provided that Me!JobNo = 226537, what about this ?
Path04_ConsAccept = "C:\Quality\ConsAcceptances\"
strFile = Dir(Path04_ConsAccept & Me!JobNo & "-*.*")
Do While strFile <> ""
FollowHyperlink Path04_ConsAccept & strFile
strFile = Dir
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Sorry PHV,
latest code finds -1 documents.
I must check my coding in the morning (23:00 here) it has probably got a little mixed up trying different things.
Thanks and please continue to help if you have anything else I could try tomorrow.

 

[PHV]

So, post your actual code !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Actual coding as it stands at the moment with added in suggestions...
'FIND ANY ACCEPTED CONCESSION
Private Sub CONSaccepted_Click()
On Error GoTo Err_CONSaccepted_Click

Dim strFile As String, Filename As String
Dim Path04_ConcessionsAccept As String
Dim astrFile

If Trim(Me.JobNo & " ") = "" Then
MsgBox "No Job No."
Exit Sub
End If

Path04_ConcessionsAccept = "C:\000-Quality Control Program\Concession Acceptances\"
Filename = Dir(Path04_ConcessionsAccept) ' + Me.JobNo & "*")

'This next section rem'd out to try another suggestion...
'Do While Filename <> ""
'MsgBox Me.JobNo
' strFile = strFile & Path04_ConcessionsAccept & Filename & "|"
' Filename = Dir
'MsgBox Filename
'Loop

'Path04_ConsAccept = "C:\Quality\ConsAcceptances\"
strFile = Dir(Path04_ConsAccept & Me!JobNo & "-*.*")
Do While strFile <> ""
FollowHyperlink Path04_ConsAccept & strFile
strFile = Dir
Loop

Debug.Print strFile
astrFile = Split(strFile, "|")
MsgBox UBound(astrFile) & " file/s found."
For i = 0 To UBound(astrFile)
FollowHyperlink astrFile(i)
Debug.Print astrFile(i)
Next

Exit_CONSaccepted_Click:
Exit Sub
Err_CONSaccepted_Click:
MsgBox Err.Description
Resume Exit_CONSaccepted_Click
End Sub
As stated earlier above code was loading ALL files instead of the active JobNo (using Me.JobNo) and after latest edit does not load anything and shows -1 documents in the part which counts the files found.
Any further help greatly appreciated.

 

[PHV]

And what about this ?
Private Sub CONSaccepted_Click()
Dim strFile As String
Dim Path04_ConcessionsAccept As String
Dim i As Integer

If Trim(Me!JobNo & "") = "" Then
MsgBox "No Job No."
Exit Sub
End If
Debug.Print "JobNo='" & Me!JobNo & "'"
Path04_ConcessionsAccept = "C:\000-Quality Control Program\Concession Acceptances\"
strFile = Dir(Path04_ConsAccept & Me!JobNo & "-*.*")
Do While strFile <> ""
i = i + 1
Debug.Print i, strFile
FollowHyperlink Path04_ConsAccept & strFile
strFile = Dir
Loop
MsgBox i & " file/s found."
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Thanks PH and Remou.
The final listing PH gave me has done the trick. Program now finds the correct four files and ignores the others in folder using Me!JobNo or Me.JobNo.
I did have to correct Path04_ConsAccept to Path04_ConcessionsAccept.
Thanks again.
PALman.

 

[PALman]

To improve on the program that I have had much help on I would like to move the line of coding which locates the folder from the Private Sub and place it for global use. I have a few paths like this and it would seem to make sense to have them together. The line is…
Path04_ConcessionsAccept = "C:\000-Quality Control Program\Concession Acceptances\"
Other Lines start Path01_ Path02_ etc.
Is this possible and how can I do it and how would I call this from the Private Sub?
Again any further help is much appreciated.

 

[Remou]

You can set these paths as constants at module level, but it might be better to keep them in a small 'system table' which can be dlookup-ed. A table is much easier to maintain.

 

[PALman]

Hi Remou,
I have tried your suggestion and created table named Folder Paths with fields Path and PathNo and code as below...

Debug.Print "JobNo='" & Me!JobNo & "'"
'REMMED OUT THIS LINE... Path04_ConcessionsAccept = "C:\000-Quality Control Program\Concession Acceptances\"
Path = DLookup("[Path]", "Folder Paths", "[PathNo] ='Path04'")
MsgBox Path (This gives correct path)

However this next line causes error "Bad File or Number"
strFile = Dir(Path & Me!JobNo & "-*.*")

Do While strFile <> ""
i = i + 1
Debug.Print i, strFile
FollowHyperlink Path & strFile
strFile = Dir
Loop

MsgBox "Accepted Concessions: " & i & " found."

As usual any further help appreciated.
Thanks again
PALman