Export to Excel

sabzam

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.

Savatage

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

kriki

[Topic moved from 'NAV Three Tier' forum to 'NAV/Navision Classic Client' forum]

bangswit

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

julkifli33

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?

crisnicolas

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...

julkifli33

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

crisnicolas

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;

Savatage

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;

julkifli33

i do not change the code
but i already group it
but it's still the same

crisnicolas

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?