In one of my projects I use Excel a lot for reporting.  I create the Excel documents with OpenXML and with the help of the Excel Buffer table.  With the click of a button the user creates a beautiful Excel Workbook but when I open the workbook it is displayed in the back of Dynamics NAV client window.

One of the users has been complaining about this and in the process of upgrading from NAV 2009 to NAV 2013 he asked me if this will be fixed.  I of course had to in order to increase the appreciation of the upgrade.

So I looked into this but did not find any .net object to use.  Looked like I had to import user32.dll to do this and that is not possible directly in NAV.  Hence a new class dll was born.

[code language=”csharp”] public static class WindowHelper
{
[System.Runtime.InteropServices.DllImport("user32.dll")]
static extern bool SetForegroundWindow(IntPtr hWnd);

public static void ActivateAppByProcessName(string processName)
{
Process[] p = Process.GetProcessesByName(processName);
if (p.Length > 0)
ActivateAppByHandle(p[0].MainWindowHandle);
}

public static void ActivateAppByPartialProcessName(string processName)
{
Process[] processes = Process.GetProcesses();
foreach (Process process in processes)
{
if (process.MainWindowTitle.Contains(processName))
{
ActivateAppByHandle(process.MainWindowHandle);
}
}
}

public static void ActivateAppByFileName(string fileName)
{
string[] fileNameParts = fileName.Split(new Char[] { ‘.’ });
string windowTitle = "";
for (int i = 0; i < fileNameParts.Length – 1; i++)
{
if (i == 0)
{
windowTitle += fileNameParts[i];
}
else
{
windowTitle += "." + fileNameParts[i];
}
}
ActivateAppByPartialProcessName(windowTitle);
}

static void ActivateAppByHandle(System.IntPtr WindowHandle)
{
SetForegroundWindow(WindowHandle);
}
}[/code]

In the same solution I had a problem converting the Excel Workbook to a PDF file. So I also added that support to this class.

[code language=”csharp”] public static class ExcelHelper
{
public static void ExportPDF(Microsoft.Office.Interop.Excel.Workbook xlWrkBk, string pdfFileName)
{
xlWrkBk.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfFileName);
}
}[/code]

Now I am able to open Excel in front of the NAV client and also able to convert the Excel Workbook to a PDF file.  In Table 370, Excel Buffer, I add a local variable for this class and add a single line in the code.

[code]The changes I make are below[code] PROCEDURE GiveUserControl@3();
VAR
WindowHelper@50000 : DotNet "’UserHelperClass, Version=1.0.0.0, Culture=neutral, PublicKeyToken=22affbe033b077df’.UserHelperClass.WindowHelper" RUNONCLIENT;
BEGIN
IF NOT ISNULL(XlApp) THEN BEGIN
XlApp.Visible := TRUE;
// Dynamics.is – Bring Excel to front –
WindowHelper.ActivateAppByFileName(XlWrkBk.Name);
// Dynamics.is – Bring Excel to front +
XlApp.UserControl := TRUE;
CLEAR(XlApp);
END;
END;
[/code]

And in Codeunit 424 in the bottom of the ExportData trigger

[code]
xlApp.Visible := TRUE;
//Dynamics.is – Bring Excel to Front –
WindowHelper.ActivateAppByFileName(FileMgt.GetFileName(FileName));
//Dynamics.is – Bring Excel to Front +[/code]

The class and then changed objects are attached below.

UserHelperClass  ExcelBufferUpdate

8 thoughts on “My Excel Helper to bring opened Excel Documents to front

  1. Erik says:

    Another great share which wil save me a lot of investigation time to convert NAV 2009 code in which MsExcel and MsWord are used.

    So with openxml and this lib exporting to excel looks faster then in native.

    I think merging NAV data to MsWord can be done also with OpenXML and then use the xml to import in Word like the stylesheet tool.

  2. wakestar says:

    Hi

    I tested this and my experience is that SetForegroundWindow is not always working.
    It really depends on the environment.

    Read this:
    http://www.shloemi.com/2012/09/solved-setforegroundwindow-win32-api-not-always-works/

  3. Thank you.

    I will keep that in mind if the current solution if not working for my customers.

  4. There is an AppActivate method in Microsoft.VisualBasic.Interaction object. Perhaps by using that object this can all be done with NAV code. Have not tested…

  5. Peter says:

    Hi,

    this is exactly what I will need to do, but for Word.

    I created a method that calls: document.ExportAsFixedFormat(pdfFileName, WdExportFormat.wdExportFormatPDF);

    But I always get the error “Instance of .NET Framework Object could not be created: Montage Microsoft.Office.Interop.Word, Version=15.0.0.0, …”

    I tried setting “Specific Version” to false.

    Any Ideas?

    1. Are you creating you own dll file like I did ?
      I was not able to use the PDF export directly from NAV.

      1. Peter says:

        Yes exactly. The shown code is from the static method in the class library (.net 3.5).

      2. Peter says:

        Exactly.

        I created a .net 3.5 class libary with a static class and static method (with the body shown above).

        I can compile and call the function in NAV, but on calling the method on runtime, I get the error.

Leave a Reply

%d bloggers like this: