Monday, May 23, 2016

How do I export Access data to Excel - Part 3

There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

In Part 1, I discussed various manual methods. In Part 2, I discussed TranferSpreadsheet. This time I’ll look at Office Automation, which gives you maximum control over automated exports.

    Office Automation

    Office automation allows a developer to open and manipulate other MS Office applications in VBA code.  It allows the Access developer to use the specialized functions of those other programs, functions that would be hard to create natively.  I’ve used automation to create documents in Word, charts in Excel, and presentations in PowerPoint.  Sometimes all three from the same Access application.

    Here are a few examples:

  • AutomatingWordFromAccess.mdb ( advanced)
  • ExportToExcelCharts.mdb ( intermediate )
  • AutomatingPowerpoint.mdb ( intermediate )

    In this case, I want to export data from an Access database into specific formatted cells in an Excel spreadsheet. To do this, I need to use the Data Access Object model built into all Office apps.

    Excel Object Library (VBA)

    Through VBA, a developer can open and manipulate other Office applications. This is not limited to MS Office applications, but that’s another discussion. Each Office application has its own Object Model, each with its own Collections, Methods, and Properties. Once I reference that model in Access, I can use VBA to use that application’s Collections, Methods and Properties. Since I want to automate Excel, I need to use the Excel Object Library.

    Setting References

    In order to use an external object model, I need to first set a Reference to it. To do that, I need to open the Visual Basic Editor (VBE), which can be opened in Access on the Database Tools tab and choosing Visual Basic.

    image

    Once the VBE is open, choose Tools > References

    image

    The Reference List will look like this:

    image

    The first three checked references will automatically be there in a new Access database.  The fourth line WON’T.  You need to scroll down the list (to the “M”s) to choose Excel.  Check the box and click OK. Reopen the References, and it should look something like the above.

    Depending on your Office version, the references may be named differently, there may be additional references, or they may be in a different order. It’s not important most of the time.  The important thing is to find and check the Excel reference.

    Data Access Objects (DAO)

    DAO is the object model used to open and manipulate databases in VBA.  DAO is not part of Access. Technically, it’s the object model for the Jet database engine, and it can be referenced in any Office application to manipulate data.  However, since Access uses the Jet database engine as its default, a reference is set to it whenever an Access database is created. Since I need to take data from an Access database and send it to an Excel worksheet, I need both object models.

    Two Methods

    There are two ways of using Office Automation that I want to discuss:

    1. Using a Do…While loop to PUSH data from Access to Excel
    2. Using the CopyFromRecordset to PULL data into Excel from Access

    Regardless of which method I use, there is a basic framework of creating and instantiating objects I need to build.  This framework looks like this:

    'First I need to define some variables. Ordinarily, I would define all my variables here, but in this case, I’m going to define them as I go for clarity.
    Dim RowVal As Integer
    Dim ColVal As Integer
    Dim conPath As String

    '  Find the path where the database resides
        conPath = CurrentProject.Path

    ‘  Define and Open Database Object
        Dim db As DAO.Database
      Set db = CurrentDb


    '  Define and Open Recordset Object based on a query in my database called “TheDataQuery”. I could, of course be any saved query.
        Dim rs As DAO.Recordset
        Set rs = db.OpenRecordset("TheDataQuery")

    '  Define and Open Excel as an Excel Object. This is just opening Excel itself without a workbook.
        Dim objXLApp As Excel.Application
        Set objXLApp = Excel.Application


    ' Create and Open an Excel "Workbook" object. The workbook (that is Excel file) must exist in the place given in the .Open statement.
            Dim objXLBook As Excel.Workbook
        Set objXLBook = objXLApp.Workbooks.Open _
            (conPath & "\Export_ForNext.xlsx")

    ' Create and Open a "Worksheet" object. The tab, in this case named TheDataSheet, must also already exist.
        Dim objResultsSheet As Excel.Worksheet
        Set objResultsSheet = objXLBook.Worksheets("TheDataSheet")


    ' Create a "Range" object.  This is a named range in Excel which defines a certain group of cells. The actual values in the Range argument depends on the particular spreadsheet, but it should be at least as wide, and longer than the anticipated recordset.
            Dim rng As Excel.Range
        Set rng = objResultsSheet.Range("A2:F45")

    ' ****EXPORT CODE GOES HERE ****

    ' Save and close spreadsheet
            objXLBook.Save
        objXLBook.Close
           

        ' Lastly, I’ll destroy all the objects I created.
        Set db = Nothing
        Set rs = Nothing
        Set objResultsSheet = Nothing
        Set objXLBook = Nothing
        Set objXLApp = Nothing

    So far, I haven’t actually done anything useful. I’ve only created and destroyed a bunch of objects. The real work happens in the EXPORT CODE GOES HERE section. The code will vary depending on the method you use and the whether I want to over-write the data or append the data to the end of the existing data.

    Do…While Method

    I’ll start with the Do…While method.  This method “pushes” the data from Access into Excel. It does this by opening and walking through a recordset in Access and setting specified cells in the Excel sheet.

    Overwrite Data
    (Enter the following into the  ****EXPORT CODE GOES HERE ****)

    '  **************************************
    '  Set beginning row and column numbers
        RowVal = 2
        ColVal = 1

    '  Delete any existing data
        rng.ClearContents

    '  Write data from Access query to Spreadsheet
        Do While Not rs.EOF
            objResultsSheet.Range(Cells(RowVal, ColVal), _
                Cells(RowVal, ColVal)) = rs!phys_id
            objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
                Cells(RowVal, ColVal + 1)) = rs!spec_code
            objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
                Cells(RowVal, ColVal + 2)) = rs!Export_Date
            objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
                Cells(RowVal, ColVal + 3)) = rs!Drug1
            objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
                Cells(RowVal, ColVal + 4)) = rs!Drug2
            objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
                Cells(RowVal, ColVal + 5)) = rs!Drug3
            RowVal = RowVal + 1
            rs.MoveNext
        Loop
    '  **************************************

    Append Data
    (Enter the following into the  ****EXPORT CODE GOES HERE ****)

    '  **************************************
    '  Set beginning row and column numbers
       RowVal = 2
      ColVal = 1

    ' Find the last row of data in the Excel sheet
      Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
            RowVal = RowVal + 1

        Loop

    ' Write data from Access query to Spreadsheet
        Do While Not rs.EOF
            objResultsSheet.Range(Cells(RowVal, ColVal), _
                Cells(RowVal, ColVal)) = rs!phys_id
            objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
                Cells(RowVal, ColVal + 1)) = rs!spec_code
            objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
                Cells(RowVal, ColVal + 2)) = rs!Export_Date
            objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
                Cells(RowVal, ColVal + 3)) = rs!Drug1
            objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
                Cells(RowVal, ColVal + 4)) = rs!Drug2
            objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
                Cells(RowVal, ColVal + 5)) = rs!Drug3
            RowVal = RowVal + 1
            rs.MoveNext
       Loop
    '  **************************************

    CopyFromRecordSet Method

      The CopyFromRecordSet method is not an Access function.  It’s actually a method built into Excel. However, because I’m using Office Automation, I can use this Excel method to pull data from Access to Excel.  Using this method, you don’t need to worry about row position or column position.

      Recall that above, I defined a recordset object based on the query “TheDataQuery”,
          Set rs = db.OpenRecordset("TheDataQuery")
      and I also created a range object
          Set rng = objResultsSheet.Range("A2:F45")

      The CopyFromRecordSet method allows me to drop the defined recordset (rs) into the defined range (rng).  It requires an argument for maximum rows and columns (45 and 10 below). I usually just define an area bigger than I could possibly need.

      Overwrite Data
      (Enter the following into the  ****EXPORT CODE GOES HERE ****)

      '  **************************************
      '  Set beginning row and column numbers
          RowVal = 45
          ColVal = 10

      ' Delete any existing data
          rng.ClearContents
         
      ' Write data from Recordset to Spreadsheet range
          Call rng.CopyFromRecordset(rs, RowVal, ColVal)
      '  **************************************


      Append Data
      (Enter the following into the  ****EXPORT CODE GOES HERE ****)

      '  **************************************
      '  Set beginning row and column numbers
          RowVal = 1
          ColVal = 6

         
      ' Find the last row of data
          Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
              RowVal = RowVal + 1
          Loop

             
      ' Create a "Range" object
          Set rng = objResultsSheet.Range(Cells(RowVal, 1), Cells(RowVal, 1))
         
      ' Write data from Recordset to Spreadsheet range
          Call rng.CopyFromRecordset(rs, RowVal, ColVal)
      '  **************************************

      You can find this code all pulled together in a sample database from my website:

      Download Sample:

      ExportToExcel_OfficeAutomation

    • No comments: