deadeye123
New Member
- Joined
- Dec 27, 2016
- Messages
- 10
I am not a VBA expert. What I would like to do, is to append those csv files to my table in Access. It's an ongoing process, so those CSV files keep coming in to my folder every day. I don't want to copy the headers though since I already have the headers in my Access table that i created. (I could delete my table in Access if I can find a code that just appends the CSV files and creates the headers and table in Access).
I tried the code below but the issue is, that everytime I use it, it appends the whole csv files to my table. those csv files have headers but the headers are loaded as actual data in my table and not as headers. the files are located under C:\files. any help is appcreciated
Sub Import_multiple_csv_files()
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Const strPath As String = "C:\Contacts" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
strFile = Dir(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)
DoCmd.TransferText acImportDelimi, , _
"Test", strPath & strFileList(intFile)
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"</code>End Sub
I tried the code below but the issue is, that everytime I use it, it appends the whole csv files to my table. those csv files have headers but the headers are loaded as actual data in my table and not as headers. the files are located under C:\files. any help is appcreciated
Sub Import_multiple_csv_files()
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Const strPath As String = "C:\Contacts" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
'Loop through the folder & build file list
strFile = Dir(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)
DoCmd.TransferText acImportDelimi, , _
"Test", strPath & strFileList(intFile)
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"</code>End Sub
