Import CSV to table using VBA

[marksimus]

Hi all,

I would like to use VBA to import a csv file into an existing access table. Fairly new to VBA, and this is out of my current league...could anyone please point me in the right direction.

Cheers for any help.

[TC99]

Hi marksimus,

This was in VB but I changed to VBA from memory so check it but it should work for you:

Function importData(sFileName As String, vDelimeter As Variant, sQuery As String)
    
    Dim vArray() As String
    Dim iLocalLoop As Integer
    Dim db as Database
    Dim rst As New Recordset
    Dim sLine As String
    
    Close #1
    Open sFileName For Input As #1
    Set db = CurrentDB
    Set rst = db.OpenRecordset(sQuery)
    
    Do Until EOF(1)
        sLine = ""
        Line Input #1, sLine
        vArray = Split(sLine, vDelimeter)    ' vDelimeter can be vbtab or a comma ',' etc.
        rst.AddNew
        iLocalLoop = 0
        For iLocalLoop = 0 To UBound(vArray())
            On Error Resume Next            ' In case of vartype error
            rst.Fields(iLocalLoop) = vArray(iLocalLoop)
            On Error GoTo ErrMsg
        Next iLocalLoop
        rst.Update
    Loop
    
    Close #1
    rst.Close
    set rst = Nothing
    
End Function

You just need to pass it the filename with path, delimeter and the query or table name.

Hope it helps.

TC

[marksimus]

Thanks TC,

This is exactly what I need.

Cheers

Marksimus