export to excel

hai there…
is it possible if we want to export the data to excel?
like we create report than preview so the data will appear in the excel worksheet
how to make it?
thanx

Hi, if its simply data from a table that you want to export to Excel then why not create a DataPort. You can secify which fields you want and the order you want them in. Once the Dataport has run you can open the file in Excel…Paul [:D]

hehe ok… yes you right
but something i forgot to tell… (sorry :P)
what i want is not to export like the ordinary export, but create a excel report
so after we give the parameter like for example : salesperson code : AA
and then automatically preview in excel only for salesperson code : AA

You can get a pretty good idea of what to do if you read the code that is used in Analisys by dimensions, export to excell. Here things are exported using filters/pivots etc.

However, this may be too complex.

I have created many reports that output to Excel without trying to add formatting or pivot tables.

It is really simple.
Contact me by email and I will send you an example.

Rgds,
Colin

Take a look at table 370, and then how to implement it can be seen on report 29, 81, 82, 7113, 7132 and 11605.

/TH

i could export
but only if type data = text
if decimal it will be appear error message

this is my code

Documentation()

Item - OnPreDataItem()
Window.OPEN(
‘Says :’ + Text001 +
@1@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
TotalRecNo := Item.COUNTAPPROX;
RecNo :=0;

TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);
EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
EnterCell(3, 1, t00, TRUE, TRUE, FALSE);
EnterCell(3, 2, t01, TRUE, TRUE, FALSE);
EnterCell(3, 3, t02, TRUE,TRUE,FALSE);
EnterCell(3, 4, t03, TRUE,TRUE,FALSE);
EnterCell(3, 5, t04, TRUE,TRUE,FALSE);
EnterCell(3, 6, t05, TRUE,TRUE,FALSE);
EnterCell(3, 7, t06, TRUE,TRUE,FALSE);
EnterCell(3, 8, t07, TRUE,TRUE,FALSE);
EnterCell(3, 9, t08, TRUE,TRUE,FALSE);
EnterCell(3, 10, t09, TRUE,TRUE,FALSE);
EnterCell(3, 11, t10, TRUE,TRUE,FALSE);
Row := 3;

Item - OnAfterGetRecord()
Row := Row + 1;

EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);
EnterCell(Row, 4, “Unit Cost”, FALSE, FALSE, FALSE); —> this is the problem, because unit cost is decimal data type
EnterFilterInCell(GETFILTER(“Unit Cost”),FIELDCAPTION(“Unit Cost”));

RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

Item - OnPostDataItem()
Window.CLOSE;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;

EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;

EnterCell(Row, 4, FORMAT(“Unit Cost”), FALSE, FALSE, FALSE) [:)]

Use explicity casting as adviced by Anna above. Note that there are only a very few data types that implicitly cast in Navision.

Search in the help for the Format command, there are some cool things you can do with it by using parameters in Carrots < >

EnterCell(Row, 4, FORMAT(“Unit Cost”), FALSE, FALSE, FALSE)

Still the same
there’s an error message said
You have Specified an unknown variable
Unit Cost
Define the variable under 'Global C/AL sysmbols

by the way if we want to export from navision direct to excel, isi possible using dataport?
because while i’m using dataport, the data is mergin in 1 field altogether… :frowning:
i must export to txt first and then import using excel, the txt file i have export first from navision

form?report?
ID number please?

You can use a dataport to export to Excel (sort of).

  1. Make sure the Dataport is tab delimited.

  2. Make sure the filename ends in .txt.

  3. Run the dataport

  4. Browse to the file, right click, Open With, Micorsoft Office Excel

By doing it this way Excel skips the whole import wizard and opens the file directly!

(The default in Excel is to import based on tab delimted so if you haven’t changed this then it will work. If it doesn’t work then email at jwilder@stonewallkithcen.com and I will tell you how to fix it)

Easiest would prolly be report 7113.

Advanced one is CU 424

Stan,

the field “Unit Cost” is in the item table as per standard. Has someone renamed it?

Have you tried hitting F5 and then going to the “Item” in the first column, “Fieldname” in the second column, and then finding “Unit Cost” in the third column?

If you export to text, copy and paste the object in here, or PM me I will take a look.

Regards

/TH

this the code :


Item - OnAfterGetRecord()
Row := Row + 1;
EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);
EnterCell(Row, 4, FORMAT(“Unit Cost”),TRUE,TRUE,TRUE);
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));


