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

Sales & Purchases number series reaching maximum

In one of the larger companies that I have worked with we ran into a problem with the number series for sales- and purchase documents.  When the company started to use Navigator back in 1994 all the number series were initialized.  Then after some years of usage and upgrade to Financials, Navison and Dynamics NAV those original number series reached maximum and new invoices, credit memos and shipments where not the last in the history list.

To solve this I created a renumbering tool.

RenumberingSalesAndPurchases

I used this tool in NAV 4.00 SP3 and also in NAV 2009 R2.

With this tool I added number prefix to the old numbers and then changed the no. series to create new numbers into the same pattern.  Now, of course, the document numbers are nine numbers and should not be a problem in the future.

The tool is attached below.

SalesPurchDocRenumering

Use SAVEASPDF to E-Mail Invoices and Credit Memos

When I was running NAV 2009 I used PDFCreator, BioPDF or BullZipPDF printers to create a PDF copy of a report.  In NAV 2013 we have the option to use native SAVEASPDF command that uses built-in methods to create a PDF document on the server.

I used the Job Queue in NAV 2009 to send all my invoices and credit memos via email to my customers.  An updated version for NAV 2013 that uses

[code]
FileName := FileMgt.GetDirectoryName(FileMgt.ServerTempFileName(‘pdf’)) + ‘\’ + STRSUBSTNO(‘%1 %2.pdf’,TABLECAPTION,"No.");
IF EXISTS(FileName) THEN
ERASE(FileName);
SalesInvHeader := "Sales Invoice Header";
SalesInvHeader.SETRECFILTER;
CLEAR(Invoice);
Invoice.SETTABLEVIEW(SalesInvHeader);
Invoice.USEREQUESTPAGE(FALSE);
IF Invoice.SAVEASPDF(FileName) THEN BEGIN[/code]

is attached below.

JobQueueEMailer2013