Add Sheet AFTER/BEFORE current one?

Business Central/NAV Developers Forum
1

Hello, I already looked here in this forum to get a solution to insert a new sheet to the current workbook, but nothing found! The Navision help (use f(x)) on xlBook.Worksheets.Add shows: [IDISPATCH Add :=] xlWorkSheets.Add([VARIANT Before][, VARIANT After][, VARIANT Count][, VARIANT Type]) If I use: xlBook.Worksheets.Add(’’, 1, 1, -4167); or xlBook.Worksheets.Add(0, 1, 1, -4167); I get an error message saying: method Add could not be executed. It only works if no parameter is given like this: xlBook.Worksheets.Add; But then the new Sheet appears before current one and this I don’t want. Any ideas? PS: Does anybody know how to handle optional parameters in Navision? e.g: …Add(NoParam, 1, 1, -4167) how to NOT enter a optional Parameter in Navision, do I have to give ‘’ or Zero??? Thanks for any help!

2

Syntax expression.Add(Before, After, Count, Type) expression Required. An expression that returns a Worksheets object. Before Optional Variant. An object that specifies the sheet before which the new sheet is added. After Optional Variant. An object that specifies the sheet after which the new sheet is added. Count Optional Variant. The number of sheets to be added. The default value is one. Type Optional Variant. The sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. The default value is xlWorksheet. Remarks If Before and After are both omitted, the new sheet is inserted before the active sheet.

3

This example adds a new worksheet after the last worksheet in the active workbook (in VB). Worksheets.Add.Move after:=Worksheets(Worksheets.Count) Good luck [:D]

4

Hi M [;)] We discussed this problem some months ago in another thread. Unfortunately we didn’t find a proper solution [:(]. bye André

5

quote:

Originally posted by dr.burns
PS: Does anybody know how to handle optional parameters in Navision? e.g: …Add(NoParam, 1, 1, -4167) how to NOT enter a optional Parameter in Navision, do I have to give ‘’ or Zero???

You can omit optional parameters only at the end of the parameter list, i.e. if foo(a, b, c) is a function with optional parameters, you may call it as foo(a, b) or foo(a), but not as foo(b, c) or foo(a, c) etc. You have to provide “null values” for parameters you do not want to pass.

6

Hello, I am NOT IN VB!!! I am in Navision, and I know how to open the VB help! But In Navison I have the problem not in VB!!!

7

quote:

Originally posted by dr.burns
… I am NOT IN VB!!! I am in Navision, and I know how to open the VB help! …

Keep cool [;)]. Almost all excel automation functions are working like in VBA. Except passing variables. So - the look into the VB- help is often helpful for coding in Navision. bye André

8

Yeah, that’s right. I’ve always used VB help when exported/imported data to/from excel and drawed charts in excel. It always helped!!!

9

Hi I played a little with automation and sheets at the weekend. Here is the result: **//Current last sheet will be deleted at the end of code!** x := xlBook.Worksheets.Count; xlSheet := xlBook.Worksheets.Item(x); IF x > 1 THEN xlSheet.Activate; xlSheet.Name := 'Last'; xlSheet := xlBook.Worksheets.Item(1); xlSheet.Activate; //Fill the sheet i:=1; REPEAT IF (x > 1) OR (i > 1) THEN xlSheet := xlSheet.Next; xlSheet.Activate; IF xlSheet.Name = 'Last' THEN BEGIN xlSheet := xlBook.Worksheets.Add(); xlSheet.Name:= 'New' + FORMAT(i) ; //for testing only xlSheet.Activate; END; // fill the sheets //for testing data xlRange:= xlSheet.Range('A1'); xlRange.Activate; xlRange.Value:= 'New Table' + FORMAT(i); // i:= i +1; UNTIL i = 15; // 15 new sheets x := xlBook.Worksheets.Count; xlSheet := xlBook.Worksheets.Item(x); xlSheet.Activate; xlSheet.Delete; bye André