Export to Excel not running Macro

Toad Data Point
1

Hello,

I am running a SQL statement against an Oracle DB and then exporting to Excel (.xlsm). I have selected the option to have a macro run after the export however it is not working and when I open my exported excel the macro is no longer there.

I have “Enable all macros” and “Trust access to the VBA project object model” selected in the .xlsm file. In the Export Wizard I do not have “Overwrite” selected.

2

Sounds like you have all of the correct items selected. Can you email me your automation file and supporting files? I can take a look.

Debbie
dpeabody@quest.com

3

Thanks for the files. There is a known issue regarding the use of Suffix with a macro file. If you take off the suffix the macro will fire. If you still want a Suffix, use the Copy file append suffix activity.

Debbie

P.S. For Oracle connections don’t use ODBC. Use the Native Oracle provider. You will get better processing out of export/import as it uses array processing.

4

Another way to get around this is to create a batch file that starts the excel file, make it an autorun macro so when the file opens, the macro runs. Add the batch to the automation file.

5

Hey, that is a clever idea

Debbie

6

Hello,

I had this working great in 2.7 but downloaded the 3.0 beta and now can’t see my macro - any idea?

7

I just ran the scripts you sent me in 3.0 and they work fine for me. Tell me more about not seeing the macro? Does your automation script run ok? Do you not see the macro when going through the Export wizard? Do the macros show in the VBA editor of Excel?

Debbie

P.S. One time when I was running the export wizard to view the macros, Excel had a problem. Check your settings in Excel and make sure you have the security turned off so that macros can be used. Maybe something got changed during a Microsoft update.

8

Don’t receive any errors. The scripts run fine. I just no longer see my Macro when going through the export. I checked my excel and it does not seem that anything has change.

I did change the email recipients and the placement of the finished file. For some reason whenever I make one change I seem to have to go back and redo changes i have made before. I am wondering if something was changed I am unaware of - although I spent the better part of today reviewing.

I will send you a copy of my new scripts.

9

I tried adapting your new xlsm file and export template and now Excel wouldn’t run any files with macros. Something is going on here but I am not sure if it is TDA or Excel.
Do you see the macro page at all in the export wizard or do you see the macro page but no macros are listed?

What about Excel. Can you see the macros in the macro list? Can you edit them?

Debbie
P.S. I entered CR90958 on this but we will need to add more info on what is occuring.

10

I do see the macro page in the export wizard - but its blank.

In excel I do see the macro and I can edit them. I can also run the macro.

I am going to look again tomorrow and see if I can find some setting that is off. Other then the security in Excel and not using ‘overwrite’ in export, is there anything else that needs to be checked/unchecked, etc?

11

No. You have the options that need to be set. I am not sure what else to suggest. Do you another pc to try this on? Just to see how consistant the issue is?

Debbie

12

I seem to be able to see my macros now.

Thanks for your help!

13

I am glad this is working for you now.

Debbie