CSV into a table inside a MS Access database using Excel VBA

Question

I want to be able to take a csv file like this one http://ichart.yahoo.com/table.csv?s=^GSPC, which would be located in the workbook folder and make it as a table named GSPC inside an already existing blank MS Access database. The blank database file would be located in the same folder and named tblImport.accdb. I have looked at many forum threads and found nothing simple enough for a noob like me. I'm very happy for any kind of help.

At least give it a shot and people will have some incentive to help you out. — Tarik, Aug 04 '13 at 13:12
If you plan on VBA, you'll need to look into SQL `Create` to make a table and http://stackoverflow.com/questions/9564908/open-csv-file-via-vba-performance to see how CSVs can be opened. Also, if you plan on manipulating the data after then look into recordsets. — Grant, Aug 04 '13 at 14:27
I don't understand why you want to get Excel involved with this. If you want to use VBA to import CSV into Access, you can use `DoCmd.TransferText` from Access VBA. It can create the Access table for you and save the CSV data in that table. http://msdn.microsoft.com/en-us/library/office/bb214141(v=office.12).aspx — HansUp, Aug 04 '13 at 15:09
Thank you very much for your comments Tarik, TKEyi60 and HansUp. I´m Using excel VBA because I want to make a user interface in a macro enabled Excel file that serves the purpose of being a customized database for financial instruments. I also want to have all the query power from using Access and SQL. I guess I´m doing the structure all wrong. Maybe I should ask a general question based on what I´m actually trying to accomplish? F.y.i. I just got a student job in a bank and they use Excel VBA and want me to get familiar with SQL in that context. — steinbitur, Aug 04 '13 at 15:47

HansUp · Accepted Answer · 2013-08-04T20:58:46.113

The easiest way to import CSV into Access is to use DoCmd.TransferText from within an Access application session.

But you want to use Excel VBA. In that case, if your MS Office installation includes Access (MSACCESS.EXE), you can use Excel VBA to automate Access and still make use of DoCmd.TransferText for easy CSV import.

I tested this module in Excel 2007. It creates the GSPC table and stores the data from table.csv in that table. If the table already exists, TransferText will simply append the CSV data. You can execute DELETE FROM GSPC before running TransferText so the table will contain only the latest CSV data.

This may look a bit intimidating considering you said you're a noob. However much of the following is comments I added to guide you. The actual "guts" of that procedure is fairly short and simple.

Option Explicit

Public Sub ImportCsvToAccess()
    Const cstrCsvFile As String = "table.csv"
    Const cstrDbFile As String = "tblImport.accdb"
    Const cstrTable As String = "GSPC"
    Dim strFolder As String

    '* early binding *'
    ' requires reference to Microsoft Access <version> Object Library
    'Dim objAccess As Access.Application
    'Set objAccess = New Access.Application

    '* late binding *'
    ' no reference required
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")

    ' this is useful during development
    ' in production, you may prefer to hide it (Visible = False)
    objAccess.Visible = True

    strFolder = ActiveWorkbook.Path & Chr(92)
    'Debug.Print strFolder
    objAccess.OpenCurrentDatabase strFolder & cstrDbFile, _
        Exclusive:=True

    '* early binding *'
    'objAccess.DoCmd.TransferText _
    '    TransferType:=acImportDelim, _
    '    TableName:=cstrTable, _
    '    Filename:=strFolder & cstrCsvFile, _
    '    HasFieldNames:=True

    '* late binding *'
    ' acImportDelim = 0
    objAccess.DoCmd.TransferText _
        TransferType:=0, _
        TableName:=cstrTable, _
        Filename:=strFolder & cstrCsvFile, _
        HasFieldNames:=True

    objAccess.Quit
    Set objAccess = Nothing
End Sub

Early binding is useful during development because it gives you Intellisense and type checking. Late binding for production means you can discard the reference to avoid deployment hassles with different Office versions. But you can do early binding for both if you wish. — HansUp, Aug 04 '13 at 17:00
This is a beautiful solution. It works faster and better than I could have hoped for. Thank you for the comments as well. Thank you HansUp. — steinbitur, Aug 04 '13 at 20:14

CSV into a table inside a MS Access database using Excel VBA

1 Answers1