FSO When Naming Conventions are Inconsistant

Rekd

Banned
Joined
Apr 28, 2010
Messages
136
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. :biggrin:

Code:
' 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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,539
Office Version
365
Platform
Windows
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

Banned
Joined
Apr 28, 2010
Messages
136
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'.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,380
Messages
5,444,113
Members
405,268
Latest member
JLEMS

This Week's Hot Topics

Top