Hi Forum,
I didn’t find anything on this in a search, but sorry if this has already been asked.
I think the option to run macros from Toad is great! It is definitely helping me miss Hyperion/Brio just a little bit less…but is there a way to save the final report as .xlsx instead of .xlsm? We have some users and processes that require .xlsx format.
Thank you,
Julie
Unfortunately, since only xlsm file contains macros which we can run after the file is saved, the file cannot be saved as xlsx format. You need to explicitly save as xlsx format it in Exel afer the file is created.
Hi Kiki,
When you say ‘explicitly save’, do you mean manually save in Excel? Or is there a way to do this in Toad?
Thank you,
Julie
From: Kiki [mailto:bounce-Kiki@toadworld.com]
Sent: Tuesday, February 16, 2016 2:50 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Automation with Excel - using macro but saving file as .xlsx
RE: Automation with Excel - using macro but saving file as .xlsx
Reply by Kiki
Unfortunately, since only xlsm file contains macros which we can run after the file is saved, the file cannot be saved as xlsx format. To do that, you need to explicitly save as xlsx format it in Exel.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
This e-mail message, including any attachments, is for the sole use of the person to whom it has been sent, and may contain information that is confidential or legally protected. If you are not the intended recipient or have received this message in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Please notify the sender immediately by return e-mail and permanently delete this message and any attachments. Gartner makes no warranty that this e-mail is error or virus free.
Kiki is there a command line call to excel that she can use to save xlsm as xlsx. That way she would preserve the macro file and still deploy the xlsx file.
Julie,
Maybe a workaround would be to modify the macro - once it finishes then save itself as .xlsx… something like this:
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Mid(ThisWorkbook.Name, 1, InStrRev(ThisWorkbook.Name, ".") - 1), xlOpenXMLWorkbook
Then Toad can pick the .xlsx file up once it’s created… Also, please note that both .xls and .xlsb also support macros… .xlsm is 97-2003 excel format which allows you only 65k rows while xlsb is binary format which has 1 milion+ rows and the result file size is smaller…
Martin’s solution is the easiest. I like my macro files to stay clean and perform manipulations on an xlsx file. Long before Toad could launch macros I would write out to an Excel or csv file then use the run program step to launch Excel with an Excel file (xlsm) in the argument line that has an Auto_Open macro in it (runs when opened). That macro would then use the Excel or csv file that was created in the prior step.
I was able to create a solution for you - Attached is an automation script which runs excel macro from .xlsb (excel binary format) (made in Toad Data Point 3.8.0.575)
Just open the automation script (.tas) in Toad and hit F5 to execute:
Please note that executing the cmd.exe will open a black window for the time of macro execution ---- if i recall correctly there is a switch for cmd to somehow hide itself and let the vbs file execute in the background… but not sure about that…
Also note that should the vbs file encounter any unhandled errors the excel application will stay invisible open in the task manager (use “On error goto ErrHandler” to deal with this situation)
Please do let me know if this satisfies your needs (=
ToadAutomationScript_RunExcelMacro.zip (10.3 KB)
Thank you for the suggestions. I haven’t had time to test this because I don’t want to edit my original macro and I don’t see a way to choose a new macro. I think I have to recreate the whole .tas file maybe?
I will update as soon as I get it to process.
Thanks again,
Julie
From: Martin.Holkovic [mailto:bounce-MartinHolkovic@toadworld.com]
Sent: Wednesday, February 17, 2016 12:33 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Automation with Excel - using macro but saving file as .xlsx
RE: Automation with Excel - using macro but saving file as .xlsx
Reply by Martin.Holkovic
Julie,
Maybe a workaround would be to create a macro to do something like this:
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Mid(ThisWorkbook.Name, 1, InStrRev(ThisWorkbook.Name, ".") - 1), xlOpenXMLWorkbook
This macro simply saves the excel file where the macro runs as .xlsx file format in the current directory.
Also, please note that both .xls and .xlsb also support macros… .xlsm is 97-2003 excel format which allows you only 65k rows while xlsb is binary format which has 1 milion+ rows and the result file size is smaller…
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
This e-mail message, including any attachments, is for the sole use of the person to whom it has been sent, and may contain information that is confidential or legally protected. If you are not the intended recipient or have received this message in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Please notify the sender immediately by return e-mail and permanently delete this message and any attachments. Gartner makes no warranty that this e-mail is error or virus free.
Sure please let us know how it goes.
The solution i’ve posted does not need editing - you just open the .tas file and run in Toad… i believe this is the part you are trying to achieve so i’d suggest you to review the content of it in order to understand how you can use it in your own solution.
Let me know if you need help with editing the macro - you can send the .tas and .xlsm files to my email address (see below) so i can review and edit it for you.
my email: