Auto-generate report to excel with Job Queue Entry Process

CaineWolf

Hello,

I'm running MS NAV2009 R2 in a Classic Client environment. We have a "Production Report" with a single DataItem that points to the <Sales Header> (a relatively simple report).

The standard version of this has Sections for a printed copy, but there is also an option on the request form to Export to Excel. This all works fine.

We now want to be able to run this out of hours so that and excel version is ready each morning when the boss arrives. I thought a Job Queue Entry process would do it as long as I set the variable ExporttoExcel:= TRUE; in the OnInitReport()


I have also added C/AL code to the OnPostReport()

ManufSetup.GET;
ExcelBuf.SaveBook('\\Shared\NAV\Production\Report'+FORMAT(WORKDATE,0,'<day,2><month,2><year4>')+'.xlsx');
ExcelBuf.CloseBook('\\Shared\NAV\Production\Report'+FORMAT(WORKDATE,0,'<day,2><month,2><year4>')+'.xlsx');

As mentioned, it all runs perfectly fine when I run it manually. I've set up a Job Queue Entry to run it but every time I do it just sits in a status of IN PROCESS and doesn't generate the excel spreadsheet (and blocks all other Job Queue Processes until I reset the NAS).

I have tried creating an alternate version of the same report and removed the Sections so that it is a kind of "excel only" but that removed the data when I ran it manually and still didn't let the JQE process run.

Any suggestions about what may be causing this and a possible resolution?

Luc_VanDyck

Have a look at this thread: viewtopic.php?f=23&t=26971&hilit=excel+nas&start=15

User tinoruijs mentions that it worked after replacing the UNC paths with a driveletter. Your mileage may vary though.

CaineWolf

Thanks for the prompt response Luc.

I don't think it's the path that's the problem.

I think it was to do with the Code that was populating excel was on the Sections. So I moved the Code to the DataItem and deleted the sections but now I'm getting a message:

An exception was raised in method Range. The OLE control or Automation server has returned error (HRESULT) -2147352567.
The component did not provide the exception description.

When I try to run it manually.

I've narrowed it down to a single line of Code:

ExcelBuf.INSERT;

When I comment this out I don't get the error, but the report runs with no actual data (lines) just Column Headers and Totals are added.

I don't understand what it means. A quick search suggested it was either "permission" based or that there was too much data for Excel to handle, but neither is true since it worked previously (when the Code was on the Sections).

Does anyone know what else could be causing this message?