UtterAccess Discussion Forums > Export Recordset Results To Excel

Full Version: Export Recordset Results To Excel

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

schreinman

Apr 18 2012, 05:45 PM

I am sorry but cannot find the method used to export RecordSet results to Excel. Can someone please let me know the easiest way to export the results of a RecordSet to Excel.

I'd like to:
1. Get rs results
2. Open Excel with the results, including column headers

Start with:

CODE

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = CurrentProject.Connection
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic

    ...

    rs.Open strSQL

    If Not (rs.EOF And rs.BOF) Then
    |                                |
    |         NEED CODE HERE         |
    |                                |
    Else
        MsgBox "No records were found that matched the given search parameters.", 0, "PARTS Database Message"
    End If

    rs.Close
    Set rs = Nothing

Can I use DoCmd.OutputTo or DoCmd.TransferSpreadsheet???

If so, how do I reference the rs results?

Please help; I've been trying to find a solution to this all afternoon and can't find the answer.

THANKS!

Doug Steele

Apr 18 2012, 06:21 PM

Ken Snell lists a couple of options in Ken's Examples for Exporting to EXCEL Workbook Files

schreinman

Apr 18 2012, 06:26 PM

Great resource; thanks Doug (and congrats on your recent post number achievement)!

I came across some similar solutions, but thought there must be a shorter/easier way to do this. Guess I was wrong.

Thanks again!

schreinman

Apr 19 2012, 04:33 PM

After looking over the various options listed on Ken Snell's site I came up with the following for my solution:

CODE

    Dim strVIEW As String
    Dim strSELECT As String
    Dim strFROM As String
    Dim strWHERE As String
    Dim strSQL As String
    Dim strMsg As String

    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Set rs = Me.Recordset
    Set db = CurrentDb

    strSQL = strSELECT & strFROM & strWHERE

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)    '<<<<NEED HELP HERE (how do I properly set and open the db using a SQL string???)

    If Not (rs.EOF And rs.BOF) Then
        blnEXCEL = False
        strPathFileName = "C:\PARTS.xls"

        blnHeaderRow = True

        On Error Resume Next
        Set xlx = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
              Set xlx = CreateObject("Excel.Application")
              blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0

        xlx.Visible = True  'workbook visible when the code is running (set to false if not desired)

        Set xlw = xlx.Workbooks.Add
        Set xls = xlw.Worksheets(1)
        xls.Name = "Search Results"
        Set xlc = xls.Range("A1")
        Set db = CurrentDb()

        ' Write the header row to worksheet
        If blnHeaderRow = True Then
            For lngColumn = 0 To rs.Fields.Count - 1
                xlc.Offset(0, lngColumn).Value = rs.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
        End If

        ' copy the recordset's data to worksheet
        xlc.CopyFromRecordset rs

        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing

        Set xlc = Nothing
        Set xls = Nothing
        xlw.SaveAs strPathFileName
        xlw.Close False
        Set xlw = Nothing
        If blnEXCEL = True Then xlx.Quit
        Set xlx = Nothing
    Else
        MsgBox "No records were found that matched the given search parameters.", 0, "PARTS Database Message"
    End If

    rs.Close
    Set rs = Nothing

Bottom line: what code do I use to open the db (see: "<<<<NEED HELP HERE (how do I properly set and open the db using a SQL string???)" in the code above??? I've tried a handful of options and they all result in error messges.

Thanks!

Doug Steele

Apr 19 2012, 04:41 PM

Not sure I understand your question "how do I properly set and open the db using a SQL string?". The database is already open (that's what the Set db = CurrentDb does, and btw you don't need to the line of code Set rs = Me.Recordset): the SQL string is how you extract specific rows of data from specific tables in that database.

Your original question included reference to strSQL: I'd assume you'd use that same SQL statement in this code as well.

schreinman

Apr 19 2012, 04:57 PM

Something is wrong with the following code:

CODE

    Dim strSQL As String
    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Set rs = Me.Recordset
    Set db = CurrentDb

    strSQL = [Any simple SQL code]

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)   '<<<<<<<<<<<<<<<

Different error messges I am seeing (depending on the SQL code) are:

A simple query of a table resulted in the following:
* You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column. [3622]
Adding 'dbSeeChanges' still results in the same error message

A simple query of a View got me closer (actually opened Excel with the column headers):
* Run-time error '430': / Class does not support Automation or does not support expected interface (highlights the following code: xlc.CopyFromRecordset rs)

I still feel that my OpenRecordset statement needs tweaking but I don't have the ADO knowledge to know what it is...

Thanks Doug!

Doug Steele

Apr 19 2012, 05:23 PM

You never mentioned you were going against a table in SQL Server before!

What are the actual SQL statements you've tried?

No ADO knowledge is required: you're using DAO, not ADO, in that code.

schreinman

Apr 19 2012, 05:47 PM

Doug,

You are absolutely correct; I typed ADO when it should have been DAO.

