Excel once again: How to create multiple sheets ?

Nokio

Hi,
i have the following problem. I've created an extern Excel file which i have to fill out from navision later. I will make this with the ExcelBuffer table.

It has the standard 3 worksheets and I need 3 worksheets more.
How exactly i can create them?

I have the folling automations. Do I need any others?
Excel = 'Microsoft Excel 11.0 Object Library'.Application
Workbook ='Microsoft Excel 11.0 Object Library'.Workbook
Worksheet = 'Microsoft Excel 11.0 Object Library'.Worksheet

Another Question is, when I manage to create the 3 worksheets, how can I select f.e. sheet no. 4.

Couldn't find anything helpful in searching the forum.
Thx 4 any help.

Queenmercury

There's no way to create multiple sheets with ExcelBuffer... I've created a new object based on Excel Buffer with these these updates :

1) I've modified the function "CreateSheet" in a way it always call the automation server function that adds a sheet.
2) I've created one function called "FillDataInSheet(SheetName)" with the following code :

CreateSheet(SheetName,'xxxx',COMPANYNAME,USERID);
DELETEALL;
ClearNewRow;

When i want to export in excel from any other object :
1) First i create a variabile linking to the new object
2) I call AddColumn...
3) I call FillDataInSheet. It fills the sheet and make the buffer empty.
4) Start over again from 2 for every sheet you want to create.

Bye

Nokio

Which function of the automation server is´it and how exactly can I use it?

Queenmercury

This is the function i've modified...in bold the function of automation server.
Open table 370 and compares the code to mine.
Note that I've deleted any refernce to the "Information Sheet" of the standard because i use it as any other sheet (I deleted also the functions addinfocolumns....).

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

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

XlWrkSht := XlWrkBk.Worksheets.Add();
XlWrkSht.Name := SheetName;

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;
IF FIND('-') THEN BEGIN
REPEAT
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
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;
UNTIL NEXT = 0;
XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
END;

Window.CLOSE;

mjrogers

Great info!, thanks

Savatage

Nokio wrote:

It has the standard 3 worksheets and I need 3 worksheets more.

FYI
Excel->Tools->Options->General->"Sheets In New Workbook"=3

you can change it to whatever you want.