Excel Buffer - Creating Multiple Sheets In 1 Workbook

Jonathan2708 · 2008-02-22

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 · 2008-02-22

Shouldn't ExcelBuf.CreateSheet be in OnPreDataItem?

Which sheet ends up being created?

Jonathan2708 · 2008-02-22

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 · 2008-02-22

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 · 2008-03-06

Have you tried

ExcelBuf.SetUseInfoSheed;

Before ExcelBuf.CreateBook;

?

Alkro · 2008-04-08

Anybody could solve this trouble?

I have the same question.

bbrown · 2008-04-08

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 · 2008-04-08

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

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

Quivelus · 2008-04-24

Tx, Works fine!

Dean_Axon · 2009-03-19

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 · 2009-03-19

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 · 2009-11-23

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 · 2011-05-17

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 · 2011-05-25

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

Tiwaz · 2017-04-10

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.?

Howdy, Stranger!

Quick Links

Categories

Excel Buffer - Creating Multiple Sheets In 1 Workbook

Comments