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?

Jacques Geday

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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