Generating reports into Excel at night

Shenpen

Sometimes no matter what you do, the slowness of a report due to too much transactions or a complicated request makes it hard to use.

So why not autogenerate reports at night into Excel?

Here is an example inventory list - one for each Location on a separate Worksheet.

{
NOTES: sum won't work with any other language than English, change it, f.e. SZUM for Hungarian


Variables:

Name	DataType	Subtype	Length
Excel	Automation	'Microsoft Excel 11.0 Object Library'.Application	
Workbook	Automation	'Microsoft Excel 11.0 Object Library'.Workbook	
Worksheet	Automation	'Microsoft Excel 11.0 Object Library'.Worksheet	

}

IF NOT CREATE(Excel,TRUE) THEN
  ERROR('FSCK!!!');
Excel.Visible(FALSE);
Workbook := Excel.Workbooks.Add;
CLEAR(Loc);
IF Loc.FIND('-') THEN REPEAT
   Worksheet := Workbook.Worksheets.Add;
   Worksheet.Name:=Loc.Code;
   CLEAR(Item);
   CLEAR(i);
   IF Item.FIND('-') THEN REPEAT
      Item.SETFILTER("Location Filter",Loc.Code);
      Item.CALCFIELDS(Inventory);
      IF Item.Inventory<>0 THEN BEGIN
         i+=1;
         Worksheet.Range('A'+FORMAT(i)).Value:=Item."No.";
         Worksheet.Range('B'+FORMAT(i)).Value:=Item.Description;
         Worksheet.Range('C'+FORMAT(i)).NumberFormat:=0;  //all other format you can check out with Excel macro recording
         Worksheet.Range('C'+FORMAT(i)).Value:=Item.Inventory;
      END;
   UNTIL Item.NEXT=0;
   Worksheet.Range('B'+FORMAT(i+2)).Value:='Total:';
   Worksheet.Range('C'+FORMAT(i+2)).Value:='=SUM(' + 'C1:'+'C'+FORMAT(i)+ ')'; //language-dependent, in Hungarian Excel it is SZUM
UNTIL Loc.NEXT=0;
Workbook.SaveCopyAs('D:\locations'+FORMAT(TODAY) +'.xls');
// your date format may cause problems


// or if you want to show it:
//Excel.Visible(TRUE);
//Excel.UserControl(TRUE);
CLEAR(Excel);

fruz

Define the SUM as TextConstant, so anyone can give his own language if needed.

montgat

It seems great !!! it's just an EXCEL report generation?
#-o