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
– Tarik, Commented Aug 4, 2013 at 13:12
If you plan on VBA, you'll need to look into SQL Create to make a table and stackoverflow.com/questions/9564908/… to see how CSVs can be opened. Also, if you plan on manipulating the data after then look into recordsets. — Grant
– Grant, Commented Aug 4, 2013 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. msdn.microsoft.com/en-us/library/office/… — HansUp
– HansUp, Commented Aug 4, 2013 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
– steinbitur, Commented Aug 4, 2013 at 15:47

HansUp · Accepted Answer · 2013-08-04 20:58:46Z

2

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

edited Aug 4, 2013 at 20:58

answered Aug 4, 2013 at 16:57

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HansUp Over a year ago

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.

steinbitur Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related