Export Table data without using List Form

gadzilla1

Hello all,

I need to export table data for analysis (preferably to Excel) and cannot use a list form due to the need to see FlowFields.

Is there any functionality/reports that exist in standard Navision I can use as a starting point?


As an FYI, I was using list forms but am going away from this. See 2nd to last post in thread below:

viewtopic.php?t=19122&highlight=

Thanks - gad1

ajhvdb

You can use a dataport.

Savatage

Or a report - Here's a very rough report I threw together in 2 mins.

This will export Customer Ledger Entries to excel.
You can Filter the output by customer # or a date range or by a document type using the filters.

perhaps it can be a starting point - since I don't know the fields or table you are looking for.

I made it a fob for easy import.
http://savatage99.googlepages.com/Repor ... 2Excel.fob

<edit>Choose Preview NOT Print I didn't make it processing only.

in case it gets lost in translation somewhere
the Automation :xlSheet -> 'Microsoft Excel 11.0 Object Library'.Worksheet

Basis: http://www.mibuso.com/dlinfo.asp?FileID=596
thanks Mark!

nunomaia

One of the easiest way it’s preview a report such has customer list report, save has html, open directly that html in excel. You will see that information in Excel.

Savatage

But it doesn't have cool progress bars n' stuff

nunomaia

Savatage wrote:

But it doesn't have cool progress bars n' stuff

In that point I agree with you

gadzilla1

Thanks everyone!

Harry - This is really really cool. One LAST question to close this up, how do I get the filter value in parenthesis?

I'm assuming that it's in the following line:

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

So that, if I ran a report to Excel for the Customer table on date filter 1/1/07 to 3/1/07 the Excel tab would say:

Customer (1/1/07..3/1/07)

If filtered on Customer No. = TEST777 the Excel tab would say:

Customer (TEST777)

Thx - gad1

Savatage

Excel Tabs can be tricky

First off they only ley you enter 31 characters - else you get an error.
It doesn't like some special characters (:) for example - else you get an error.

You will have to DelChr the String to remove the characters Excel doesn't like before inserting the name.

Here's the quick steps:
1)View->Globals->TextConstants-> Change Text000 to Text0000
2)View->Globals->new Variable "Text000" length 250
3)OnPreDataItem Add Text000 := "Cust. Ledger Entry".GETFILTERS;
4)Youe Have to DELChr Text000 now to remove the charaters excel doesn't like and COPYSTR chop it at 31.
Add this to the end of the OnPreDataItem Section..Something Like

Text000 := "Cust. Ledger Entry".GETFILTERS;
Text000 := DELCHR(Text000,'=','-_+=\|[]}{".,#$@%^&*+!~`:;/?><'); 
Text000 := COPYSTR(Text000, 1, 31);

*You could probably rename Text000 to TabName to make it clearer what it's for.

I haven't tried it but i think it might be in the right direction.

Savatage

http://savatage99.googlepages.com/CLE_ExportTabTest.JPG



So using GETFILTERS does force in
Customer No.
Document Type
Posting Date

(in this example) which uses up alot of those 31 available spaces.
I guess an alternative would be to use a request form where you fill in the filters into a text box which then you have more control of.
:-k

Updated Ver2.0
-Processing only
-Filters Become The Tab Name
http://savatage99.googlepages.com/Repor ... el_2.0.fob

gadzilla1

Oops! I should have written sooner, I figured something out before your reply...thinking you'd take a bit to reply.

YEAH, Excel tabs are a little messed up...I got an error with the / \ character stuff, so I forced the records down 3 lines and added the filter as a header in the first row, like so:

FirstRow := 1;

Row := 3;

EnterCell(FirstRow, 2, DateFilter, TRUE, TRUE, TRUE);
EnterCell(Row, 1, Text001, TRUE, TRUE, TRUE);
EnterCell(Row, 2, Text002, TRUE, TRUE, TRUE);
EnterCell(Row, 3, Text003, TRUE, TRUE, TRUE);
EnterCell(Row, 4, Text004, TRUE, TRUE, TRUE);
EnterCell(Row, 5, Text005, TRUE, TRUE, TRUE);

Kind of a goofy way to do it but it works a-ok. I saw your January 2007 thread on Excel Buffer stuff and learned a lot. Cool cool stuff.

Thanks again, I'll put solved in there. gad1

Savatage

It's fun to play around with it. You learn something new everyday. Not sure perhaps that post was about exporting A/R aging to excel? That one was a trip.

The solution in the 2 posts above does work well when using 1 filter but quickly starts to run out of TAB space when using 2 or more.