Link to home
Create AccountLog in
Avatar of marku24
marku24Flag for United States of America

asked on

Export Access Query to an Excel Tab

Hello all - I would like to run a VBA script that transfers (appends) an Access Query to a designated tab in Excel.  Here is the trick i am having a problem with....  The Access query contains a date field called "dateloaded".  If this date already exists in the Excel tab i would like to overwrite the excel data, if it does not exist i would like it to append the data to the tab.  Here are some of my parameters:

AccessQuery#1:  “sqryHires”   to write (append) to Excel tab “Hires”
AccessQuery#2:  “sqryTerms” to write (append) to Excel tab “Terms”
AccessQuery#3:  “sqryTransfers” to write (append) to Excel tab “Transfers”
In the access queries is a single date field:  “DateLoaded”

Is that doable using VBA?
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Can you post something with data I guess a database that has the data and the Query we can run this VBA in Excel and read the data in an ADO/DAO recordset and get it in Excel.

Could you post both an Excel that have your tabs and the sample DB ?
Gowflow
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
For these kind of operations the usual user-friendly exports/imports are not much use.
You have to use Excel automation.
Simply put you will treat Excel as an object and you will have complete control over all its functionality.
In your case you will take your query and you start copying-pasting values from query cell to Excel cell.
For start take a look here :https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Excel-Automation.aspx
So while you are copying you can the value of the destination cell and act accordingly
Avatar of marku24

ASKER

Thank you all for you quick response