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!