9 Replies Latest reply: May 15, 2014 10:00 AM by Grant _ RSS

    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

       

      [size="1"][ November 01, 2004, 01:02 PM: Message edited by: Datapump User ][/size]

        • Adding Excel 2003 Reference File to post export script
          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!   [img]smile.gif[/img]

          • Adding Excel 2003 Reference File to post export script
            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.

             

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

             

            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

             

            [url="http://support.microsoft.com/?kbid=317109"]http://support.microsoft.com/?kbid=317109[/url]

             

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

             

            [url="http://www.xtremevbtalk.com/showthread.php?t=160433"]http://www.xtremevbtalk.com/showthread.php?t=160433[/url]

             

            There are also interop assemblies available for Office XP

             

            [url="http://support.microsoft.com/kb/328912"]http://support.microsoft.com/kb/328912[/url]

             

            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 /b[/quote]

            • Adding Excel 2003 Reference File to post export script
              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.

              • Adding Excel 2003 Reference File to post export script
                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.

                 

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

                 

                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

                 

                [url="http://support.microsoft.com/?kbid=317109"]http://support.microsoft.com/?kbid=317109[/url]

                 

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

                 

                [url="http://www.xtremevbtalk.com/showthread.php?t=160433"]http://www.xtremevbtalk.com/showthread.php?t=160433[/url]

                 

                There are also interop assemblies available for Office XP

                 

                [url="http://support.microsoft.com/kb/328912"]http://support.microsoft.com/kb/328912[/url]

                 

                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]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 /b[/quote][/b][/size][/QUOTE]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

                • Adding Excel 2003 Reference File to post export script
                  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. /b[/quote]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

                  • Adding Excel 2003 Reference File to post export script
                    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]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. /b[/quote]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 /b[/size][/QUOTE]

                    • Adding Excel 2003 Reference File to post export script
                      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]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.

                       

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

                       

                      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

                       

                        [url="http://support.microsoft.com/?kbid=317109"]http://support.microsoft.com/?kbid=317109[/url] 

                       

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

                       

                        [url="http://www.xtremevbtalk.com/showthread.php?t=160433"]http://www.xtremevbtalk.com/showthread.php?t=160433[/url] 

                       

                      There are also interop assemblies available for Office XP

                       

                        [url="http://support.microsoft.com/kb/328912"]http://support.microsoft.com/kb/328912[/url] 

                       

                      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]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 /b[/quote][/b][/size][/QUOTE]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 /b[/size][/QUOTE]

                      • Adding Excel 2003 Reference File to post export script
                        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

                        • Adding Excel 2003 Reference File to post export script
                          Grant _

                          I am using MDP 7.01 and Monarch Pro 7.01.

                           

                          Thanks