Send Excel Data to Access

While working on client projects, I enter all my timesheet data in Excel because:

  • Excel’s usually open, so it’s the easiest program for me to use
  • that’s the way I’ve always done it
  • other important reasons that are long forgotten.

However, I create my invoices in Access, so I have to move the data from Excel to Access, usually at the end of the workday. Last week, JP asked about the code that I use, so here’s how it works.

Filter the Completed Items

On the Excel timesheet there’s a Send to DB button that runs a macro to filter the completed rows to a different worksheet. The Advanced Filter extract range has just the columns that I need for the export, in the order that I want them.

Send Data to Access

Once the data’s on the export sheet, I give it a quick glance, to make sure everything looks okay. Then I click the Send to Access button at the top of that sheet. It runs a macro that opens an ADO connection to the database, inserts the Excel data, and closes the connection. Finally, it clears the export range, to remove the data.

How It Works

For the export code, the connection string and command text string are on the QueryStrings worksheet in the the Excel workbook. I enter the info in the green cells, and the strings for the macro are calculated in the white cells.

This makes it easy to modify the connection strings. For example, if the database moves to a different folder, I just type the new address in the Database cell.

I’m not a connection expert, so perhaps this can be improved, but here’s my code:

‘=======================

Sub SendDataToAccess()

Dim wsQS As Worksheet
Dim sConnect As String
Dim sCommand As String
Dim adoCn As ADODB.Connection

Set wsQS = Worksheets(“QueryStrings”)
Set adoCn = New ADODB.Connection
sConnect = wsQS.Range(“rngConnect”).Value
sCommand = wsQS.Range(“rngCommand”).Value

‘ Get ADO connection to the workbook
adoCn.Open sConnect
‘ Append data from Excel worksheet
adoCn.Execute sCommand

‘ Close the connection to the workbook
adoCn.Close
Set adoCn = Nothing
Worksheets(“CopyToDB”).Range(“DataToExport”).Offset(1, 0).ClearContents
Worksheets(“Proj DB”).Activate

Set wsQS = Nothing

End Sub

‘========================

You may also like...

15 Responses

  1. John McTigue says:

    “What kind of Virgo posts a typo?” (Nov 01)

    “… the strings for the macro are are calculated in the white cells.” (Nov 04)

    That kind of Virgo :)

  2. Oh, I hate when that happens. ;-)
    Thanks John!

  3. Interesting post Debrah. So what would you do with *existing* records?

  4. JP says:

    Nice! Do you find that the data is appended to the bottom of the table? I usually instantiate Access and use the DoCmd.TransferSpreadsheet to import worksheets. How many rows do you usually import and how long does it take?

    –JP

  5. Jan Karel, I sometimes manually delete the completed records in the main table, but usually just remove the end time, and change the date to the next day, so I can record more work on that project.

    JP, the Access table has an AutoNumber field, so the appended records end up at the bottom of the table. There are only 5-6 records most days, and it takes about a second to export them.

  6. Debra,

    I was hoping for something as simple as the INSERT INTO myTable IN SELECT * FROM …

    I pull data from an Access table into Excel, update and add records and write back. The table has an autonumber field which is empty for new records in Excel so I know when to use INSERT or UPDATE.

    I expect the INSERT SQL you wrote above does not work on a filtered sheet so I’d have to split the table into two sheets (new records and existing records) to make that work, right?

  7. Jan Karel, that sounds right.

  8. Callum Renwick says:

    This is great! I’m very new to Access and want to understand fully how this works. What type of query is qryProjDataFromExcel?

  9. @Callum, that is a Select query, with the fields for the data being pushed from Excel.

  10. Ralph says:

    How to link Excel to an accdb Access format. ODBC Driver Manager error.

  11. Joe says:

    Thanks for the lovely code. I am pretty new to this. I struggle to run the code as it stops at adoCn.execute sCommand. I think it’s to do with the “DataToExport” Range. How did you set up that range, so the sql string finds that data set?

    Thanks
    Joe

    • @Joe, I use a macro to filter the data onto the export sheet, and name the range.
      Here’s the part that names the range:

      Sub SetExportRange()
      Dim wsCopy As Worksheet
      Set wsCopy = Worksheets("CopyToDB")
      Dim rngCopy As Range
      
        Set rngCopy = wsCopy.Range("C3").CurrentRegion
        ActiveWorkbook.Names.Add "DataToExport", _
            "=" & wsCopy.Name & "!" _
              & rngCopy.Address
      
      Set wsCopy = Nothing
      Set rngCopy = Nothing
      End Sub
  1. November 10, 2008

    […] Sending Excel Data to MS Access […]

  2. May 12, 2017

    […] you want to know how to send Excel data to Access, this is a great read on our favorite Excel help sites, Contextures out of […]

  3. June 9, 2017

    […] Excel MVP, Debra at Contextures.com did a nice post on this subject. click here to read how to “Send Excel Data to Access”. Her site is our favorite Excel resource on the […]

Leave a Reply to Callum Renwick Cancel reply

Your email address will not be published. Required fields are marked *