Options

Export to Excel

sabzamsabzam Member Posts: 1,149
Dear all,

I need to export some data to an excel sheet but the fields are to retain certain validation options. For example when exporting customer ledger entries, the Document Type options are: ,Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund. I need that the resultant excel sheet retains this information and if the user goes to the field Document Type he can check the options available and choose another option.

Thanks in advance.

Comments

  • Options
    SavatageSavatage Member Posts: 7,142
    sabzam wrote:
    Dear all,

    I need to export some data to an excel sheet but the fields are to retain certain validation options. For example when exporting customer ledger entries, the Document Type options are: ,Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund. I need that the resultant excel sheet retains this information and if the user goes to the field Document Type he can check the options available and choose another option.

    Thanks in advance.

    A few things...
    Even if the options are Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund, the customer ledger entry that has been exported is just one of those. That entry has a specific Document Type.

    So even if you exported the data to excel and the doc type field was Changable as requested. Where does changing Invoice -> to Payment get you? Or any other option for that matter???

    IN excel it's done this way...The doc type field in excel
    Data->Validation
    Under Settings->Validation Criteria = LIST
    Source = Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund

    You can copy that down the column for each entry. Perhaps if you did it with a macro you can look at the marco & re-create it in the excel buffer.

    Strange request unless we're not getting the whole story? :-k
  • Options
    krikikriki Member, Moderator Posts: 9,098
    [Topic moved from 'NAV Three Tier' forum to 'NAV/Navision Classic Client' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    bangswitbangswit Member Posts: 265
    after successful export to excel, it usually open that excel file
    how to make after the export, the excel file doesn't opened automatically?
    thanks
  • Options
    julkifli33julkifli33 Member Posts: 1,078
    i have several data in sales line like these :

    Item No Quantity
    A001 10
    A002 5
    A003 11
    A001 10
    A004 15

    because Item A001 there are twice, so they should be accumulated become


    i want to export the data to excel become like this

    A001 20
    A002 5
    A003 11
    A004 15

    how to group them?
  • Options
    crisnicolascrisnicolas Member Posts: 177
    julkifli33 wrote:
    i have several data in sales line like these :

    Item No Quantity
    A001 10
    A002 5
    A003 11
    A001 10
    A004 15

    because Item A001 there are twice, so they should be accumulated become


    i want to export the data to excel become like this

    A001 20
    A002 5
    A003 11
    A004 15

    how to group them?

    This is not an export to excel question but a grouping question...
    It depends on where you are doing this, you can do it one way or another.

    If you are exporting using a report, you can use the grouping options that reports have to first group and then export.
    If you are doing this somewhere else, you'll have to first group yourself, then export.
    Depending on what exactly you are exporting, you could even use some of the flowfields on item card to get this sum of quantity on sales lines...
    Or... instead of inserting a new record on Excel Buffer every single time, before inserting, check if a record already exists on Excel Buffer for the actual Item, in that case, instead of inserting, modify the quantity Excel Buffer record...
  • Options
    julkifli33julkifli33 Member Posts: 1,078
    My code
            BEGIN
               //Fill Excel Header
               Row := 1;
               Window.OPEN(
               Text001 +
               '@1@@@@@@@@@@@@@@@@@@@@@\');
               Window.UPDATE(1,0);
               TotalRecNo := SL.COUNTAPPROX;
               RecNo :=0;
               TempExcelBuffer.DELETEALL;
               CLEAR(TempExcelBuffer);
               //Fill Excel Body
               REPEAT
                        SL.RESET;
                        SL.SETRANGE(SL."No.",Item."No.");
                        Row := Row + 1;
                       [b] tempitemno[i] := SL."No.";
                        tempquantity[i] := tempquantity[i] +SL.Quantity;[/b]
               
    
                        EnterCell(Row, 1, TempItemNo[i], FALSE, FALSE, FALSE);
                        EnterCell(Row, 4, FORMAT(tempquantity[i]), FALSE, FALSE, FALSE);
                        EnterCell(Row, 5, FORMAT(SL."Shipment Date"), FALSE, FALSE, FALSE);
                        EnterCell(Row, 6, SL."Sell-to Customer No.", FALSE, FALSE, FALSE);
                        EnterCell(Row, 10, FORMAT(SL."Requested Delivery Date"), FALSE, FALSE, FALSE);
                        EnterCell(Row, 11, FORMAT(SL."Variant Code"), FALSE, FALSE, FALSE);
                        EnterCell(Row, 12, FORMAT(SL."Description 2"), FALSE, FALSE, FALSE);
                        RecNo := RecNo + 1;
    UNTIL SL.NEXT = 0;
              Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
              Window.CLOSE;
              TempExcelBuffer.OpenBook(FORMAT(ExportOptions) + ' ' + FileName + ' - ' + Item."No."  + ' ' +
              FORMAT(TODAY,0,'<Year4><Month,2><Day,2>') + '.xls','Export');
              TempExcelBuffer.CreateSheet('Export','',COMPANYNAME,USERID);
              TempExcelBuffer.GiveUserControl;
    

    what i am thinking is like to create temporty item no and tempory quantity
    i already code like this
    but still failed :(
  • Options
    crisnicolascrisnicolas Member Posts: 177
    your REPEAT..UNTIL structure doesn't seem to be right... where is the initial FIND (or FINDSET)? why do you have a SETRANGE inside the REPEAT..UNTIL structure on the var you are actually looping through?

    What is tempitemno and tempquantity?
    How many dimensions those vars have? How do you know at design time how many items you'll export?

    You are not grouping anything like this, and besides, the EnterCell function is inside the same loop used for grouping... you are "exporting" before the grouping has been done.
    This is not going to work.

    Try something like this:

    IF SL.FINDSET THEN
    REPEAT
      tmpSL.SETRANGE("No.",SL."No.");
      IF tmpSL.FINDFIRST THEN
      BEGIN 
        tmpSL.Quantity := tmpSL.Quantity + SL.Quantity  //it will be better, though, if you sum "Quantity (Base)" instead of Quantity
        tmpSL.MODIFY;
      END
      ELSE
      BEGIN
        tmpSL := SL;
        tmpSL.INSERT;
      END;
    UNTIL SL.NEXT = 0;
    
    // The grouping is completely done at this time, now we can start exporting to excel
    
    IF tmpSL.FINDSET THEN
    REPEAT
       EnterCell(...)
       EnterCell(...)
       ...
    UNTIL tmpSL.NEXT = 0;
    
  • Options
    SavatageSavatage Member Posts: 7,142
    Whats easy to do is create a report using the wizard that groups the way you want it.
    then instead of adding your code to onaftergetrecord.
    I view the section that does the grouping and add the code to that section instead

    EnterCell(Row, 1, TempItemNo, FALSE, FALSE, FALSE);
    EnterCell(Row, 4, FORMAT(tempquantity), FALSE, FALSE, FALSE);
    EnterCell(Row, 5, FORMAT(SL."Shipment Date"), FALSE, FALSE, FALSE);
    EnterCell(Row, 6, SL."Sell-to Customer No.", FALSE, FALSE, FALSE);
    EnterCell(Row, 10, FORMAT(SL."Requested Delivery Date"), FALSE, FALSE, FALSE);
    EnterCell(Row, 11, FORMAT(SL."Variant Code"), FALSE, FALSE, FALSE);
    EnterCell(Row, 12, FORMAT(SL."Description 2"), FALSE, FALSE, FALSE);
    RecNo := RecNo + 1;
  • Options
    julkifli33julkifli33 Member Posts: 1,078
    i do not change the code
    but i already group it
    but it's still the same
  • Options
    crisnicolascrisnicolas Member Posts: 177
    julkifli33 wrote:
    i do not change the code
    but i already group it
    but it's still the same

    If you haven't changed the code, how do you expect to see any difference?
Sign In or Register to comment.