I am currently looking at reasons for the '430' runtime error:

http://bytes.com/topic/access/answers/2110...rdset-dao-3-6-a

http://www.UtterAccess.com/forum/Run-time-...ed#entry1936758

I'm going to keep experimenting for solutions and will come back later, hopefully with the solution, or possibly with follow-up questions.

Doug Steele

Apr 19 2012, 06:07 PM

What versions of Access and Excel are you using? Is your data in SQL Server? What operating system? What are the SQL statements you've tried unsuccessfully to use?

schreinman

Apr 19 2012, 06:21 PM

All my SQL statements, including those that are built from the Search form are all working now. Not sure what changed aside from simplifying OpenRecordset to:

Set rs = db.OpenRecordset(strSQL)

All the research on the '340' runtime error are discouraging especially since all our machines at our place of employment are locked and configured by third party.
Here's the rundown on my machine:

Office 2007
-Access 2007
-Excel 2007

Windows XP Pro Version 2002 SP3

Data is stored on SQL Server

I used to open a query with the Search results and want to export to Excel instead (better solution, right?). The SQL statement pulls all records from a VIEW on the SQL Server.

Any ideas about the '340' error? I actually came across one of your posts in response to another '340' error, but the solution didn't apply to my case.

Thanks for sticking with me on this one...

Doug Steele

Apr 19 2012, 06:43 PM

I'm confused. It sounds as though it's working: are you just trying to research the error out of curiosity, or are you still encountering errors? And is the error number 340 or 430?

schreinman

Apr 20 2012, 09:50 AM

Sorry for the confusion (including that the error code s/b '430'). I am saying that the recordset is populating as I am getting past the 'If Not (rs.EOF And rs.BOF) Then' code.

Excel then opens with all of the column headers and the tab is renamed to 'Search Results'

But none of the data populates to the spreadsheet beyond that and I get the following message:

"Run-time error '430': / Class does not support Automation or does not support expected interface" associated with the following line of code:

'xlc.CopyFromRecordset rs'

My main question to you is: Is this line correct/complete: 'Set rs = db.OpenRecordset(strSQL)' or should I add Type or Options? I'm not having any problems here but just want to know proper code wrt DAO.

Beyond that I will check with our computer administrators today and see if they can do something about the '430' run-time error, unless anyone has suggestions on how I may be able to correct or get around this error?

Thanks again!!!

CODE

    Dim strVIEW As String
    Dim strSELECT As String
    Dim strFROM As String
    Dim strWHERE As String
    Dim strSQL As String
    Dim strMsg As String

    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Set db = CurrentDb

    ....

    strSQL = strSELECT & Chr(13) & strFROM & Chr(13) & strWHERE & Chr(13) & "ORDER BY " & strVIEW & ".AssetID DESC, " & strVIEW & ".MoveID DESC;"

    Set rs = db.OpenRecordset(strSQL)

    If Not (rs.EOF And rs.BOF) Then
        blnEXCEL = False
        strPathFileName = "C:\PARTS.xls"

        blnHeaderRow = True

        On Error Resume Next
        Set xlx = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
              Set xlx = CreateObject("Excel.Application")
              blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0

        xlx.Visible = True  'workbook visible when the code is running (set to false if not desired)

        Set xlw = xlx.Workbooks.Add
        Set xls = xlw.Worksheets(1)
        xls.Name = "Search Results"
        Set xlc = xls.Range("A1")
'        Set db = CurrentDb()

        ' Write the header row to worksheet
        If blnHeaderRow = True Then
            For lngColumn = 0 To rs.Fields.Count - 1
                xlc.Offset(0, lngColumn).Value = rs.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
        End If

        ' copy the recordset's data to worksheet
        xlc.CopyFromRecordset rs    '<<<<<<<<<<<<<'430' Runtime Error Code!!!

        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing

        Set xlc = Nothing
        Set xls = Nothing
        xlw.SaveAs strPathFileName
        xlw.Close False
        Set xlw = Nothing
        If blnEXCEL = True Then xlx.Quit
        Set xlx = Nothing
    Else
        MsgBox "No records were found that matched the given search parameters.", 0, "PARTS Database Message"
    End If

    rs.Close
    Set rs = Nothing

Doug Steele

Apr 20 2012, 10:27 AM

Well, Ken had a lot of experience working with Access and Excel when he put together his pages, and I know he's got parameters on the OpenRecordset statement in his example, but I've never bothered, and I've never had a problem without them.

Don't know what to suggest. You might see if your computer administrators can do a repair of your Excel installation, just in case something isn't registered correctly. Good luck!

dipetete

Apr 20 2012, 10:45 AM

I didn't read all the post to be honest.
But the way I'd perform that task will be something like this:
-. Get the data from SQL server using a Pass-Through Query.
-. Assign that query to a recordset.
-. Transform the recordset into a matrix (array)
-. Export in a single step the matrix to Excel.
From my tests, this is probably one of the fastests ways of doing this.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.