Excel file generated through NAS Job Queue doesn't appear

boeves · 2012-03-09

Why won't report save my excel file on the server when running through the Job Queue (NAS)? It works from the command line and thru classic on the same server! Job finishes but the file is nowhere! ](*,)

I'm writing in hopes someone has had the same issue and can provide some assistance. I have searched Mibuso for hours and while it got me this far in the process (thank you to all who post solutions!!), I haven't found anything that refers to the latest issue that has come up.

As stated above, the report runs and saves the file to the server when I run it through classic and when I run it with the Run command utilizing the RTC on the NAS server. BUT, while the Job Queue job finishes successfully, there is no file in the directory when it completes. I am running the report all 3 ways utilizing the same credentials and on the same server which is also hosting NAS.

Also, I am making sure that there are no user interface requirements within the code (I've commented them out or created new functions). And, I did add the Desktop folder to the C:\Windows\SysWOW64\config\systemprofile\Desktop directory on the server.
And, other jobs which run on the job queue save PDF's to the directory I have specified in this report, so it shouldn't be an issue of permissions.

Thanks so much!!!!

Luc_VanDyck · 2012-03-09

boeves wrote:

Why won't report save my excel file on the server when running through the Job Queue (NAS)? It works from the command line and thru classic on the same server! Job finishes but the file is nowhere! ](*,)

I'm writing in hopes someone has had the same issue and can provide some assistance. I have searched Mibuso for hours and while it got me this far in the process (thank you to all who post solutions!!), I haven't found anything that refers to the latest issue that has come up.

Then you have read this thread as well: Is Excel automation supposed to work together with the NAS?. The link in that thread, http://support.microsoft.com/default.as ... -us;257757 , tells us:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Running the NAS from command line is not the same as running the NAS as a Windows Service.

boeves · 2012-03-09

Thank you for the reply. Yes, I did see the post, but thought someone may have found a way around this by now other than going the Open XML route.

As they have not..... Or I'm guessing you would know

.... I will explore that now.

Thanks again!

ssingla · 2012-03-10

Hi All,

I haven't faced any problem using NAS with export to excel. The following is the code which is used (nothing unusual/extra coding has been used):

SMTPSetup.GET;
CompInfo.GET;
IF Salesperson.FINDSET THEN REPEAT
  IF Salesperson."E-Mail" <>'' THEN BEGIN
    SNo:=0;
    CommentT.SETRANGE("Table Name",CommentT."Table Name"::Customer);
    CommentT.SETRANGE(Date,TODAY);
    CommentT.SETRANGE("Salesperson Code",Salesperson.Code);
    IF CommentT.FINDSET THEN BEGIN
      ExcelBuf.DELETEALL;
      ExcelBuf.RESET;
      MakeExcelDataHeader;
      REPEAT
        Cust.GET(CommentT."No.");
        SNo+=1;
        MakeExcelDataBody;
      UNTIL CommentT.NEXT=0;
      ExcelBuf.CreateBook;
      ExcelBuf.CreateSheet('Blocked Customer','BPFood',COMPANYNAME,USERID);
      IF EXISTS(SMTPSetup."Default File Location"+'\'+DELCHR(FORMAT(Salesperson.Code),'=','-./')+'.xlsx') THEN
        ERASE(SMTPSetup."Default File Location"+'\'+DELCHR(FORMAT(Salesperson.Code),'=','-./')+'.xlsx');
      ExcelBuf.SaveWorkBook(SMTPSetup."Default File Location"+'\'+DELCHR(FORMAT(Salesperson.Code),'=','-./')+'.xlsx');

      //Send Mail
      TextString:=SMTPSetup."Default Connection";
      IF ISCLEAR(conn) THEN CREATE(conn);
        conn.ConnectionString(TextString);
      conn.Open;
      conn.Execute('EXEC CustBlockedMail '+''''+Salesperson."E-Mail"+''''+','+''''+'Blocked Customer Report'+''''+','+''''+
      (SMTPSetup."Default File Location"+'\'+DELCHR(FORMAT(Salesperson.Code),'=','-./')+'.xlsx')+'''');

      conn.Close;
      CLEAR(conn);

    END;
  END;
UNTIL Salesperson.NEXT=0;

I have not posted the whole code (other functions called from within the code above) because they are not relevant.

ssingla · 2012-03-10

Further the NAS is installed on Database server which is a VM with Win Serv. 2008 R2 64 Bit edition and SQL Server 2008 R2. We have also installed Excel 2010 on the server (2007 was originally installed and that also worked). The code is running for the last 3 months and I have not faced any problems except the following message came:

"This message is for C/AL programmers: The call to member LeftHeader failed. Microsoft Office Excel returned the following message:Unable to set the LeftHeader property of the PageSetup class "

The solution to this error was to install printer on the server (PDF Creator did it) and make it a default printer. NAS generates some 30 excel files and send it with email everyday.

boeves · 2012-03-12

Thank you Sandeep!!! I reviewed your code and noticed that my file extension was .xls not .xlsx, as soon as I changed that it worked! Thank you so much for taking the time to post that code and reply, you made my day!

boeves

Excel file generated through NAS Job Queue doesn't appear

Answers

Categories