Excell worksheet

Tirta

Hi,
I make one report to export Item Inventory and Consumption to Excel. It shoud be enter in one Workbook, one File but two different Worksheet.
I've tried many ways but it always created 2 Files ](*,)

Can you help me?
Thanks.

Here is my code:

My Report:

Function ExportInventory()
TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);

EnterCell(....)

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet('Inv','',COMPANYNAME,USERID);


Function ExportConsumption()
TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);

EnterCell(....)

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet('Inv','',COMPANYNAME,USERID);

If I don't use Createbook in my second function, it would cause an Error 'No instanz bla bla'

I guess I should create a new function 'CreateNewSheet' in Table Excel Buffer, but how?

lynhthi

Hi !
Here is my solution :

1.
In Table 370, I add new function CreateSheet2(..) the same CreateSheet() function but at the beginning of it, I add some additional code as follow :

CreateSheet2(..)
IF NewSheetNb <> 0 THEN BEGIN
XlWorkSheet := XlWorkBook.Worksheets.Item(NewSheetNb);
XlWorkSheet.Activate;
END;
…


2.
Export the first one :
…..
ExcelBufTemp.CreateBook;
ExcelBufTemp.CreateSheet2(
'Inventory',
'1',
COMPANYNAME,USERID,
0);


3.
Export the second one :
….
ExcelBufTemp.CreateSheet2(
'Consumption',
'2',
COMPANYNAME,USERID,
2);
ExcelBufTemp.GiveUserControl;

Hope your report run well.
If you find out the better way please tell me.
Regard.

Tirta

Hi Lynhthi,

Thanks for your help.
But nothing changed, I still received the same error messages:

This message is for C/AL programmers:

This Automation variable has not been instantiated.
You can instantiate it by either creating or assigning it.

If I put the code

ExcelBufTemp.Createbook;

before the second Exportcommand than it works... but this is not what I want..

Do you still have another idea?

[-o<

lynhthi

I've done and it works well.
Could you tell me more detail than what you do ?

Tirta

Hi Lynhthi,

Ok, I will show you what have I done.

Report 50000 
OnAfterGetRecord()
Calculate etc...


OnPostDataItem()
IF ExportInExcel THEN BEGIN
  ExportInventory;
  ExportHistory;
END;


ExportInventory()
TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);

EnterCell(1,1,'No.',TRUE,FALSE,TRUE);
EnterCell(1,2,'Item No.',TRUE,FALSE,TRUE);
EnterCell(1,3,'Inventory',TRUE,FALSE,TRUE);

IF Item.FIND('-') THEN BEGIN
  RowNo := 1;
  REPEAT
    RowNo := RowNo + 1;
    ColumnNo := 1;
    EnterCell(RowNo,ColumnNo,FORMAT(RowNo - 1),FALSE,FALSE,FALSE);
    ColumnNo += 1;
    EnterCell(RowNo,ColumnNo,Item."No.",FALSE,FALSE,FALSE);
    ColumnNo += 1;
    Item.CALCFIELDS(Inventory);
    EnterCell(RowNo,ColumnNo,FORMAT(Item.Inventory),FALSE,FALSE,FALSE);
  UNTIL Item.NEXT = 0;
END;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateNewSheet('Inventory','1',COMPANYNAME,USERID,0); //Just followed your Tips


ExportHistory()
TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);

EnterCell(1,1,'No.',TRUE,FALSE,TRUE);
EnterCell(1,2,'Item No.',TRUE,FALSE,TRUE);
EnterCell(1,3,'Posting date',TRUE,FALSE,TRUE);
EnterCell(1,4,'Entry type',TRUE,FALSE,TRUE);
EnterCell(1,5,'Quantity',TRUE,FALSE,TRUE);

IF Item.FIND('-') THEN BEGIN
  RowNo := 1;
  REPEAT   
    "Item Ledger Entry".SETFILTER("Entry Type",'%1|%2',"Item Ledger Entry"."Entry Type"::Consumption,
      "Item Ledger Entry"."Entry Type"::"Positive Adjmt.");
    "Item Ledger Entry".SETRANGE("Item No.",Item."No.");
    "Item Ledger Entry".SETFILTER("Posting Date",'%1..%2',XStartingdate,Endingdate);
    IF "Item Ledger Entry".FIND('-') THEN
      REPEAT
        RowNo := RowNo + 1;
        ColumnNo := 1;
        EnterCell(RowNo,ColumnNo,FORMAT(RowNo - 1),FALSE,FALSE,FALSE);
        ColumnNo += 1;
        EnterCell(RowNo,ColumnNo,Item."No.",FALSE,FALSE,FALSE);
        ColumnNo += 1;
        EnterCell(RowNo,ColumnNo,FORMAT("Item Ledger Entry"."Posting Date"),FALSE,FALSE,FALSE);
        ColumnNo += 1;
        EnterCell(RowNo,ColumnNo,FORMAT("Item Ledger Entry"."Entry Type"),FALSE,FALSE,FALSE);
        ColumnNo += 1;
        EnterCell(RowNo,ColumnNo,FORMAT("Item Ledger Entry".Quantity),FALSE,FALSE,FALSE);
      UNTIL "Item Ledger Entry".NEXT = 0;
  UNTIL Item.NEXT = 0;
