Schedule job using Excel buffer

Business Central/NAV User Forum
1

Hi All

I wrote many report using Excel Buffer (370) to export excel file. Can somebody tell me how to export these reports by scheduled job or other method?

2

If you are on version 5.0 or above, you can use jobqueue and use NAS to schedule jobs to run.

3

Yes Thanks for your reply. Actually, I am using NAV 5.0 SP1 and using Excel buffer but in Excel buffer (370), I can’t find the save or save as function to export excel file as following sample. I use createbook to export file after the file created to “Book1”. How can I auto to create excel file named “d:\testexcel.xls”?

Name
CreateBook
OpenBook
CreateSheet
CreateRangeName
GiveUserControl
ReadSheet
SelectSheetsName
FilterToFormula
NextValue
SumIf
GetFormulaUnitErr
GetExcelReference
ExportBudgetFilterToFormula
AddToFormula
GetFormula
SetFormula
ClearFormula
NewRow
AddColumn
StartRange
EndRange
CreateRange
AutoFit
BorderAround
ClearNewRow
SetUseInfoSheed
AddInfoColumn
UTgetGlobalValue
SetCurrent
CreateValidationRule
LineStyle

4

look at one of existing reports that exports to excel and make a copy and try to change for your own needs.

5

Would you give me a sample of auto named excel report in standand NAV.

6

You need to add a save function to the Excel export routine in the excel buffer. Its very easy if you know a little about VB and Excel macros.

7

I added saveas Function in 370 excel buffer table as following code and run the report that is ok. It can save the excel automatically eg"d:\test.xlsx". when added into the Job queue. Then I wait for the queue job after run that the queue log is show success. But the file can’t be find in saved path. Have anyone can share the experience?

XlsSaveAs(Filename : Text[250])
IF FILE.EXISTS(Filename) THEN BEGIN
ERASE(Filename);
XlWrkBk.SaveAs(Filename);
END ELSE BEGIN
XlWrkBk.SaveAs(Filename);
END;