the bold one is the problem
I’m sure the table name in database is not renamed
because the problem is if I use the data type decimal like : Unit Cost, Unit Price, Standard Cost, Last Direct Cost, etc
nevertheless if I use the data type varchar like No., Description, Vendor No. etc, it can be done successfully

by the way I export it to excel, not to txt

Hey Stan,

Can you export the object to text. Like you export to Fob but to Text instead, its in the dropdown “Save as Type”. Object Designer, highlight object., file, export (heres where you save as text)

Open it in a text editor and then copy and paste it into a message here. That way I can get the same exact object as you have.

/TH

OBJECT Report 50021 Export Item By Location
{
OBJECT-PROPERTIES
{
Date=13/02/03;
Time=10:02:14;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table27;
OnPreDataItem=BEGIN
Window.OPEN(
‘Stanley Bilang :’ + Text001 +
@1@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
TotalRecNo := Item.COUNTAPPROX;
RecNo :=0;

TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);
EnterCell(1, 1, Text000, TRUE, TRUE, FALSE);
EnterCell(3, 1, t00, TRUE, TRUE, FALSE);
EnterCell(3, 2, t01, TRUE, TRUE, FALSE);
EnterCell(3, 3, t02, TRUE,TRUE,FALSE);
EnterCell(3, 4, t03, TRUE,TRUE,FALSE);
EnterCell(3, 5, t04, TRUE,TRUE,FALSE);
EnterCell(3, 6, t05, TRUE,TRUE,FALSE);
EnterCell(3, 7, t06, TRUE,TRUE,FALSE);
EnterCell(3, 8, t07, TRUE,TRUE,FALSE);
EnterCell(3, 9, t08, TRUE,TRUE,FALSE);
EnterCell(3, 10, t09, TRUE,TRUE,FALSE);
EnterCell(3, 11, t10, TRUE,TRUE,FALSE);
Row := 3;
END;

OnAfterGetRecord=BEGIN
Row := Row + 1;

EnterCell(Row, 1, “NKS Lama”, FALSE, FALSE, FALSE);
EnterCell(Row, 2, “No.”, FALSE, FALSE, FALSE);
EnterCell(Row, 3, Description, FALSE, FALSE, FALSE);

RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
END;

OnPostDataItem=BEGIN
Window.CLOSE;

TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;
END;

}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
{ PROPERTIES
{
DataItemTable=Table32;
DataItemTableView=SORTING(Item No.,Entry Type,Variant Code,Drop Shipment,Location Code,Posting Date)
WHERE(Location Code=CONST(GD));
DataItemVarName=GD;
DataItemLinkReference=Item;
DataItemLink=Item No.=FIELD(No.),
Posting Date=FIELD(Date Filter);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
TempExcelBuffer@1000000004 : TEMPORARY Record 370;
Window@1000000003 : Dialog;
Row@1000000002 : Integer;
TotalRecNo@1000000001 : Integer;
RecNo@1000000000 : Integer;
Text000@1000000009 : TextConst ‘ENU=Daftar Item BY Location’;
Text001@1000000008 : TextConst ‘ENU=Analisa Data…’;
t00@1000000007 : TextConst ‘ENU=NKS Lama’;
t01@1000000006 : TextConst ‘ENU=NKS’;
t02@1000000005 : TextConst ‘ENU=Description’;
t03@1000000010 : TextConst ‘ENU=Harga’;
t04@1000000011 : TextConst ‘ENU=GD’;
t05@1000000012 : TextConst ‘ENU=DO’;
t06@1000000013 : TextConst ‘ENU=SEWA’;
t07@1000000014 : TextConst ‘ENU=EXT’;
t08@1000000015 : TextConst ‘ENU=BPDI’;
t09@1000000016 : TextConst ‘ENU=DO PENDING’;
t10@1000000017 : TextConst ‘ENU=TOTAL’;

PROCEDURE EnterCell@1000000000(RowNo@1000000005 : Integer;ColumnNo@1000000004 : Integer;CellValue@1000000003 : Text[250];Bold@1000000002 : Boolean;Italic@1000000001 : Boolean;UnderLine@1000000000 : Boolean);
BEGIN
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE(“Row No.”,RowNo);
TempExcelBuffer.VALIDATE(“Column No.”,ColumnNo);
TempExcelBuffer.“Cell Value as Text” := CellValue;
TempExcelBuffer.Formula := ‘’;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;
END;

BEGIN
END.
}
}


thanks a lot Tony!

I’m just heading out climbing, so I will get back to this tomorrow.

/TH

ok then…thanx a lot! :slight_smile: