[TDP 3.6.0.3276] Problems with Excel Macros... Again...

Toad Data Point
1

I had really high hopes for TDP 3.6 - I was finally able to create an automation that successfully used several macros in an Excel 2010 file to arrange and pivot data so that my Senior VP would be happy. Every thing worked on the first run, no crashes, nothing - it seemed like everything was great! The last time I had tried this was with TDP 3.1 and man what a PITA that was!

And then it happened. My VP asked me to add two new tabs - just Month-to-Date versions of what I’d already done for the Daily tabs. Sure, no problem I thought - TDP has been behaving, I’ll just add two new tabs, copy and paste (and rename!) the macros, and build out a new Export Template.

Yeah, right - I should’ve known better. In fact, the problems that I’m having now are almost identical to the ones I had with earlier versions of TDP that were supposedly fixed.

Let’s go over what I’ve already tried:

  1. Editing the existing Export Template to adjust the existing export item to use the new macro names and tabs - nope, TDP crashes either upon accessing the workbook or throws an error about it already being open (even though it’s not).

  2. Making a copy of the Excel file and saving under a new name, and then creating an Export template outside of the automation - nope, TDP either displays that there are no macros at all, or thows this error:

System.Runtime.InteropServices.COMException
Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
Stack Trace:
at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Quest.Toad.ImportExport.Writers.WriterExcelInstance.TryCultureMethod(Type type, String method, Object target, Object[] args)
at Quest.Toad.ImportExport.Writers.WriterExcelInstance.GetMacros(String fileName)
at Quest.Toad.ImportExport.ExportWizard.wizardPageExecuteMacro_ShowFromNext(Object sender, EventArgs e)
at Quest.Toad.Controls.Wizard.WizardPage.OnShowFromNext(Wizard wiz)
at Quest.Toad.Controls.Wizard.Wizard.Next()
at Quest.Toad.Controls.Wizard.Wizard.btnNext_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnClick(EventArgs e)
at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m)
at DevExpress.XtraEditors.BaseControl.WndProc(Message& msg)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

RPC Stack Trace - wonderful…

Any ideas? Excel is already configured to allow access to the VBA Project Model (see point earlier about it working at first) and with minimal security.

OS: Windows 7 Enterprise 32-bit

Office: Office 2010 Professional

TDP: 3.6.0.3276

2

Sounds frustrating. However, I can’t really be sure that what you are experiencing is really related to anything in the past. I think if the issues were solely related to TDP that step 2 would have worked. I would be happy to look into this if you like. You would need to send my the atuomation script, export templates and the exel xlsm file. Somehow the renaming tripped up something.

3

If all else fails you can create an auto_open macro on an .xlsm spreadsheet and launch Excel from the run program automation step with C:\path2Excel\Excel.exe and the path and name of the xlsm file on the argument line. This will launch Excel and open the xlsm file which will then execute the auto_open macro (which can then call any other macro’s needed). Be sure to put an Application.Quit statement at the end of the Auto_Open macro to return control to Toad or it times out. Let me know if you need an example.

4

This did turn out to be an issue. I entered QAT-5175 for this. In this case the user has a single template with two exports. Each of these exports has several before and after macros and Excel is not getting closed. The work around is to separate the exports and do all the init macros up front and all the after execution macros at very end of all exports.