Excel Buffer + NAS in NAV2013?

Miklos_Hollender

First of all about the old, Classic Excel buffer: it worked with COM objects, and while Excel COM objects were never meant to be used without a GUI, as a Windows Services (which the Application Server is) I found if I comment out the code that sets the margin and page layout stuff then it works. So I based many important reports on it, automatically e-mailing the files with the SMTP Mail codeunit and scheduling it automatically with Job Queue because this is how it should be in the 21st century: managers should not need to have to log in to Navision and run a report, but have weekly sales etc. delivered in e-mail automatically.

Eventually we are going to have to upgrade, I would like to ask whether in the NAV 2013 the Excel Buffer with its new, .NET based operation can work with a scheduled GUI-less background service, like the NAS or something similar? Also including the kind of formatting changes recommended in the blogs i.e. background colors etc. with OpenXML?

Luc_VanDyck

I believe that in the 21st century, such reports should be created and scheduled using a separate reporting tool and not from within NAV.

But that aside, I know some people are using http://closedxml.codeplex.com/ to create Excel files through .net interop, without the need for Excel libraries.

Miklos_Hollender

Given that NAV has Job Scheduler, Excel Buffer, SMTP, adding as SaveAs option and mailing it is not an "un-Navisionific" solution.

Pretty much every reporting tools I found has limitations and I hate ever having to say "can't" to users.

I actually made a reporting tool on my own as a bunch of PowerShell scripts, because that is basically the new visual basic, a set of functions that read SQL stored procedures and write and send Excel files.

This works well enough for simpler reports, but in more complex ones there SQL joins and unions reach their limits and the only solution is iterating through cursors. That sort of thing is better done in C/AL. Plus, SQL doesn't have CHANGECOMPANY, and I consolidate information from multiple companies in my reports. Managers love this stuff, when every worksheet is a different company and they can check all in one file.

For this reason, I would like to operate within the logic of NAS if it works. Replacing the .NET libraries it uses it with another .NET library is an option, but only if truly necessary...

Miklos_Hollender

So, does anyone know that the new, .NET based Excel Buffer in NAV 2013 works with NAS i.e. without a GUI?

infonote

I am actually encountering a similar problem using Job Queue, NAS and Excel in NAV 2013.

Using the commented code below I get

// FileObject.CopyFile(FileNameServer,InFilePath);
//FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
//FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,'Book1.xlsx',InFilePath);

Microsoft Dynamics NAV

---

Microsoft Dynamics NAV Server attempted to issue a client callback to download a file: Book1.xlsx (CodeUnit 419 File Management). Client callbacks are not supported on Microsoft Dynamics NAV Server.

---

OK

I tried using

XlWrkBk.SaveAs(InFilePath);
XlWrkBk.Close(FALSE);
XlApp.Quit;

But since NAV 2013, .NET INTEROP is used instead of Automation, so there are a lot of parameters to pass.

Will post the solution once I find it.

Miklos Hollender wrote:

So, does anyone know that the new, .NET based Excel Buffer in NAV 2013 works with NAS i.e. without a GUI?

jversusj

any update on this? I have a modified Aged AR that we export to XL via the Excel Buffer. I modified the Excel Buffer table to save to a network path per a MiBuso blog and created a codeunit that runs the export just fine when I run it from object designer. However, when I try to schedule the codeunit to run, it always fails with the error show below.

Microsoft Dynamics NAV

---

Microsoft Dynamics NAV Server attempted to issue a client callback to download a file: Book1.xlsx (CodeUnit 419 File Management). Client callbacks are not supported on Microsoft Dynamics NAV Server.

---

OK

---

The Excel Buffer table has the added function SaveExcel, and the following two lines are present.

FileNameClient := FileManagement.DownloadTempFile(FileNameServer);
FileNameClient := FileManagement.MoveAndRenameClientFile(FileNameClient,FileName,FilePath);

I understand that the NAS is probably not happy with the DownloadTempFile call, is there an alternative I can use? I just want to take an existing report that is frequently executed during the day and be able to automate it's output over the weekend.

jversusj

I went through this with Debugger and found it's actually choking at a call to PreOpenExcel.

FileManagement.DownloadHandler throws the error about callback. I found a thread online suggesting to use Workbook method SaveAs, but when I try to use that, I still get the call back error on the following line of code.

XlApp := XlApp.ApplicationClass;

Is anyone else doing what I'm trying to accomplish with the Excel Buffer? what's your approach?

Miklos_Hollender

Sorry for the late answer, but due to needs like multiple sheets, background colors which sound difficult in the standard, I use Gunnar's ClosedXML Excel Buffer with slight modifications. http://www.dynamics.is/?p=1724 I think the only change I had to do was to comment out the BorderAround stuff as that did not seem to work with the NAS, and add these stuff I am using like Backcolor field, functions like AddSheet, MakeASum, SaveAndSendMail etc. I would share it but it is not very clean, I don't 100% remember what is what...