Avatar of marku24
marku24
Flag 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?
Microsoft AccessVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
marku24

8/22/2022 - Mon
gowflow

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
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

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
marku24

ASKER
Thank you all for you quick response
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes