Transfer Query to Excel Spreadsheet (1 Viewer)

chineloogbonna

Registered User.
Local time
Yesterday, 20:37
Joined
Jul 30, 2018
Messages
65
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

I’m here to help
Local time
Yesterday, 19:37
Joined
Oct 29, 2018
Messages
10,246
Hi. Do you want to add to the bottom of the list or to a new worksheet?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
28,780
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

Registered User.
Local time
Yesterday, 20:37
Joined
Jul 30, 2018
Messages
65
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
 

theDBguy

I’m here to help
Local time
Yesterday, 19:37
Joined
Oct 29, 2018
Messages
10,246
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

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
28,780
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

Registered User.
Local time
Yesterday, 19:37
Joined
Mar 2, 2010
Messages
661
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.

Code:
'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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom