Importing CSVs to Access with VBA

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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:

Code:
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
Set up a loop

Code:
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 :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,095,725
Messages
5,446,150
Members
405,385
Latest member
xcookiemonster64

This Week's Hot Topics

Top