Export Table data without using List Form

gadzilla1 · 2007-07-25

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 · 2007-07-25

You can use a dataport.

Savatage · 2007-07-25

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 · 2007-07-25

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 · 2007-07-25

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

nunomaia · 2007-07-25

Savatage wrote:

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

In that point I agree with you

gadzilla1 · 2007-07-26

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 · 2007-07-26

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 · 2007-07-26

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 · 2007-07-26

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 · 2007-07-26

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.

Export Table data without using List Form

Answers

Categories