Excel Buffer - Creating Multiple Sheets In 1 Workbook

Jonathan2708

Hi,

I'm having a dabble creating a ProcessingOnly Report with the Excel Buffer and am trying to create 2 sheets in the same workbook, each populated from records in a different dataitem. My code is as follows :

ExcelBuf 	Record	Excel Buffer(370)	Temporary=Yes

DataItem	Name
---------     ------------------
G/L Entry	PurchaseDocuments
G/L Entry	ChequesPettyCash

PurchaseDocuments - OnPreDataItem()
-----------------------------------
ExcelBuf.NewRow;
ExcelBuf.AddColumn('PurchInvNo.',FALSE,'',TRUE,FALSE,TRUE,'@');

PurchaseDocuments - OnAfterGetRecord()
--------------------------------------
ExcelBuf.NewRow;
ExcelBuf.AddColumn("Document No.",FALSE,'',FALSE,FALSE,FALSE,'');

PurchaseDocuments - OnPostDataItem()
------------------------------------
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet('PurchDocs','PurchDocs',COMPANYNAME(),USERID());
ExcelBuf.DELETEALL(FALSE);

ChequesPettyCash - OnPreDataItem()
----------------------------------
ExcelBuf.NewRow;
ExcelBuf.AddColumn('ChequesNo.',FALSE,'',TRUE,FALSE,TRUE,'@');

ChequesPettyCash - OnAfterGetRecord()
-------------------------------------
ExcelBuf.NewRow;
ExcelBuf.AddColumn("Document No.",FALSE,'',FALSE,FALSE,FALSE,'');

ChequesPettyCash - OnPostDataItem()
-----------------------------------
ExcelBuf.CreateSheet('Cheques','Cheques',COMPANYNAME(),USERID());

Report - OnPostReport()
-----------------------
ExcelBuf.GiveUserControl;

The problem is that only 1 sheet ends up being created with all the records from both dataitems in it. Can anybody see where I am going wrong?

Thanks,

Jonathan

tinoruijs

Shouldn't ExcelBuf.CreateSheet be in OnPreDataItem?

Which sheet ends up being created?

Jonathan2708

Hi,

No my understanding is that you call CreateSheet once you have populated the buffer records. The sheet being created is the one for the second dataitem - 'Cheques'.

Jonathan

BlueGene

Hi Jonathan,

i dealt with the same problem about 4 years ago and i can remember, that the main problem of this is the wrong key in the table excel buffer.
When you check the fields available in T370, you'll notice that there is nothing such as "Worksheet", which means you could only enter the data for one worksheet at runtime.

It takes some effort to expand the table with a new field "WORKSHEET", expand the key and also adjust the Code saved in Table 370.


Best regards

Bluegene

rsfairbanks

Have you tried

ExcelBuf.SetUseInfoSheed;

Before ExcelBuf.CreateBook;

?

Alkro

Anybody could solve this trouble?

I have the same question.

bbrown

We did something similar a few years ago (version 3.70). The process exported item information to an Excel workbook. The user could select from a list of related supplemental tables such as "Inventory Posting Group" or "Item Category". The records were then exported to multiple worksheets based on the table selected.

The CreateSheet function uses the active worksheet. It expects the worksheet to already exist. This is why it works for 1 worksheet. A new workbook gets created with 1 worksheet to start. To build multiple worksheets you must add a new worksheet before you call the CreateSheet function each time. CreateSheet does not create a worksheet. It adds data to the existing active sheet.

bbrown

Add this function to the Excel Buffer table and call it before the CreateSheet:

AddSheet()
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.Worksheets.Add;

Quivelus

Tx, Works fine!

Dean_Axon

Hi All;

I have done something similar in 4.3 using table 370, I already have the Addworksheet function (works ok) and the extra field on table 370 with a change to the primary key (works ok too), so far so good!!!

The problem I am having is getting the values from the buffer table to appear on the correct sheet. ](*,)

Any suggestions ?

TIA.

Dean.

Dean_Axon

Whoa.... hold the front page !!!!!

:idea: I'VE GOT IT !!!! (Finally LOL \:D/ )

The solution was staring me in the face all the time... so here goes

Step 1:
Add a new field to the buffer table (370):

Enabled =YES
Field No. =50000 (or anything you want)
Field Name =Worksheet name (or anything you want)
DataType = TEXT
Length = 150 (thats what I chose)

Step 2:
Change the primary key to include your new field.

Step 3:
Create a new function in the buffer table

CreateMultiSheet(SheetName : Text[250];ReportHeader : Text[80];CompanyName : Text[30];UserID2 : Text[30])
Window.OPEN(
  Text005 +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

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

IF FIND('-') THEN BEGIN
  REPEAT
    RecNo := RecNo + 1;
    Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

//This is the bit I added
    IF LastWorksheetName<>"Worksheet Name" THEN BEGIN
      XlWrkSht := XlWrkBk.Worksheets.Add();
      XlWrkSht.Name := "Worksheet Name";
      IF ReportHeader <> '' THEN
        XlWrkSht.PageSetup.LeftHeader :=
          STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);
      XlWrkSht.PageSetup.RightHeader :=
        STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
      XlWrkSht.PageSetup.Orientation := XlLandscape;
    END;
//To here
   
    IF NumberFormat <> '' THEN
      XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
    IF Formula = '' THEN
      XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
    ELSE
      XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
    IF Comment <> '' THEN
      XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
    IF Bold THEN
      XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
    IF Italic THEN
      XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;
    XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
    IF Underline THEN
      XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;

//And this bit
    LastWorksheetName:="Worksheet Name";
//To Here

  UNTIL NEXT = 0;
  XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
END;
Window.Close

Et Voila

Hanen

But if I change the primary key, it may cause trouble if this key is used in any other reports or forms...
No????? #-o

BENNE

Hi,


I have added the AddSheet function to T 370 but when I call the function am I getting the following
massage:



Any help would be appreciated

Hanen

And where is the definition of the variable LastWorksheetName?????

Tiwaz

bbrown wrote: »

Add this function to the Excel Buffer table and call it before the CreateSheet:

AddSheet()
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.Worksheets.Add;

Hello @bbrown. Do you know how can I use your function with DotNet variables xlWrkBkDotNet and XlWrkShtDotNet? since there is no dotnet xlApp etc.?