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
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
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
Could you post both an Excel that have your tabs and the sample DB ?
Gowflow