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.
This code doesn't cover all the possible naming methods but it gives an idea of how messed up it is.
Thanks!
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.
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!