Tagged Excel

Compile error: Microsoft.Office.Interop.Excel…

A few months ago I wrote a post on how to convert Excel documents to PDF with X++. Well, that piece of code made it into production! :) But I just notice it does not necessarily compile with Ax Build. :(

The code:
Excel Interop error

The error:
The xlApp declaration does not compile because Excel is not installed on the server and AX build can only be completed on the server. If you compile this same piece of code in the AOT on a server that has Excel installed, it compiles without a problem.

Excel Interop syntax error

My solution(s):

  • Install Excel on the server. I am not sure if this will allow AX Build to compile the code, but at least you can then open the AOT after AX build on the sever and compile like I described here.
  • Since I could not install Excel on the server, I did the following:
    1. Compile with AX Build on the server.
    2. If you have a compile error related to Microsoft.Office.Interop.Excel, start the server and open the AOT on a terminal server that has Excel installed.
    3. Compile the code with normal F7 compile.
    4. Compile the CIL on the terminal server.

Hope that helps! Let me know in the comments if you know of a better way to solve this.

Workbook.ExportAsFixedFormat in Microsoft Dynamics AX

How to export a file to PDF in Dynamics AX

Microsoft has an extremely useful .NET method named ExportAsFixedFormat() for exporting office files to PDF. For example, you can invoke the ExportAsFixedFormat() method on an Excel Workbook object and a PDF file is then created in the folder of your choice.

Unfortunately the documentation on how to use this in function in Dynamics AX is a little lacking. In the .NET documentation most of the parameters are described as optional, while in Dynamics AX you have to pass all the parameters.

The last parameter is a pointer to the FixedFormatExt class. It is not necessary to pass an actual value, but the method does not accept null. Saveen Reddy describes in this post how to pass an empty value in C#. In Dynamics AX is even more tricky, since you do not have access to System.Reflection.Missing.Value. You need to create an object like this:

  1.     System.Type                  type           = System.Type::GetType("System.Reflection.Missing");
  2.     System.Reflection.FieldInfo  info           = type.GetField("Value");
  3.     System.Object                missingVariant = info.GetValue(null);

The final job is below, notice:

  1. All the parameters are passed, none are optional (line 32)
  2. The last parameter is a ‘null’ object that uses System.Reflection.Missing (lines 20-22)
  3. The method is in a try and is followed by a catch for a CLRError. This makes debugging much easier since it displays the .NET error which would otherwise not have been shown (lines 42-54 )
  4. I have used System.Type since I was worried AX variables might confuse .NET. As far as I know you could use normal AX str and int variables (lines 13-18)

  1. static void ExcelExportPDFAsFixedFormat(Args _args)
  2. {
  3.     #File
  4.     Microsoft.Office.Interop.Excel.Application  xlApp;
  5.     Microsoft.Office.Interop.Excel.Workbooks    xlWBS;
  6.     Microsoft.Office.Interop.Excel.Workbook     xlWB;
  8.     System.Exception                            exception;
  10.     Filename                                    filename =  @'C:\PDF\test3.pdf';
  11.     Filename                                    excelFileName = @'C:\PDF\TR1-000058.xlsx';
  13.     System.Int32                                toPage                  = 1;
  14.     System.Int32                                fromPage                = 1;
  15.     System.Boolean                              OpenAfterPublish        = false;
  16.     System.Boolean                              IncludeDocProperties    = true;
  17.     System.Boolean                              IgnorePrintAreas        = false;
  18.     System.String                               fileNameDotNet          = filename;
  20.     System.Type                                 type = System.Type::GetType("System.Reflection.Missing");
  21.     System.Reflection.FieldInfo                 info = type.GetField("Value");
  22.     System.Object                               missingVariant = info.GetValue(null);
  24.     new InteropPermission(InteropKind::ClrInterop).assert();
  26.     xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
  27.     xlWBs  = xlApp.get_Workbooks();
  28.     xlWB   = xlWBS.Add(excelFileName);
  30.     try
  31.     {
  32.         xlWB.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType::xlTypePDF,
  33.                                  fileNameDotNet,
  34.                                  Microsoft.Office.Interop.Excel.XlFixedFormatQuality::xlQualityStandard,
  35.                                  IncludeDocProperties,
  36.                                  IgnorePrintAreas,
  37.                                  fromPage,
  38.                                  toPage,
  39.                                  OpenAfterPublish,
  40.                                  missingVariant);
  41.     }
  42.     catch(Exception::CLRError)
  43.     {
  44.         exception = ClrInterOp::getLastException();
  45.         while (exception)
  46.         {
  47.             error(exception.get_Message());
  48.             exception = exception.get_InnerException();
  49.         }
  50.     }
  51.     catch
  52.     {
  53.         error("An unknown exception has occurred");
  54.     }
  55.     CodeAccessPermission::revertAssert();
  56. }

It is also possible to create PDF’s form Word Documents. I’ll post an example if I write a job to do that in the future.

UPDATE 26 December 2014:
A senior colleague wanted to achieve something similar and I offered him this job. He pointed out that an instance of Excel is opened every time the job is run, but never closed. You can see this clearly in the Task Manager.

If you want to use this job, experiment with adding xlWB.Close and/or xlApp.Quit, and keep an eye op the running applications in the Task Manager.