Import from Excel into Access

[darude]

Hello All,
I have an MS Access application that imports data from many different excel spreadsheets located in a directory by member name. The problem is that I have an import routine in Access for each member that references the folder on the directory where the member is stored (see routine below). I was wondering if there is a generic call so I can only have one routine. If my client adds new members then I have to go into the application and add another routine. Thank you in advance.

Sub ImportSAHTF()

If Me.txtWhatForm = 1 Then
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="EUF", _
FileName:=CurrentProject.Path & "\EUF\sahtf\sahtf access.xls", _
HasFieldNames:=True
End If

End Sub

 

[PWise]

Sub Importxxx(client As String)

If Me.txtWhatForm = 1 Then
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="EUF", _
FileName:=CurrentProject.Path & "\EUF\" & client & "\" & client & " access.xls", _
HasFieldNames:=True
End If

End Sub

 

[darude]

THANK YOU SO MUCH!