FSO When Naming Conventions are Inconsistant

[Rekd]

I've got a folder full of drawings, over 70,000 of them. They are all drawings in PDF format with the part number as the name. Sometimes.

There are half a dozen different ways the files are stored, all with the first 7 chars being the part number, say 12345678.pdf

Many of the files do not follow that naming convention though. Some are stored as:
12345678.pdf
12345678101.pdf
12345678101 - Sheet1.pdf
12345678 - Sheet1.pdf
12345678XXX.pdf
123456781XX.pdf
123456781XX - Sheet1.pdf

I've got some code from another function that I do basically the same thing, but it's pretty dirty code and I'm sure there's a way to do it better.

I don't know FSO that well but I'm hoping I can provide a partial file name and have it return a match that I can get the full file name from to attach to an email.

The code I've got is below, please don't laugh at it.

' If PDF exists, process cell into link and rename cell
If oFSO.FileExists("N:\pdfs\engineering\" & cellPointer & ".PDF") Then
    If cellPointer <> "" Then 'And cellPointer.Offset(0, 1).Value <> "" Then
        Worksheets(sWS).Cells(I, iRow + 1).Hyperlinks.add Anchor:=Worksheets(sWS).Cells(I, iRow + 1), _
            Address:="N:\pdfs\engineering\" & cellPointer & ".PDF", _
            TextToDisplay:="Yes"
    End If
 
' Can't find the PDF. Try adding " - Sheet1" to end of string
ElseIf oFSO.FileExists("N:\pdfs\engineering\" & _
        cellPointer & " - Sheet1" & ".PDF") Then
    If cellPointer <> "" Then 'And cellPointer.Offset(0, 1).Value <> "" Then
        Worksheets(sWS).Cells(I, iRow + 1).Hyperlinks.add Anchor:=Worksheets(sWS).Cells(I, iRow + 1), _
            Address:="N:\pdfs\engineering\" & cellPointer & " - Sheet1.PDF", _
            TextToDisplay:="Yes"
    End If
 
' Can't find the PDF. Try changing last digit to "X"
ElseIf oFSO.FileExists("N:\pdfs\engineering\" & _
        Left(cellPointer, Len(cellPointer) - 1) & "X" & ".PDF") Then
    If cellPointer <> "" Then 'And cellPointer.Offset(0, 1).Value <> "" Then
        Worksheets(sWS).Cells(I, iRow + 1).Hyperlinks.add Anchor:=Worksheets(sWS).Cells(I, iRow + 1), _
            Address:="N:\pdfs\engineering\" & (Left(cellPointer, (Len(cellPointer) - 1)) & "X") & ".PDF", _
            TextToDisplay:="Yes"
    End If
 
' Still Can't find PDF... try trimming down to 7 chars, needs work
Else
    cellPointer = Left(cellPointer, 7)
    If oFSO.FileExists("N:\pdfs\engineering\" & cellPointer & ".PDF") Then
    If cellPointer <> "" Then 'And cellPointer.Offset(0, 1).Value <> "" Then
        Worksheets(sWS).Cells(I, iRow + 1).Hyperlinks.add Anchor:=Worksheets(sWS).Cells(I, iRow + 1), _
            Address:="N:\pdfs\engineering\" & cellPointer & ".PDF", _
            TextToDisplay:="Yes"
    End If
    End If
' If still can't find it, bail. (To Do: Test for spaces in PDF names)
End If

This code doesn't cover all the possible naming methods but it gives an idea of how messed up it is.

Thanks!

 

[Norie]

Why do you need code that covers all the naming conventions?

Doesn't FSO support wildcards, if it doesn't try using Dir instead.

 

[Rekd]

Why do you need code that covers all the naming conventions?

Doesn't FSO support wildcards, if it doesn't try using Dir instead.

I'm fairly ignorant when it comes to FSO. I tried Dir but couldn't get it to work well over the network. Considering that was a while ago perhaps it's time to try again.

I'll go research both and try some things. Thanks for the 'nudge'.