Inserting an ExcelSheet

Hi there,

I want to export a Report (e.g. Report 4 Detail Trial Balance)directly to Excel.

A new Excel Sheet is inserted BEFORE existing Sheets - it should however be inserted AFTERwards to keep the output in the same order as it is displayed on the report itself.

My Questions:

1. How can I manage this[?]
2. Can I use any MOVE-instruction like this:
XlWorkSheet.Move([VARIANT Before][, VARIANT After])
I haven't found out yet how it works...


Thanks in advance for your help.

Juergen
  • Hi,

    first, it would be better, if questions are posted in the develeper forum.

    This is from Excel - helpfile:
    
    
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)


    In Navision this should do the job:
    
    
    ..
    x := xlSheets.Count;
    // x = number of last sheet in workbook
    xlSheets.Add(,x);
    // add after last sheet
    ..


    Hope this helps


    bye

    Andre
  • Hi Andre,

    I tried to so with this source:


    NoOfSheets := XlWorkBook.Worksheets.Count;
    MESSAGE ('%1', NoOfSheets); //displays the correct number (1)of the last sheet
    XlWorkSheet := XlWorkBook.Worksheets.Add(NoOfSheets);
    XlWorkBook.Worksheets.Add(NoOfSheets);
    //I tried these instructions. both of them return a runtime-error like this:
    ... the ADD-Method of the sheet object dould not be executed.

    regards

    Juergen
  • Hi Juergen,

    sorry for answering late.
    I had a day off yesterday.

    I must confess I didn't tested my solution for
    this problem.
    I'll try to check it today and then I come back
    with a posting.

    bye

    Andre
  • Hi Juergen,

    unfortunately I didn't found a way to solve this problem [V].
    I've got always error messages from Excel.
    If you search Google Groups for 'Excel sheets.add' you will see
    many people with the same problem.

    Sorry

    Andre
  • Hy J├╝rgen

    My reports exporting data to excel always ;) need a Total sheet at the end.
    First you have to create the "Total-Sheet", than it's easy to create a new sheet at second-Last Position like this:

    Sheet:= Excel.Sheets.Item('Total');
    Sheet:= Excel.Sheets.Add(Sheet);
    Sheet.Name := Strsubstno(.....

    it's not a solution, but a way to avoid problems ;)

    Bye('Stefan')
  • hi,
    use this, it works

    create(xl);
    xl.visible:=true;
    xlworkbook:=xl.workbooks.add;

    xlsheet := xl.ActiveWorkbook.Sheets.Item(xl.ActiveWorkbook.Sheets.Count);
    xlsheet.Activate;
    xlsheet.name:='Detail Trail Balance';

Related
Recommended