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?

 

[Pat Hartman]

There seems to be some confusion regarding which file is the "master". Is it the Access database (it should be) or the Excel spreadsheet?

If the master is the Access database, just export all the data for that sheet. If Excel is the master, it's time to rethink how you are doing things.

 

[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.

 

[Pat Hartman]

I am not sure how to export the query to excel so that it goes to the next empty line each time?

What is on the other lines that you don't want to overlay? Why not just export ALL the rows? If Access is the master, then Access can replace the entire contents with up to date data.

Since TransferSpreadsheet replaces data if the sheet exists, if you want to append data, then you will need to have to populate the spreadsheet using OLE. Open the workbook, find the last row with data and then use a VBA code loop to read the query that selects the data you want to append and using .addNew add each row.

 

[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