Transfer Query to Excel Spreadsheet

[chineloogbonna]

Hi,
I am hoping to get some code for a button click to transfer query results to an existing worksheet.

So far I have the below code that opens the Excel Workbook and transfers the query to the worksheet. However, my problem is that it over rights the existing excel data previously added.

Is there a way to transfer my query data to the worksheet on the next empty row so my existing data does not get overwritten.

'Open Workbook Completed Projects
Forms!CompletedDonor!BTN_ArchiveCompleted.HyperlinkAddress = ("C:\Users\ogbon\Desktop\NonProfitCRM\CompletedProjects.xlsx")

'transfers data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Over60dayDonorList_Query", "C:\Users\ogbon\Desktop\NonProfitCRM\CompletedProjects.xlsx", , [Range("A")]


Thank you.

 

[theDBguy]

Hi. Do you want to add to the bottom of the list or to a new worksheet?

 

[chineloogbonna]

Access Database is the master. However, I have a query that only pulls certain records for a specific time frame from a table. I am not sure how to export the query to excel so that it goes to the next empty line each time?

Thanks

 

[chineloogbonna]

Button goes on database.

 

[theDBguy]

Access Database is the master. However, I have a query that only pulls certain records for a specific time frame from a table. I am not sure how to export the query to excel so that it goes to the next empty line each time?

Thanks

If you’re trying to maintain a single worksheet, this would take a few techniques. You’ll have to automate Excel, open the worksheet, and use a recordset for your query. You then need to find the last row with data and then paste your recordset there.

 

[sxschech]

This isn't complete code (just some relevant lines I copied from one of my files) to give you an idea about finding last row and copy from recordset. Using copyfrom recordset allows you to specify where to paste the data. In the example, it is pasting in col A 7 rows below the last row in the excel file.

'http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
    Dim rs As DAO.Recordset
    Dim objapp As Object
    Dim wb As Object
    Dim ws As Object
    Dim rng As Object
    Dim lastCol As Long
    Dim lastrow As Long
    lastrow = .Range("A1").currentregion.rows.Count
    lastCol = .Range("A1").currentregion.Columns.Count
    
     Set objapp = CreateObject("Excel.Application")     'Excel Not Running
        
        objapp.Visible = True
        Set wb = objapp.Workbooks.Open(filename, True, False)
        
        stCustomize = FileNameNoExt(Customize)
    Set rs = CurrentDb.OpenRecordset("queryname")
        For Each ws In wb.Worksheets
            'Debug.Print ws.Name
            With ws
                .Activate
                Set rs = CurrentDb.OpenRecordset(qry)
                rs.MoveLast
                rs.MoveFirst
    
     .Range("A" & lastrow + 7).copyfromrecordset rs