Solved Write RecordSet To Excel (1 Viewer)

jo15765

Registered User.
Local time
Yesterday, 16:40
Joined
Jun 24, 2011
Messages
116
I am wanting to create an excel workbook and write a record set to it. I have my recordset, and I have created an excel workbook. But I am not sure how to write the recordset to it. This is the code that I have.

Code:
Dim rst As DAO.Recordset
Dim userSQL As String
Dim xlBook As Object
Dim xlApp As Object
    
userSQL = "SELECT Count(username) AS username FROM userdata WHERE username NOT IN (Select username FROM weekoneuserdata);"
Set rst = CurrentDb.OpenRecordset(userSQL)

missingUsers = rst.Fields(0)

rst.Close
Set rst = Nothing

If missingUsers >= 1 Then
    continue = False
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbook.Add
    
    xlBook.SaveAs ("C:\Users\" & Environ("username") & "\desktop\Missing Data.xlsx")
    
    'write the data to the excel file
End If
 

jo15765

Registered User.
Local time
Yesterday, 16:40
Joined
Jun 24, 2011
Messages
116
@June7 - how about the code below? I'll probably convert this to a function where I can pass in the recordset as I'll need to write about 5 different recordsets to Excel, but for now - is this code what you were referencing?

Code:
Dim rst As DAO.Recordset
Dim userSQL As String
Dim xlBook As Object
Dim xlApp As Object
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim oExcelWrSht As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCols As Integer

userSQL = "SELECT Count(username) AS username FROM userdata WHERE username NOT IN (Select username FROM weekoneuserdata);"
Set rst = CurrentDb.OpenRecordset(userSQL)

missingUsers = rst.Fields(0)

rst.Close
Set rst = Nothing

If missingUsers >= 1 Then
    continue = False

    Set oExcel = CreateObject("Excel.Application")
    oExcel.ScreenUpdating = False
    oExcel.Visible = False
    
    Set oExcelWrkBk = oExcel.Workbooks.Add()
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(userSQL, dbOpenSnapshot)
    
    With rs
        If .RecordCount >= 1 Then
            For iCols = 0 To rs.Fields.Count - 1
                oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
            Next
            With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                    oExcelWrSht.Cells(1, rs.Fields.Count))
                .Font.Bold = True
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 1
                .HorizontalAlignment = xlCenter
            End With
            oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                          oExcelWrSht.Cells(1, rs.Fields.Count)).Columns.AutoFit
            oExcelWrSht.Range("A2").CopyFromRecordset rs
            oExcelWrSht.Range("A1").Select
        End If
    End With
    
    oExcelWrkBk.SaveAs ("C:\Users\" & Environ("username") & "\desktop\Missing Locations.xlsx")
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
End If
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:40
Joined
Jul 9, 2003
Messages
13,544
I use the excellent code provided by BTAB Developments to send to, and format data in Excel:-


I believe the code is something to do with Bob Larson, formerly a very active and respected member here on access world forums.

I've used the code in a couple of products and in particular to answer a question from an access World Forum member.

This is a video of the answer I provided:-

Export Query into Separate Excel Sheets - Nifty Access​


You can see more about the question and my modifications to the code on my website here:-

Excel Sheets From Access Table​

 

jo15765

Registered User.
Local time
Yesterday, 16:40
Joined
Jun 24, 2011
Messages
116
@Uncle Gizmo - I'm using a recordset and not a query. I know I could change that recordset into a query rather quickly tho.

The link you provided seems to be a dead link, when I click on it I get an error on the page

1611539270568.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:40
Joined
May 7, 2009
Messages
14,556
5 recordset, write on same Sheet or on different Sheet?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:40
Joined
Feb 19, 2002
Messages
33,677
If you use a querydef, you only need a single line of code to export the recordset using TransferSpreadsheet.
 

jo15765

Registered User.
Local time
Yesterday, 16:40
Joined
Jun 24, 2011
Messages
116
I have this function that I am using to pass in the recordset, filename and the querySQL to export to Excel.

My last remaining issue, is that even when the workbook is saved and closed in Task Manager I still have an instance of Excel left.

What step am I missing to properly clean up Excel?

Code:
Function WriteRecordsetToExcel(rs As Recordset, filename As String, querySQL As String)
        Dim oExcel          As Object
        Dim oExcelWrkBk     As Object
        Dim oExcelWrSht     As Object
        Dim db              As DAO.Database
        Dim iCols           As Integer
        
        Set oExcel = CreateObject("Excel.Application")
        oExcel.ScreenUpdating = False
        oExcel.Visible = False
        
        Set oExcelWrkBk = oExcel.Workbooks.Add()
        Set oExcelWrSht = oExcelWrkBk.Sheets(1)
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset(querySQL, dbOpenSnapshot)
        
        With rs
            If .RecordCount >= 1 Then
                For iCols = 0 To rs.Fields.Count - 1
                    oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
                Next
                With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                        oExcelWrSht.Cells(1, rs.Fields.Count))
                    .Font.Bold = True
                    .Font.ColorIndex = 2
                    .Interior.ColorIndex = 1
                    .HorizontalAlignment = xlCenter
                End With
                oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                              oExcelWrSht.Cells(1, rs.Fields.Count)).Columns.AutoFit
                oExcelWrSht.Range("A2").CopyFromRecordset rs
                oExcelWrSht.Range("A1").Select
            End If
        End With
        
        oExcelWrkBk.SaveAs ("C:\Users\" & Environ("username") & "\desktop\" & filename & ".xlsx")
        
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Set oExcelWrSht = Nothing
        Set oExcelWrkBk = Nothing
        Set oExcel = Nothing
End Function
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
8,900
I am sure that Setting an object in Access to Nothing doesn't implicitly Close it.

So in addition to @June7 advice try closing your Excel worksheet object as well.

oExcelWrkBk.Close
Set oExcelWrkBk = Nothing
 

Isaac

Lifelong Learner
Local time
Yesterday, 16:40
Joined
Mar 14, 2017
Messages
5,886
Try oExcel.Quit.
Just to add, I like to use
oExcel.DisplayAlerts=False

Prior to using .quit..........just too many unknowns. : )

Just me personally
 

Users who are viewing this thread

Top Bottom