Import many csv files in same table

Question

Hello everyone,

I have a code where we can import many csv files in one table and works fine,

the problem is that we need to drop the csv files in a specific static folder.

is there a way to adapting this code with a dialog function to allow the user

to looking the files in any folder and  then selected one or many files to import

in one table? 

Thanks for your support

Carlos

---My 365 access code-------

Private Sub Command74_Click()

Dim stLinkCriteria As String

Const strPath As String = "C:" 'Directory Path

 Dim strFile As String 'Filename

 Dim strFileList() As String 'File Array

 Dim intFile As Integer 'File Number

DoCmd.SetWarnings False

 'Loop through the folder & build file list

strFile = Dir(strPath & "*.csv")

''''''' This line does not working

'''''''strFile = Application.GetOpenFileName(, strPath & "*.csv")

 While strFile <> ""

 'add files to the list

 intFile = intFile + 1

 ReDim Preserve strFileList(1 To intFile)

 strFileList(intFile) = strFile

 strFile = Dir()

 Wend

 'see if any files were found

 If intFile = 0 Then

 MsgBox "No files found"

 Exit Sub

 End If

 'cycle through the list of files & import to Access

 'creating a new table called MyTable

 For intFile = 1 To UBound(strFileList)

'''''''Delete old data

DoCmd.RunSQL ("DELETE * FROM my table")

'''''''Import fresh data

DoCmd.TransferText acImportDelim, "mySpec  Import Specification", "MyTable", strPath & strFileList(intFile), 0

Next

MsgBox UBound(strFileList) & " Files were Imported"

End Sub

Answer 1

Hello,

I  check the reference 16.0 library , now let me selected the files but I have a error message Overflow (run-time error 6

Thank for your help

Carlos

Answer 2

Hi Duane,

Thanks for your tip, I will check this and I'll keep you posted

Carlos

Answer 3

You stated the "GetOpenFileName" is not working. Did you set a reference to the current Microsoft Office xx.x Object Library. My Access 2016 is the 16.0 library.

I believe that should resolve your issue and allow you to have the user select a folder.

There is documentation at https://docs.microsoft.com/en-us/office/vba/api...