Opening outside files that contain a specific number in the file name...

[gills]

Hey all,

I've created an userform interface for an "engineering drawing" database at my job that can be used to add, search, edit and delete drawings to/from the database. Nothing too fancy, the database is in the same workbook as the userform and it collects 6 different criteria of information (i.e. Drawing #, Date, Customer, etc..).

More importantly it can also be used to open up user selected pdf engineering drawing files that are located in a network folder. Basically, the user searches for a drawing number in the excel workbook (i.e. - 11425) and then has the option to open up the corresponding engineering drawing pdf file that's located in a network folder.

The issue I'm having is that sometimes a drawing has a few revisions in the network folder and can be labeled as such: 11425, 11425-R1, 11425-R2, etc... I want to be able to open all files containing the root drawing number (11425) so if there are four drawing revisions plus the original, I want all 5 drawings to be opened.

The code I have right now to open an exact match is (with some other things going on):

Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hWnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long
    
    Dim Matches As New Collection
    Dim i As Integer, Addrs As Long
    Dim ws As Worksheet
_______________________________________________________________________________________
Private Sub OpenDwg_Click()

    Dim FileName As String, DwgNum As String
    Dim Result As Long
    
    For b = 0 To SearchResultsBox.ListCount - 1
            If SearchResultsBox.Selected(b) Then
            k = k + 1
        Else
        End If
    Next b
'   error handling------------------------------------------------------------------------
    If k = 0 Then
        MsgBox "Please select a DWG to open.", vbExclamation, "Selection"
        GoTo error1
    ElseIf k > 1 Then
        MsgBox "Only one DWG can be opened at a time.", vbExclamation, "Selection"
        GoTo error1
    Else
    End If
' end error handling-------------------------------------------------------------------

    For j = 0 To (i - 1)
            If SearchResultsBox.Selected(j) = True Then
                Addrs = Matches(j + 1)
                DwgNum = ws.Range("A" & Addrs)
            Else
            End If
        Next j
   
    
    FileName = "\\FS2\Cadd\Projects\" & DwgNum & ".pdf"
    Result = ShellExecute(0&, vbNullString, FileName, vbNullString, vbNullString, vbNormalFocus)
    If Result < 32 Then MsgBox "DWG #: " & DwgNum & " not found", vbInformation, "Not Found"
    
error1:
End Sub

I'm new to VBA programming and have been referencing this book: http://="http://www.amazon.com/Excel...dp/0470044012", which is where I got the "ShellExecute" technique from, otherwise I would've never have known to do that.

I tried putting an asterisk in front of .pdf for the filename as some sort of wildcard, but that didn't work. I'm assuming I'm also going to need to add some sort of while loop if there is a drawing with multiple revisions.

Any help would be fantastic!

-Tom

[gills]

Ok, here's what I've figured out. If someone has another method they think is easier, don't hesitate to post:

Private Sub OpenDwg_Click()

    Dim DwgNum As String
    Dim Result As Long
    Dim Directory As String
    Dim f As String
      
    For b = 0 To SearchResultsBox.ListCount - 1
            If SearchResultsBox.Selected(b) Then
            k = k + 1
        Else
        End If
    Next b
    
'   error handling------------------------------------------------------------------------
    If k = 0 Then
        MsgBox "Please select a DWG to open.", vbExclamation, "Selection"
        GoTo error1
    ElseIf k > 1 Then
        MsgBox "Only one DWG can be opened at a time.", vbExclamation, "Selection"
        GoTo error1
    Else
    End If
' end error handling-------------------------------------------------------------------

' Determining the corresponding cell with DWG # with dropbox selection
    For j = 0 To (i - 1)
            If SearchResultsBox.Selected(j) = True Then
                Addrs = Matches(j + 1)
                DwgNum = ws.Range("A" & Addrs)
            Else
            End If
        Next j
    
'   Looping through pdf directory and opening all files with root DWG #
             
    Directory = "\\FS2\Cadd\Projects\"
    f = Dir(Directory & "*.pdf", vbReadOnly + vbHidden + vbSystem)
    Do While f <> ""
        If f Like DwgNum & "****************************************************************************.pdf" Then
            Result = ShellExecute(0&, vbNullString, Directory & f, vbNullString, vbNullString, vbNormalNoFocus)
        Else
        End If
       f = Dir()
    Loop
    
    If Result < 32 Then MsgBox "DWG # " & DwgNum & " not found", vbInformation, "Not Found"
    
error1:
End Sub