Create a WorkSheet in Microsoft Excel

SuperSage77

Hello everybody,

I want to create a WorkSheet in Excel 2007 from Nav 5.0.
I use the table Excel buffer, but I want obtain only one WorkSheet with the Name I setup in my Report.
the code is:
XlWrkSht := XlWrkBk.Worksheets.Add;==> Create a new WorkSheet 'Named'

This is OK but I obtain a new WorkSheet in a new File but with the default WorkSheet 'WorkSheet1'.
I want rename this default WorkSheet with the name of mine to obtain a file with only one Worksheet.

Is it possible?

Thanks for help§!

Rudy

Savatage

Do you want the Tab to be named?

OnPostDataItem()
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet("Your Tab Name Here","Hi MOM!",COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;

SuperSage77

Thanks

But that is correct

My code is:
Text000:='Name of my Sheet'
On Postreport()

IF vg_excel=TRUE THEN BEGIN
Window.CLOSE;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;
END;

But my question was : How do make for have only one Worksheet in my new Excel File ?
I d'nt want have two Worksheet

Thanks

Savatage

In Excel ->Tools->Options-> general->"Sheets in new Workbook" = 1

Eluia

Savatage wrote:

In Excel ->Tools->Options-> general->"Sheets in new Workbook" = 1

The problem with that is it will depend on the Client Excel parameters, so you'd have to set it each times.
Also note that Excel sheet names are limited to 31 characters only os SheetName should be a Text[31] var.

The way to create a new workbook containing only 1 Sheet is to use Excel's language, I'd use either TempExcelBuffer objects or add in the temp excel buffer table the following function:

CreateBookWithOneNamedSheet(SheetName : Text[31])
{
IF NOT CREATE(XlApp,TRUE) THEN
  ERROR(Text000);
XlApp.Visible(FALSE);
XlWrkBk := XlApp.Workbooks.Add(-4167);
XlWrkSht := XlWrkBk.ActiveSheet;
XlWrkSht.Name := SheetName;
}

-4167 is the value of the constant xlWBATWorksheet in Excel, creates a new workbook containing only one worksheet.

SuperSage77

Thanks it is super.

I did not know this parameter (-4167)

Rudy

Dean_Axon

Hi Eluia,

Where did you get the Excel Parameter (-4167) ?

Could you point me in the direction of a resource for these parameters ?

Many Thanks in advance

Savatage

Eluia wrote:

Savatage wrote:

In Excel ->Tools->Options-> general->"Sheets in new Workbook" = 1

The problem with that is it will depend on the Client Excel parameters, so you'd have to set it each times.

Not sure I know what you mean - if excel is set to create only one worksheet for each new workbook - why would it need to be set everytime? It's a set option just like file locations, standard font, etc.

If you mean that everyone that uses this export (and would only like 1 sheet) would have to make this setting too. then you are correct.

Eluia

Savatage wrote:

If you mean that everyone that uses this export (and would only like 1 sheet) would have to make this setting too. then you are correct.

I meant that, and you'd have to reset default after e new install too. It's better to be sure you always only make new workbooks with only one sheet in all cases.

As for the (-4167) parameter it's the value of an Excel VBA constant, xlWBATWorksheet. I'm quite new into NAV but have been developping word/excel/access apps for some time. The Add function of the Application.Workbooks collection can take a Template as pameter, if it's set then it adds the workbook with only one of the said Template, if no parameter is set it uses the value in the settings.

In the same way you could use the xlWBATChart (-4109) value to create a workbook containing only a Chart. The parameter can also be the name of an existing excel workbook, the new book is created on the template of the existing book.

The best way to learn about Excel objects is to go into excel VBA code (Alt+F11 from main excel window) and use the help on objects name. IT's really well made and helps start