msoFileDialogFilePicker Getting files with a certain prefix and choosing a corresponding file

[NickWren]

So I have a code that does work, it opens up a folder location, then all you have to do is select the folder you want; however there is another file that goes along with this file that I want it to pull with it.

I don't know what the code would be to get the files prefixs but I want it to only find the files with "NETEST_NormalizedData_*" Then to find the corresponding file (the only other file in that folder that has the prefix) "RawData_*"


I dont want to hit the button twice nor have it so that another window pops up for us to select the file. I just want the second file to be picked automatically along with only showing files with those certain prefixes.

Thank you for any input!

Private Sub Command1523_Click()
    Dim fdg As FileDialog, vrtSelectedItem As Variant
    Dim strSelectedFile As String
    Dim SampleFileName As String
    Dim SampleFile As String
    Dim SampleIDNum As String
    
    Set fdg = Application.FileDialog(msoFileDialogFilePicker)
     
    With fdg
      .AllowMultiSelect = False
      .Filters.Add "Comma Delimited Files", "*.csv", 1
      .InitialFileName = "//xxx/clinical/xxx-xxx/" & Year(Date) & "/" & Format(Month(Date), "00") & "." & Year(Date) & "/"
      .InitialView = msoFileDialogViewDetails
        If .Show = -1 Then
          For Each vrtSelectedItem In .SelectedItems    'onby be 1
            strSelectedFile = vrtSelectedItem
          Next vrtSelectedItem
          Me![txtSelectedFile] = strSelectedFile
          
    Dim xl              As Object
    Dim xlWrkBk         As Object
    Dim xlSht           As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = False 'Control whether or not Excel should be visible to
                            'the user or not.
    Set xlWrkBk = xl.Workbooks.Open(txtSelectedFile)
    Set xlSht = xlWrkBk.Worksheets(1)
    Me.ExcelScore = xlSht.Cells(2, 12) * 100 'RowNo, ColumnNo
    If ExcelScore = 0 Then
    MsgBox "You have uploaded the wrong excel file", vbOKCancel, "Wrong Excel File"
    Else
    SampleFileName = xlSht.Cells(2, 1)
    SampleFile = Right(SampleFileName, 4)
    SampleIDNum = Right(Me.SampleID, 4)
        If SampleFile = SampleIDNum Then
            Me.Text573 = ExcelScore
            Me.NETScore = Text573
            DoCmd.RunCommand acCmdSaveRecord
        End If
                      xlWrkBk.Close False 'Close the Workbook without saving any changes
    xl.Quit 'Close Excel
    End If

          
        Else     'The user pressed Cancel.
        MsgBox "You did not select a file."
        End If
    End With

    

    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xl = Nothing
    Set fd = Nothing
End Sub

[RuralGuy]

Just to double check, there is only one file in the selected folder that will match the "RawData_*" file spec, right?

[NickWren]

yes, and only one file that will match "NETEST_NormalizedData_*"

[RuralGuy]

That should be a snap. I'll take a run at it.

[RuralGuy]

Is this for MS Access or Excel?

[NickWren]

its for importing excel data into access, from a button inside access

[RuralGuy]

Why are you putting the .csv into Excel 1st? Access knows how to import .csv files.

[NickWren]

I am taking the excel file (the file is in a csv format) then importing the data associated with that sample. so im not putting it into excel, just opening up a document. I know access knows how to import csv, but I am uploading one file that I select, and I want it to automatically upload the corresponding file which has more data for that file.

[RuralGuy]

Are you wanting to use Excel on the "other" file as well? I'm still confused why Excel is even involved. I guess I'm just thick headed.