Adding Excel 2003 Reference File to post export script

[Grant]

I was wondering if how I could add the reference library for Microsft Excel 2003 so that I can call Excel library functions from the script editor? I am trying to auto-execute an excel macro (vbscript) that's embedded in the excel file I am exporting the data to with Datapump. Thanks

[ November 01, 2004, 01:02 PM: Message edited by: Datapump User ]

[Darren]

Hey there Datapump User!

Just trying to clarify this a bit. Are you trying to auto-execute the VBscript macro from Excel AFTER the MDP export, or trying to use Monarch to export the actual macro script itself?

Hopefully I can help you out. Maybe with a few more details I can! smile.gif

[Gareth Horton]

Hi,

I think all you need to do is add the namespace to your script by going into the references and imports tab in the script editor and adding the following namespace:

Microsoft.Office.Interop

I believe that Office 2003 will register all the necessary assemblies in the GAC, so you should not have to add the assemblies manually in the Data Pump references dialog.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/stagsdk/html/stconPIAs.asp

If they are not installed correctly or you have any pathing issues, then you could add them in manually from the <WINDIR>Assembly directory.

The following articles may also be useful

http://support.microsoft.com/?kbid=317109

http://www.devcity.net/forums/faq.asp?fid=15&cat=Interop

http://www.xtremevbtalk.com/showthread.php?t=160433

There are also interop assemblies available for Office XP

http://support.microsoft.com/kb/328912

It appears to be problematic using office automation from 2000 and earlier with .NET.

The alternative there would be to just execute a VBScript.

Gareth


Originally posted by Datapump User:
I was wondering if how I could add the reference library for Microsft Excel 2003 so that I can call Excel library functions from the script editor? I am trying to auto-execute an excel macro (vbscript) that's embedded in the excel file I am exporting the data to with Datapump. Thanks

[Grant]

To clarify exactly what I am trying to accomplish:
- I recieve a text file form a payroll company
- Using Monarch is export a summary of the data to a worksheet in a pre-exisiting wookbook that contains a VBA macro that formats the data from the export in such a way that it is readable by our financial system.
- I would like to automate the process so an end user doesn't have to open the excel spreadsheet with the new data and press the button in excel that executes the macro to reformat the data.

Any help you could provide would be greatly appreciated. This would be a big break through, as I have several processes that follwo this logic with the excel intervention. I deadlly I would like to eliminate the Excel step altogehter and have Datapump format the text but that would seem like a lot of programming to handle the text manipulation.

[Grant]

Originally posted by Gareth Horton:
Hi,

I think all you need to do is add the namespace to your script by going into the references and imports tab in the script editor and adding the following namespace:

Microsoft.Office.Interop

I believe that Office 2003 will register all the necessary assemblies in the GAC, so you should not have to add the assemblies manually in the Data Pump references dialog.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/stagsdk/html/stconPIAs.asp

If they are not installed correctly or you have any pathing issues, then you could add them in manually from the <WINDIR>Assembly directory.

The following articles may also be useful

http://support.microsoft.com/?kbid=317109

http://www.devcity.net/forums/faq.asp?fid=15&cat=Interop

http://www.xtremevbtalk.com/showthread.php?t=160433

There are also interop assemblies available for Office XP

http://support.microsoft.com/kb/328912

It appears to be problematic using office automation from 2000 and earlier with .NET.

The alternative there would be to just execute a VBScript.

Gareth


[/size]quote:[/size]Originally posted by Datapump User:
I was wondering if how I could add the reference library for Microsft Excel 2003 so that I can call Excel library functions from the script editor? I am trying to auto-execute an excel macro (vbscript) that's embedded in the excel file I am exporting the data to with Datapump. Thanks [/size]Thanks but the Microsoft.Office.Interop didn't work, the compiler in Datapump gave an error on type .Interop.

Any more suggestions would be greatly appreciated, this is a big stumbling block for me and the productivity I could achieve with Datapump.

Thanks

[Grant Perkins]

Originally posted by Datapump User:
Ideally I would like to eliminate the Excel step altogether and have Datapump format the text but that would seem like a lot of programming to handle the text manipulation. How far have you investigated Monarch's ability to to have a shot at this as part of the initial extract that goes to Excel?

Do you have the possibility of posting a sample of something like the input file and what it needs to be like when prepared for insertion into your target application?

I (and maybe others) would be happy to have a look and make some suggestions or at least offer an opinion about the potential for elimiating teh Excel step.

Grant

[Darren]

