Exporting Query Results to an existing Excel Workbook

[Dnphm]

Hey,



Here is my situation. I have queries that displays email addresses of our employees in each department. Due to employees leaving from time to time, these queries are updated regularly to accomodate for those changes. I also have a large Email Contact workbook in Excel with each department having its own worksheet tab.

What I have been doing is creating an Export macro in Access and using the Transfer Spreadsheet action to export each query to its own excel spreadsheet into a directory. Then I would paste the updated results into the email contact workbook.

Is there an easier way to export the query results into the corresponding tab in the existing excel workbook, updating/overwriting the old data?

If you need more information, let me know.

Thanks

[jzwp11]

Access-Excel automation can accomplish what you are after but it will take some Visual Basic for Application (VBA) coding. Do you have experience with that?

Out of curiosity, what are you with the data in Excel once you get it into the spreadsheet?

[Dnphm]

Access-Excel automation can accomplish what you are after but it will take some Visual Basic for Application (VBA) coding. Do you have experience with that?

Out of curiosity, what are you with the data in Excel once you get it into the spreadsheet?

I have some experience but will need some guidance.

Once the data is in excel, I make a count of how many records are in each tab (department) on another worksheet. So essentially I am looking to overwrite the old data and have it update the count.

Any help with the coding would be helpful.

[jzwp11]

The count by department can be done with 1 query in Access. Is that the only reason you are exporting to Excel (I would guess not)? I assume that you have some field in your table that designates whether an employee is active or not and thus to have them included in the listing by department or not respectively.

SELECT deptID, Count(emailaddressfield) as CountOfEmployees
FROM youremployeetable
GROUP BY deptID
Having activefield=-1



In terms of coding, you would basically need nested recordsets with an outer loop for the department and an inner loop to go through the employee records. If you could provide the basic table structure for the tables involved, I can rough out the code.