Help with import of data

[Paulmc1981]

Hi and thank you in advance... as I am not very good as VBA (The code supplied was found on the internet).



I have used some code (see code below) to link some csv files into MS Access, I need this changed so that it does the following:

1) Checks if a table exists to log what csv files have already been used
- If no table exists create one
- If table exists, continue

2) Check the table that logs the csv files that have already been imported
- If there are no new csv files - end
- If there are new csv file(s) - import using the below code and add the csv file to the table that logs what csv files have been imported

The code I already have is:

Sub DoImport()


Dim fso As Object
Dim fld As Object
Dim fil As Object
Dim FldPath As String

Const DestTable As String = "imptable"

FldPath = "C:\Users\paul.coan\Desktop\HistoricalData"



Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FldPath)

With DoCmd
.SetWarnings False
For Each fil In fld.Files
If UCase(Right(fil.Name, 3)) = "CSV" Then
.TransferText acImportDelim, , DestTable, fil.Path, True
End If
Next
.SetWarnings True
End With


Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

MsgBox "done"


End Sub

[jonh]

Sub DoImport()


	Dim fil As Object


	Const FldPath As String= "C:\Users\paul.coan\Desktop\HistoricalData"
	Const DestTable As String = "imptable"
	
	MakeImportTable


	For Each fil In CreateObject("Scripting.FileSystemObject").GetFolder(FldPath).Files
		If UCase(Right(fil.Name, 3)) = "CSV" Then


		    importit = CurrentDb.OpenRecordset("select filename from ImportLog where filename='" & fil.Path & "'").EOF
            
            If importit Then
                DoCmd.TransferText acImportDelim, , DestTable, fil.Path, True
                CurrentDb.Execute ("insert into ImportLog (filename,imported) values ('" & fil.Path & "',#" & Format(Now, "dd-mmm-yyyy hh:nn:ss") & "#)")
                MsgBox "'" & fil.Path & "' imported ok."
            Else
                MsgBox "'" & fil.Path & "' was not imported"
            End If


		End If
	Next


	MsgBox "done"
	
End Sub


Private Sub MakeImportTable()
On Error Resume Next
    RefreshDatabaseWindow
    CurrentDb.Execute "create table ImportLog"
    CurrentDb.Execute "alter table ImportLog add filename text 255"
    CurrentDb.Execute "alter table ImportLog add imported datetime"
    RefreshDatabaseWindow
End Sub

[Paulmc1981]

WOW! Thanks indeed!