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?
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
Last Comment
marku24
ASKER CERTIFIED SOLUTION
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.
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
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
ASKER
Thank you all for you quick response
Could you post both an Excel that have your tabs and the sample DB ?
Gowflow