My Excel Helper to bring opened Excel Documents to front

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 Replies to “My Excel Helper to bring opened Excel Documents to front”

  1. 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. 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. 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