Datapump User,

What version of Monarch and MDP are you using? I agree with Grant. Please post a sample and I would be happy to play around with the sample and attempt to do what you are trying to accomplish...

Darren.

Originally posted by Grant Perkins:
[/size]quote:[/size]Originally posted by Datapump User:
Ideally I would like to eliminate the Excel step altogether and have Datapump format the text but that would seem like a lot of programming to handle the text manipulation. How far have you investigated Monarch's ability to to have a shot at this as part of the initial extract that goes to Excel?

Do you have the possibility of posting a sample of something like the input file and what it needs to be like when prepared for insertion into your target application?

I (and maybe others) would be happy to have a look and make some suggestions or at least offer an opinion about the potential for elimiating teh Excel step.

Grant [/size]

[Gareth Horton]

Hi

I got the interop working fine.

You might want to try another approach.

First, find out where the physical image of where the Microsoft.Office.Interop.Excel assembly is by going into the .NET Configuration Wizard, going into the GAC (Assembly Cache) and taking a look at the Properties of the assembly. You should have a codebase value which will give you the physical path to the assembly.

You may already know where the assembly resides.

Now go to the references and imports tab in the script editor and add a reference to that dll, (the top box) by selecting the file and give the name in the first box as Microsoft.Office.Interop.Excel

Get rid of any imports you were trying for excel from the lower box.

Now, when you write the script, declare the Excel object in this way, using the full namespace.

The following code should then work.

dim ExcelApp as new microsoft.office.interop.excel.application

ExcelApp.Visible=true

thread.sleep(5000)

ExcelApp.quit

Excelapp = nothing


NOTE*** This is only test code, which you can use in the script editor test compile. As Datapump runs as a .NET service, it will never show a UI, so be sure to close down the Excel object, or the process will run forever. You need to add the System.Threading namespace to your imports for the sleep command to work***

Gareth

Originally posted by Datapump User:
[/size]quote:[/size]Originally posted by Gareth Horton:
Hi,

I think all you need to do is add the namespace to your script by going into the references and imports tab in the script editor and adding the following namespace:

Microsoft.Office.Interop

I believe that Office 2003 will register all the necessary assemblies in the GAC, so you should not have to add the assemblies manually in the Data Pump references dialog.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/stagsdk/html/stconPIAs.asp

If they are not installed correctly or you have any pathing issues, then you could add them in manually from the <WINDIR>Assembly directory.

The following articles may also be useful

http://support.microsoft.com/?kbid=317109

http://www.devcity.net/forums/faq.asp?fid=15&cat=Interop

http://www.xtremevbtalk.com/showthread.php?t=160433

There are also interop assemblies available for Office XP

http://support.microsoft.com/kb/328912

It appears to be problematic using office automation from 2000 and earlier with .NET.

The alternative there would be to just execute a VBScript.

Gareth


[/size]quote:[/size]Originally posted by Datapump User:
I was wondering if how I could add the reference library for Microsft Excel 2003 so that I can call Excel library functions from the script editor? I am trying to auto-execute an excel macro (vbscript) that's embedded in the excel file I am exporting the data to with Datapump. Thanks [/size]Thanks but the Microsoft.Office.Interop didn't work, the compiler in Datapump gave an error on type .Interop.

Any more suggestions would be greatly appreciated, this is a big stumbling block for me and the productivity I could achieve with Datapump.

Thanks [/size]

[Grant]

I tried adding a sample of the data but this forum would not accept various characters that were including in the text...

This is what the report looks like...
07/02/04 08:48 Accrual Wage Summary from 06/13/04 to 06/30/04 Page: 1
--------------------------------------------------------------------------------
Date Full Name Emp. # GLCode Div TOT TOT$ OT1 OT1$
================================================== ==============================
Dep #: 100
================================================== ==============================
06/25/04 XXXXXX, XXX X. 5120000 5600 512 67.50 592.65 0.00 0.00
06/29/04 XXXXXXXX, XXXXXXX 5040000 5600 504 30.25 321.56 0.00 0.00
06/27/04 XXXXX, XXXXXX 5101111 5600 510 91.50 900.65 0.00 0.00
06/30/04 XXXXXX, XXXXXXX 5070000 5600 507 98.50 1,119.79 2.50 42.19
06/30/04 XXXXXX, XXXXXX X. 5090000 5653 509 96.50 1,337.65 6.50 130.75