END;

TempExcelBuffer.CreateNewSheet('History','2',COMPANYNAME,USERID,2); //Just followed your Tips

TempExcelBuffer.SaveBook(FileName);   
TempExcelBuffer.CloseBook;

And in Table 370 Excel Buffer I have added several new function.
To add a new Worksheet in the same Workbook I have tried your ways:

CreateNewSheet(SheetName : Text[250];ReportHeader : Text[80];CompanyName : Text[30];UserID2 : Text[30];NewSheetNb : Integer)
IF NewSheetNb <> 0 THEN BEGIN 
  XlWorkSheet := XlWorkBook.Worksheets.Item(NewSheetNb);
  XlWorkSheet.Activate;
END; 

Window.OPEN(
  Text005 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

XlEdgeBottom := 9;
XlContinuous := 1;
XlLineStyleNone := -4142;
XlLandscape := 2;
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNTAPPROX;
RecNo := 0;

XlWorkSheet.Name := SheetName;
IF ReportHeader <> '' THEN
  XlWorkSheet.PageSetup.LeftHeader :=
    STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);
XlWorkSheet.PageSetup.RightHeader :=
  STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
XlWorkSheet.PageSetup.Orientation := XlLandscape;
IF FIND('-') THEN
  REPEAT
    RecNo := RecNo + 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
    IF Formula = '' THEN
      XlWorkSheet.Range(xlColID + xlRowID).Value := "Cell Value as Text"
    ELSE
      XlWorkSheet.Range(xlColID + xlRowID).Formula := Formula;
    IF Comment <> '' THEN
      XlWorkSheet.Range(xlColID + xlRowID).AddComment := Comment;
    IF Bold THEN
      XlWorkSheet.Range(xlColID + xlRowID).Font.Bold := Bold;
    IF Italic THEN
      XlWorkSheet.Range(xlColID + xlRowID).Font.Italic := Italic;
    XlWorkSheet.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
    IF Underline THEN
      XlWorkSheet.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
  UNTIL NEXT = 0;
XlWorkSheet.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
Window.CLOSE;

lynhthi

HI Tirta !

You can try as follow :

- Don't use this command CLEAR(TempExcelBuffer) in the second export function ExportHistory()
(because when you clear you can not keep the sheet)
- Don't name Sheet as "History" because error :
"The message is for C/AL progrmaers :
The call to member Name failed. Microsoft Excel returned the following message :
History is reserved name."
- And try create book and sheet as I try as below code


ExportInventory()
{
.....
.....
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateNewSheet('Inventory','1',COMPANYNAME,USERID,0);
}


ExportHistory()
{
...
//CLEAR(TempExcelBuffer);
...
TempExcelBuffer.CreateNewSheet('Consumption','2',COMPANYNAME,USERID,2);
TempExcelBuffer.GiveUserControl();
}

Tirta

Thanks for your reply.
I will try rite now.. =P~

Tirta

It works.. !!!!
Thanks Lynhthy!!!! =D>
Cám ón ông

lynhthi

Ah, You can write in Vietnamese but I'm a woman.

Tirta

I can write down only
Got it from google..

Vietnamese (Vietnam) Cám ón
Vietnamese (Vietnam) [to man] Cám ón ông
Vietnamese (Vietnam) [to married woman] Cám ón bà
Vietnamese (Vietnam) [to unmarried woman] Cám ón cô
Vietnamese (Vietnam) [to male equal] Cám ón anh
Vietnamese (Vietnam) [to female equal] Cám ón chi
Vietnamese (Vietnam) [to young person] Cám ón em
Vietnamese (Vietnam) Cám òn qúi vi rhât
Vietnamese (Vietnam) Ông quá tú-tê dôí vói tôi

So many variation.. hmm.. Ups... this is not Grammarforum..
By the way.. I like pho ... vietnamese noodle.. nyam...

Cám ón chi