ACCESS: Macro to export query results to specific tab in Excel spreadsheet, whilst overwriting existing contents.

Hi,

Any criteria in your queries will result in errors using the code provided. Try to do the below and see what happens.

Replace               Set rst = CurrentDb.OpenRecordset(strQRYName)

With                    Set rst = fDAOGenericRst(strQRYName)

Create another new module with:

Function fDAOGenericRst(strSQL As String, _

                    Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _

                    Optional intOptions As DAO.RecordsetOptionEnum, _

                    Optional intLock As DAO.LockTypeEnum, _

                    Optional pdb As DAO.Database) As DAO.Recordset

                                         

    Dim db As Database

    Dim qdf As QueryDef

    Dim rst As DAO.Recordset

    Dim prm As DAO.Parameter

   

    If Not pdb Is Nothing Then

        Set db = pdb

    Else

        Set db = CurrentDb

    End If

   

    On Error Resume Next

    Set qdf = db.QueryDefs(strSQL)

    If Err = 3265 Then

        Set qdf = db.CreateQueryDef("", strSQL)

    End If

    On Error GoTo 0

   

    For Each prm In qdf.Parameters

        prm.Value = Eval(prm.Name)

    Next

   

    If intOptions = 0 And intLock = 0 Then

        Set rst = qdf.OpenRecordset(intType)

    ElseIf intOptions > 0 And intLock = 0 Then

        Set rst = qdf.OpenRecordset(intType, intOptions)

    ElseIf intOptions = 0 And intLock > 0 Then

        Set rst = qdf.OpenRecordset(intType, intLock)

    ElseIf intOptions > 0 And intLock > 0 Then

        Set rst = qdf.OpenRecordset(intType, intOptions, intLock)

    End If

    Set fDAOGenericRst = rst

   

    Set prm = Nothing

    Set rst = Nothing

    Set qdf = Nothing

    Set db = Nothing

   

End Function

Regards,

I know this is a very old thread but I'm not very familiar with VBA and im trying to use this code but when I try to run it I get the error "Microsoft access cannot find the name_ you entered in the expression." Any help would be greatly appreciated. 

Thanks, 

Jermani Hawkins

I know this is an old post, but has anyone gotten this to work in Access 2013/2016?  The process runs, but opens a new workbook as read-only for each call of the module.

thanks.

Jeff

No worries, happy to hear it worked for you. 

Good of luck with your project.

Thank you - it worked!!  The revision to the first module, and then adding the generic function worked beautifully.  As a side note:  I had a macro in the Excel spreadsheet which I no longer needed so I could save it as a regular workbook, rather than a macro-enabled workbook.   By doing so, it eliminated any residual error messages.  Many, many thanks!!

Hi,

Any criteria in your queries will result in errors using the code provided. Try to do the below and see what happens.

Replace               Set rst = CurrentDb.OpenRecordset(strQRYName)

With                    Set rst = fDAOGenericRst(strQRYName)

Create another new module with:

Function fDAOGenericRst(strSQL As String, _

                    Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _

                    Optional intOptions As DAO.RecordsetOptionEnum, _

                    Optional intLock As DAO.LockTypeEnum, _

                    Optional pdb As DAO.Database) As DAO.Recordset

    Dim db As Database

    Dim qdf As QueryDef

    Dim rst As DAO.Recordset

    Dim prm As DAO.Parameter

    If Not pdb Is Nothing Then

        Set db = pdb

    Else

        Set db = CurrentDb

    End If

    On Error Resume Next

    Set qdf = db.QueryDefs(strSQL)

    If Err = 3265 Then

        Set qdf = db.CreateQueryDef("", strSQL)

    End If

    On Error GoTo 0

    For Each prm In qdf.Parameters

        prm.Value = Eval(prm.Name)

    Next

    If intOptions = 0 And intLock = 0 Then

        Set rst = qdf.OpenRecordset(intType)

    ElseIf intOptions > 0 And intLock = 0 Then

        Set rst = qdf.OpenRecordset(intType, intOptions)

    ElseIf intOptions = 0 And intLock > 0 Then

        Set rst = qdf.OpenRecordset(intType, intLock)

    ElseIf intOptions > 0 And intLock > 0 Then

        Set rst = qdf.OpenRecordset(intType, intOptions, intLock)

    End If

    Set fDAOGenericRst = rst

    Set prm = Nothing

    Set rst = Nothing

    Set qdf = Nothing

    Set db = Nothing

End Function

Regards,