---------------------------------------
Department Total: ??????? ????????? 293.50 4,944.50
--------------------------------------------------------------------------------
Dep #: 131
================================================== ==============================
06/26/04 XXXXXXXX, XXXXXX 5093640 5600 501 82.00 0.00 2.25 0.00
---------------------------------------
Department Total: 82.00 0.00 2.25 0.00
--------------------------------------------------------------------------------
Dep #: 200
================================================== ==============================
06/28/04 XXXXXXXX, XXXXXXXXX 5040000 5601 504 96.00 921.60 0.00 0.00
06/27/04 XXXXXX, XXXXXXXXXXXXXX 5012222 5601 501 105.00 1,218.00 0.00 0.00




07/02/04 08:48 Accrual Wage Summary from 06/13/04 to 06/30/04 Page: 5
--------------------------------------------------------------------------------
Date Full Name Emp. # GLCode Div TOT TOT$ OT1 OT1$
================================================== ==============================
06/24/04 XXXXX, XXXXX 5031111 5601 503 80.00 841.60 0.00 0.00
06/29/04 XXXXX, XXXXXXXXXX 5041111 5601 504 88.00 844.80 0.00 0.00
06/30/04 XXXXXX, XXXXXXX 5051111 5601 505 95.50 953.09 0.00 0.00
06/28/04 XXXXXXX, XXXXXX 5011111 5601 501 103.75 1,141.25 0.00 0.00
06/30/04 XXXXXXX, XXXXXXXX 5041111 5601 508 120.00 1,107.20 0.00 0.00
06/30/04 XXXXXX, XXXXXX 5011111 5601 501 64.25 706.75 0.00 0.00
Dep #: 140
================================================== ==============================
06/26/04 XXXXXXXX, XXXXXX 5093640 5600 501 82.00 0.00 2.25 0.00
---------------------------------------
Department Total: 82.00 0.00 2.25 0.00
--------------------------------------------------------------------------------
Dep #: 280
================================================== ==============================
06/28/04 XXXXXXXX, XXXXXXXXX 5040000 5601 504 96.00 921.60 0.00 0.00
06/27/04 XXXXXX, XXXXXXXXXXXXXX 5012222 5601 501 105.00 1,218.00 0.00 0.00


This is the output I need from it..

D,G,FP,502,5652,,,,,19770.57,Accrue salary costs for Dept 400,
D,G,FP,502,5662,,,,,547.64,Accrue EI costs for Dept 400,
D,G,FP,502,5663,,,,,978.64,Accrue CPP costs for Dept 400,
D,G,FP,502,5665,,,,,395.41,Accrue Payroll Tax costs for Dept 400,
D,G,FP,502,5656,,,,,14008.13,Accrue salary costs for Dept 500,
D,G,FP,502,5662,,,,,388.03,Accrue EI costs for Dept 500,
D,G,FP,502,5663,,,,,693.40,Accrue CPP costs for Dept 500,
D,G,FP,502,5665,,,,,280.16,Accrue Payroll Tax costs for Dept 500,
D,G,FP,502,5654,,,,,3001.06,Accrue salary costs for Dept 600,
D,G,FP,502,5662,,,,,83.13,Accrue EI costs for Dept 600,
D,G,FP,502,5663,,,,,148.55,Accrue CPP costs for Dept 600,
D,G,FP,502,5665,,,,,60.02,Accrue Payroll Tax costs for Dept 600,
D,G,FP,502,5655,,,,,3816.57,Accrue salary costs for Dept 700,
D,G,FP,502,5662,,,,,105.72,Accrue EI costs for Dept 700,
D,G,FP,502,5663,,,,,188.92,Accrue CPP costs for Dept 700,
D,G,FP,502,5665,,,,,76.33,Accrue Payroll Tax costs for Dept 700,
D,G,FP,502,1711,,,,,-44542.28,Total Payroll Costs Accrued for Division 502,
D,G,FP,503,5600,,,,,10136.60,Accrue salary costs for Dept 100,

The model I have in monarch takes the report text and exports a spreadsheet containing 3 columns...
Division, Department and Total Dollars....
Divis dept TotDollars
501 100 17185.05
501 131 0.00
501 200 32440.78
501 300 2772.84
501 800 4547.26
501 970 146.78
502 400 19770.57

My excel macro then processes these columns and applies rules for GL coding etc. and outputs the report in .csv report for upload with header lines, etc.

Feel free to have a look and let me know of any suggestions...

Thanks,
Grant

[Grant]

I am using MDP 7.01 and Monarch Pro 7.01.

Thanks