# Automating Complex Excel Reports **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [March 2, 2012, 5:41pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139 "2012-03-02T17:41:16Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 2, 2012, 5:41pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/1 "2012-03-02T17:41:16Z")
Subject stolen shamelessly from Debbie’s post here: [http://www.toadworld.com/Blogs/tabid/67/EntryId/628/Automating-Complex-Excel-Reports-new-in-version-2-7.aspx](http://www.toadworld.com/Blogs/tabid/67/EntryId/628/Automating-Complex-Excel-Reports-new-in-version-2-7.aspx) I’ve set up my template file and macro as well as the pivot table - all works as I think it should. But, here’s the rub - after exporting the data, I need to save the file individually - each day will have it’s own file, instead of constantly overwriting the template, and I’m not sure how to accomplish that. I also need to, if possible, convert the file to something over than .xlsxm as we’ve got a very paranoid Exchange server and it’s likely to delete the attachment. If anyone has any suggestions, I’m all eyes 🙂 ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 2, 2012, 7:27pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/2 "2012-03-02T19:27:16Z")
I am working in the 3.1 Beta code and when I test this you can use the append time stamp and it will give you a unique name. It still needs to be a xlsm file, otherwise Excel can't open it properly. I think this was something we fixed in this code base so may not work in your version. TDA 3.1 will be GA first week of April. Debbie ![Macros.png](https://global.discourse-cdn.com/quest/original/2X/5/542c96cc9e9db3f543c0b3f4a44299b9ddbf308b.png) ---
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 2, 2012, 10:31pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/3 "2012-03-02T22:31:19Z")
Is there anyway to customize the date/time stamp like I can when I define a variable in automation? I’m asking, because I’ve used that option before, and it goes something like this: filenameDDMMYYYY HHMMSS.xlsx The run-together filename is going to drive people nuts. While I personally don’t care, if it drives them nuts, they drive me nuts 🙂 And what part did you think was fixed - the requirement for the xlsm file, or the date/time stamp? ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 5, 2012, 2:12pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/4 "2012-03-05T14:12:16Z")
The datetime stamp is fixed in the next release. To accomplish the date format you want, set an automation variable and then use the automation variable in your filename. To do so, append it to the file name but dereference the variable name with the '#' symbols. See screenshot. Debbie ![stamp.png](https://global.discourse-cdn.com/quest/original/2X/5/5b4d5ede63cd58d0fa083e3aad99e70a36658c6f.png) ---
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 5, 2012, 2:14pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/5 "2012-03-05T14:14:20Z")
It looks like some things have changed with the Export Wizard activity between 2.7 (when your guide is written) and 3.0 (what I’m running). Specifically, the Export Wizard activity wants me to create a template if I’m not using an existing one (obviously I’m not), and it wants me to export _something_ - but your guide has me using the Select to File activity to export my data, not the Export Wizard - so I’m not sure what to tell it to export. Additionally, after selecting my .xlsm file, and getting to the Before Export/After Export macro options - it doesn’t show the macro in the Excel file. I re-opened the Excel file to make sure it’s still there, and it is, but TDA can’t see it for some reason. Any ideas? ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 5, 2012, 8:27pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/6 "2012-03-05T20:27:25Z")
In the example I was showing how to use both the expert wizard and select to file activities with xlsm files. In order to fire a macro you have to use the export wizard activity. This activity always uses a saved template so you will need to go throught the wizard, set when to fire the macros and save to disk. If you only want to fire a macro then write a query that does not return any rows. RE: not seeing the macros, I am not sure what is occuring there. I would try our 3.1 Beta. If you still have the issue in our Beta please send me a copy of your file and we can take a look. Debbie ---
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 7, 2012, 10:43am UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/7 "2012-03-07T10:43:03Z")
I understand - I really only need to use the Export Wizard then, as it accomplishes both tasks. I used the 3.1 Beta, and I’m getting an error message when I get to the Macro select (3.0 doesn’t display any error messages when it doesn’t show the macros). The error is: “Programmatic access to Visual Basic Project is not trusted. Please enable this feature in Excel in order to allow the execution of macros.” In Excel I’ve enabled all macros, as you outlined in your blog post. Is there something else I need to enable? Debbie Peabody wrote: In the example I was showing how to use both the expert wizard and select to file activities with xlsm files. In order to fire a macro you have to use the export wizard activity. This activity always uses a saved template so you will need to go throught the wizard, set when to fire the macros and save to disk. If you only want to fire a macro then write a query that does not return any rows. RE: not seeing the macros, I am not sure what is occuring there. I would try our 3.1 Beta. If you still have the issue in our Beta please send me a copy of your file and we can take a look. Debbie ---
**Author:** ![N.B](https://avatars.discourse-cdn.com/v4/letter/n/bc8723/32.png) [@N.B](https://forums.toadworld.com/u/N.B)\ **Post date:** [March 7, 2012, 10:55am UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/8 "2012-03-07T10:55:46Z")
I figured out how to enable the Programmatic access. 3.1 sees the Macro, 3.0 still doesn’t for some reason (even after enabling the Programmatic access in Excel). This is somewhat frustrating 🙂 Is 3.1 mature enough to put into production for automated reporting? I know it’s still a beta license and I probably won’t be able to swap over to the Enterprise license until GA in the first week of April, but I don’t have that long (our Microstrategy DW goes away 4/1) and I’m being pushed to automate as much as possible. N.B. wrote: I understand - I really only need to use the Export Wizard then, as it accomplishes both tasks. I used the 3.1 Beta, and I’m getting an error message when I get to the Macro select (3.0 doesn’t display any error messages when it doesn’t show the macros). The error is: “Programmatic access to Visual Basic Project is not trusted. Please enable this feature in Excel in order to allow the execution of macros.” In Excel I’ve enabled all macros, as you outlined in your blog post. Is there something else I need to enable? Debbie Peabody wrote: In the example I was showing how to use both the expert wizard and select to file activities with xlsm files. In order to fire a macro you have to use the export wizard activity. This activity always uses a saved template so you will need to go throught the wizard, set when to fire the macros and save to disk. If you only want to fire a macro then write a query that does not return any rows. RE: not seeing the macros, I am not sure what is occuring there. I would try our 3.1 Beta. If you still have the issue in our Beta please send me a copy of your file and we can take a look. Debbie ---
**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\ **Post date:** [March 7, 2012, 9:42pm UTC](https://forums.toadworld.com/t/automating-complex-excel-reports/7139/9 "2012-03-07T21:42:06Z")
Yes, it is stable to use. In fact, we are only making changes to the code up until the 15th and there are no outstanding changes for import or export. Debbie