Add sheets in Excel

Bart_Groot

Hello,

I'm trying to insert a new sheet in Excel. If I use the statement:

Sheets.Add

then a new sheet is inserted before the other sheets. It should however be possible to insert sheets after another sheet with the following syntax

[IDISPATCH Add :=] xlSheets.Add([VARIANT Before][, VARIANT After][, VARIANT Count][, VARIANT Type])

I tryed

xlSheets.Add(0,1,2,-4167);

but this results in the message that the call to the function add failed. Method add of class sheets failed.

Does anyone know how to insert a new sheet after the existing one?

JoeK

Hi,

if you want to do this, maybe thats the right way:

XlWksheet := XlSheets.Item(3); // The Sheet before the new should be added
XlWksheet := XlSheets.Add(XlWkSheet);

The Parameters Before and After expect Worksheets.

Hope i could help

Greetings

Bart_Groot

JoeK wrote:

Hi,

if you want to do this, maybe thats the right way:

XlWksheet := XlSheets.Item(3); // The Sheet before the new should be added
XlWksheet := XlSheets.Add(XlWkSheet);

The Parameters Before and After expect Worksheets.

Hope i could help

Greetings

Hi

This is not wat i'm looking for. I only have one sheet and I want to add one after this one. Trying your code (after changing Item(3) to item(1) results in a new sheet before the existing one.

Greetings

Bart

JoeK

Yes, i know, just remind me. I got the same problem. My solution was to add the sheet (before all other sheets) and after i filled all sheets move the sheets in the correct order, like this:

xlwksheet := xlsheets.item(1);
for L_Counter := 2 to xlsheets.count() do begin
  xlwksheet2 := xlsheets.item(L_Counter);
  xlwksheet2.move(xlwksheet);
  xlwksheet := xlwksheet2;
end;

Hope i could help now...

The problem with the add method of the worksheets is that the parameters are: Before (WkSheet) , After (WkSheet) this parameter are optional, this means that if u want to add the sheet after the other the first parameter should be empty. I don´t know how to do this in nav.

Greetings

IB

Try using like this:

CREATE(xlApp); //Excel.Application
xlApp.Workbooks.Open(excelfilename); //excelfilename : Text
xlWb := xlApp.ActiveWorkbook; //xlWb - Excel Workbok
xlSheetPlaceAfter:=xlWb.Worksheets.Item(Index); //from 1 to Worksheets count ; selecting sheet after which we want to place new sheet
xlWsht := xlWb.Worksheets.Add(); //creating new sheet
xlWsht.Name:='newsheet'; 
xlSheetPlaceAfter.Move(xlWsht); //Replacing places
xlWb.Save;
xlWb.Close();
CLEAR(xlApp);

if You want to change for example places of sheets 2 and 4 (indexes) (Sheet4 will be now on place Sheet2).
Numerical before:
1 2 3 4 5
Numerical after:
1 4 2 3 5

CREATE(xlApp); //Excel.Application
xlApp.Workbooks.Open(excelfilename); //excelfilename : Text
xlWb := xlApp.ActiveWorkbook; //xlWb - Excel Workbok
xlSheetPlaceAfter:=xlWb.Worksheets.Item(4); //from 1 to Worksheets count
xlWsht:=xlWb.Worksheets.Item(2);
xlSheetPlaceAfter.Move(xlWsht); //Replacing places
xlWb.Save;
xlWb.Close();
CLEAR(xlApp);

About Native Move usage, there are no Type.Missing type in Navision, so this thing I think won't work. Another way is to do with macros but...:)
Hope this helps.
Best wishes,
IB

Bart_Groot

Thanx for all the advise. :-)

Bart Groot