3 Replies Latest reply on May 15, 2012 3:28 PM by Ahmad Fattahi

    Excel automation with Datalink macros

    retetes

       Hello,

       

       We have made a reporting system that uses excel automation with datalink plugin.

       

       This system has been implemented on excel 2007 and datalink 3.1.6 on a Windows 2003 system and has been working web for years.

       

       Recently we had to migrate the application to a new computer with Windows 2008, Excel 2007 and the same version of datalink. It seems to be working correctly but the report does no make the calculations:

       

       The cells that use functions defined in the plugin datalink then the value '#NAME?' indicating that the function or macro is no recognized. Teh application that performs the execution of the report does not generate any errors or exceptions and the log indicates that all actions are performed correctly.

       

       If we open the same file and press F9 the calculation is done without any problem, accessing PI datalink and calculating without problems.

       

       We tried also with Datalink 2010 SP1 but the result is just the same.

       

       We have reviewed the post of vCampus '[DEAD LINK] http://vcampus.osisoft.com/discussion_hall/add_in_microsoft_development/f/11/p/251/1008.aspx# 1008' but we had alredy done the accepted solution.

       

       Our system runs a console application implemented in .NET (C#) that runs under a user of the Administrators group. The code that calculates the reports goes as follows:

       
      // fileName = copy in temporal directory of a xlsx file
      Workbook wk = excel.Workbooks.Open(
          fileName
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value
          , Missing.Value);
      // Register pipc32 addin
      excel.RegisterXLL(Path_xll); // In our case 'E:\Aplicaciones (x86)\PIPC\Excel\pipc32.xll'
      // Set report date param
      Worksheet sh = (Worksheet)wk.Worksheets["Hoja1"];
      string date = Today.ToShortDateString();
      sh.Cells[2, 2] = date; // B2
      // Execute calculations
      excel.Calculate(); // CalculateFull(); CalculateFullRebuild();
      // Save book
      wk.Save();
      wk.Application.Quit();
      

       

       
       Does anyone see any errors in out code?
       Can it be an incompatibility between versions of windows/excel/datalink?
       Has this problem occurred to anyone before?


      System in which the application works correctly:
      - Windows 2003 R2 sp1
      - Microsoft Office 2007
      - Datalink: 3.1.6


      New system in which the application does not work:
      - Windows Server 2008 R2 Standard Service Pack 1 (64bit)
      - Microsoft Office Excel 2007 (12.0.4518.1014)
      - Datalink 3.1.6 (we tried without success with Datalink 2010 SP1).

       Thanks in advance

        • Re: Excel automation with Datalink macros
          Ahmad Fattahi

          Can you check the return Boolean from the call RegisterXLL? You get #NAME when the XLL is not registered correctly. Please make sure that it is.  If the RegisterXLL code is returning false and not true, that means the XLL is not registered. Please check the path to the XLL to make sure it is correct among other things.

            • Re: Excel automation with Datalink macros
              retetes

              Hello,

               

              Thanks for the answer.

               

              After checking the 'RegisterXLL' method returned "true" I have test some modifications in the plugin register and open workbook instructions order . Finally it has worked well. This is my final code

               

              // Register pipc32 addin
              bool reg = excel.RegisterXLL(Path_xll); // In our case 'E:\Aplicaciones (x86)\PIPC\Excel\pipc32.xll'
              
              // fileName = copy in temporal directory of a xlsx file
              Workbook wk = excel.Workbooks.Open(
                  fileName
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value
                  , Missing.Value);
              AddIn addin_pipc = excel.AddIns.Add(Path_xll);
              

               

               

              I have not tried it without 'excel.Addins.Add()' intruction, but now the app is OK.

               

              Thank you very much.