Toad World® Forums

XML issue: undeclared variable error in automation script

I just modified an existing automation script in TDP3.7 - there is a select file task that creates a new Excel file and an email activity. I added a second select to file activity and created a master Excel file with 2 tabs. I am exporting each result set from the 2 queries to a separate tab. I have many scripts just like this set up, but today all of a sudden I get this error:

Export_1 - ‘xr2’ is an undeclared prefix. Line 1, position 403.

I looked in the XML and I don’t see “xr2” anywhere. I set the script up again to send each query result to a new file and it worked fine. I’m not sure why this error popped up. I can send the automation script tomorrow.

yes, please post the script and erroring automation log file. I don’t recognize the “undeclared prefix” message. That does not appear in our code.

Interesting wrinkle in this. The error occurs when I create an Excel file first, and then point the exports to this file. If, in my export file window, I select overwrite, thus forcing TDP to recreate the file, then my exports work. Most of the scripts I have created in the past involved Excel files that I first created because I wanted to add a bunch of pivot tables. I would then use select to file tasks for all my exports. These scripts are still working fine with the files I created, which leads me to believe that maybe there was some update to Excel, and when TDP creates an Excel file, it is using a slightly older version?

I created a script, and I can recreate an Excel file from scratch so that it causes the error. Based on my testing, the first script runs and exports to the Excel file, but then the file becomes corrupt and causes the second export to fail.

I attached the script with embedded sql and the Excel file, but I didn’t run the script yet in order to corrupt the Excel file. Please let me know if I should corrupt the file and repost.
test_pk_workorder_shortage_rpt.zip (11.6 KB)

I can reproduce the exact error with your excel file in TDP 3.7. But in current GA version TDP 4.2.1 i do not get this error.

i also created a new master file similar to yours and it does not error with TDP 3.7. I suspect the version of Excel or how the master file is made. Here is error I see when opening file. I attached the file I used. Try using it and just clear the data prior to exporting to the named sheet.
Test Workorder Shortage Report.xlsx (90.5 KB)

I am testing TDP 4.2.1 and I do not get this error anymore. Since I am not ready to upgrade all my automation scripts yet, I am able to modify and save my Excel master files using Excel 2010 on my dev machine. Thanks for your help with this.