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 store these email records in a large Email Contact workbook in Excel with each department having its own worksheet tab. When data is updated in access, I want it updated in the excel workbook as well so I can send it off to certain people. My first worksheet in my excel workbook is where I make Counts of all of the records in each department (tab). When updated, the number will change according to the number of records in each tab.
*Each tab's title is identical to my query's name. So I want it to overwrite.
What I have been doing is creating a macro using the TransferSpreadsheet action and exporting the queries to individual Excel spreadsheets in a directory. Then, I would import those individual spreadsheets into the large workbook and making changes that way. I was hoping there is a more efficient way of doing this.
So I have been trying to work this out and I have started to get close to where I want but I am having trouble with something. I have gone away from using macros so I created several modules for each export query. Here is the code:
I would use this same code for each query export, changing the query name in the code.
The problem I am encountering is that rather than overwriting the worksheets in the excel workbook, it would just create a new worksheet tab with the title, "QUERY_NAME_1".
So, it exports the queries to the existing excel workbook but it won't overwrite the same worksheets. Any ideas?
Thanks, much appreciated
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 store these email records in a large Email Contact workbook in Excel with each department having its own worksheet tab. When data is updated in access, I want it updated in the excel workbook as well so I can send it off to certain people. My first worksheet in my excel workbook is where I make Counts of all of the records in each department (tab). When updated, the number will change according to the number of records in each tab.
*Each tab's title is identical to my query's name. So I want it to overwrite.
What I have been doing is creating a macro using the TransferSpreadsheet action and exporting the queries to individual Excel spreadsheets in a directory. Then, I would import those individual spreadsheets into the large workbook and making changes that way. I was hoping there is a more efficient way of doing this.
So I have been trying to work this out and I have started to get close to where I want but I am having trouble with something. I have gone away from using macros so I created several modules for each export query. Here is the code:
Code:
Public Sub ExportQuery1ToExcel()
Const FILE_PATH As String = "[B]C:\Directory\[/B]"
Dim FullPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, , "[B]QUERY_NAME[/B]", strFullPath & "[B]EXCEL WORKBOOK[/B]", False
MsgBox ("Export is complete.")
End Sub
I would use this same code for each query export, changing the query name in the code.
The problem I am encountering is that rather than overwriting the worksheets in the excel workbook, it would just create a new worksheet tab with the title, "QUERY_NAME_1".
So, it exports the queries to the existing excel workbook but it won't overwrite the same worksheets. Any ideas?
Thanks, much appreciated