Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-16-2019, 12:14 PM   #1
chineloogbonna
Newly Registered User
 
Join Date: Jul 2018
Posts: 43
Thanks: 10
Thanked 0 Times in 0 Posts
chineloogbonna is on a distinguished road
Transfer Query to Excel Spreadsheet

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.Hyperlin kAddress = ("C:\Users\ogbon\Desktop\NonProfitCRM\CompletedPro jects.xlsx")

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


Thank you.

chineloogbonna is offline   Reply With Quote
Old 09-16-2019, 12:26 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,594
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Transfer Query to Excel Spreadsheet

Hi. Do you want to add to the bottom of the list or to a new worksheet?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-16-2019, 01:17 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Transfer Query to Excel Spreadsheet

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-16-2019, 02:14 PM   #4
chineloogbonna
Newly Registered User
 
Join Date: Jul 2018
Posts: 43
Thanks: 10
Thanked 0 Times in 0 Posts
chineloogbonna is on a distinguished road
Re: Transfer Query to Excel Spreadsheet

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 is offline   Reply With Quote
Old 09-16-2019, 02:14 PM   #5
chineloogbonna
Newly Registered User
 
Join Date: Jul 2018
Posts: 43
Thanks: 10
Thanked 0 Times in 0 Posts
chineloogbonna is on a distinguished road
Re: Transfer Query to Excel Spreadsheet

Button goes on database.
chineloogbonna is offline   Reply With Quote
Old 09-16-2019, 03:24 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,594
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Transfer Query to Excel Spreadsheet

Quote:
Originally Posted by chineloogbonna View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-17-2019, 06:22 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Transfer Query to Excel Spreadsheet

Quote:
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-17-2019, 07:39 AM   #8
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 611
Thanks: 28
Thanked 112 Times in 101 Posts
sxschech is on a distinguished road
Re: Transfer Query to Excel Spreadsheet

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

sxschech is offline   Reply With Quote
Reply

Tags
access , excel , query , transfer , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer spreadsheet (to Excel) stopped working dcavaiani General 7 10-16-2017 04:33 AM
Transfer Spreadsheet to new instance of Excel kvar Modules & VBA 3 12-30-2014 12:55 PM
Transfer Spreadsheet Won't Work if Excel is Already Open HorsepowerX Modules & VBA 2 09-18-2013 12:08 PM
Select criteria to transfer to Excel Spreadsheet Nyneave Modules & VBA 1 07-24-2012 12:18 PM
Transfer Excel Spreadsheet to Access gblack General 6 04-18-2005 05:20 AM




All times are GMT -8. The time now is 09:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World