Importing CSVs to Access with VBA

[tyija1995]

Hey, I have 55 CSV files that I want to export in to Access, I am currently using a macro but it only takes the first file from the directory, I need a loop to take all of the files from the same directory but I am not sure how to implement it, any help would be appreciated. I am using the following code currently:

Public Function Imprt_CSV()


 Dim strPathFile As String
 Dim strFile As String
 Dim strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean


    blnHasFieldNames = True


 strPath = Left(CurrentProject.Path, InStrRev(CurrentProject.Path, "\")) & "\OrigData\"


 strFile = Dir(strPath & "*.csv")
 
       strTable = strFile
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


       strFile = Dir()
       
End Function

Running this pushes through the first file in the directory I am using.

 

[welshgasman]

Set up a loop

Do While strFile <> ""
       strTable = strFile
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


       strFile = Dir()
Loop

I cannot see how you can have a table name the same as the file name as that also have the extension? xxx.csv