I created a solution in Access97 that import data from comma delimited files using this process:
1. identify the csv filename and path
2. copy the data from the file into the target table
First you must establish an import profile specific to your CSV file format by manually going through the process of importing a csv file and naming and saving the resulting profile. This can then be used with the Docmd.transferText method to bring the data into the table. By using this profile with the Docmd.TransferText method you can copy the data from any csv with the same format.
here's the code that transfers the text:
Docmd.Transfertext acImportDelim,"nameofprofile","nameoftable","filepathandname"
Here's the code I use to gather the file details for the combo-box:
Function ManifestList(fld As Control, ID As Variant, row As Variant, _
col As Variant, Code As Variant) As Variant
Dim stRCriteria As String, strFile As String, strPath As String, strExtn As String, intFiles As Integer
Select Case Code
Case acLBInitialize ' Initialize.
ManifestList = True
Case acLBOpen ' Open.
ManifestList = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ManifestList = -1
Case acLBGetColumnCount ' Get columns.
ManifestList = 2
Case acLBGetColumnWidth ' Get column width.
ManifestList = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intFiles = 0
strPath = "C:\CSV IMPORTS\"
strExtn = "*.csv"
stRCriteria = strPath & strExtn
strFile = Dir(stRCriteria)
Do While Len(strFile) > 0
If intFiles = row Then
Debug.Print col
If col = 0 Then
ManifestList = strFile
ElseIf col = 1 Then
ManifestList = FileDateTime(strPath & strFile)
End If
Exit Do
End If
intFiles = intFiles + 1
strFile = Dir
Loop
End Select
End Function
Change the function name ManifestList to whatever you prefer. Change the Path to the path on your C: drive where the files are parked and place the function name in the RowSourceType of the Combo box. Set the combo to a column count of 2 bound to column 1.
If your don't need the date/time info for your csv files then modify the function accordingly and set the combo to 1 column.
Function ManifestList(fld As Control, ID As Variant, row As Variant, _
col As Variant, Code As Variant) As Variant
Dim stRCriteria As String, strFile As String, strPath As String, strExtn As String, intFiles As Integer
Select Case Code
Case acLBInitialize ' Initialize.
ManifestList = True
Case acLBOpen ' Open.
ManifestList = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ManifestList = -1
Case acLBGetColumnCount ' Get columns.
ManifestList = 1
Case acLBGetColumnWidth ' Get column width.
ManifestList = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intFiles = 0
strPath = "C:\CSV IMPORTS\"
strExtn = "*.csv"
stRCriteria = strPath & strExtn
strFile = Dir(stRCriteria)
Do While Len(strFile) > 0
If intFiles = row Then
ManifestList = strFile
Exit Do
End If
intFiles = intFiles + 1
strFile = Dir
Loop
End Select
End Function
