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

Anonymous
2011-01-18T20:33:05+00:00

Hi,

I need to write a macro for access, that will export the contents of a query to a specific tab on an excel spreadsheet, whilst overwriting the exiting contents. Is this possible?

I have tried various ways, but it always wants to overwrite the excel file altogether!

Thank you.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

47 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2016-02-08T19:26:22+00:00

    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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-07-28T14:20:21+00:00

    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

    Was this answer helpful?

    0 comments