Automation and Excel

Cannikin

Hey guys,

So I'm reading about these "Automation Variables" and getting stuff from Navision right into Excel. What is the Automation package I'm reading about? Is it a module?

Right now, if we want something in Excel, we run a report, save it as HTML, copy and paste it into Excel! Pretty bad, huh? Any help would be appreciated!

Rob

Savatage

Wow the number of hits from search the forum for "Excel Automation" is incredible.

did you check this one out?
http://www.mibuso.com/forum/viewtopic.php?t=927&highlight=excel+automation

Other things you might be interested in are:

If you really want to get easy use on Navision Data with Excel
http://www.jetreports.com/

Some free downloads from mibuso

Export Navision Table Data PRO
http://www.mibuso.com/dlinfo.asp?FileID=350
Quickly export desired Navision data from any table to TXT file.
1. Select table.
2. Select any of fields (All,None,Togle,Manual).
3. Set filters (for any field).
4. Export to txt.

Other features:
- Include row with FieldID, FieldName, FieldType.
- TAB or ; separated.
- Quick tips/help included.
- 2 forms only.

Excel Column Exporter v2.1
http://www.mibuso.com/dlinfo.asp?FileID=207
Choose fields of any table to export to Excel.

Version 08/10/2003:
Now includes objects both for MBS-Navision 3.60 & MBS-Navision 3.70

Version 2.1:
Navision Attain Importer/ Exporter tool now also available for NA 3.70

Tree functions were added:
- Initialize tables on table board for a new company
- Delete selected fields information
- Delete records

Version 2.0:
In the first version, it was possible to export information from any inicializate table of Navision Attain to an Excel Sheet, by selecting fields on Fields subform board of Form 50990 - Export Excel.

Now, in this second version, it's also possible:
- Define line filters before export to Excel
- Save Fields/Line filters configuration and restore it
- Prepare excel files before import
- Import to Navision an Excel sheet after define a table/fields to import

Cannikin

Thanks Savatage! Now I have to try and convince them to let me install this stuff. Do you know anything about this Automation stuff?

Savatage

Wow the number of hits from search the forum for "Excel Automation" is incredible.

did you check this one out?
http://www.mibuso.com/forum/viewtopic.p ... automation

I always install new stuff on a separate copy of the database on my own PC first. Just to see if these programs do what I am looking for them to do.

Cannikin

I really need something that let's me output fields based on some logic behind the scenes. For example if I want to calculate a value and have that populate a new column in an Excel file. It looks like those Mibuso downloads only let me export raw chunks of data, not do any kind of logic on the values behind the scenes.

Cannikin

Savatage wrote:

Wow the number of hits from search the forum for "Excel Automation" is incredible.

did you check this one out?
http://www.mibuso.com/forum/viewtopic.p ... automation

I always install new stuff on a separate copy of the database on my own PC first. Just to see if these programs do what I am looking for them to do.

Yeah I did a search, but it seems like they're all dealing with specific problems someone is having getting a field to export or coding errors. I just want a general overview on what this whole Automation thing even is. Or can I just copy and paste that dude's code and be able to export to Excel?

Savatage

When I want to do something like that - I use the G/L->Account Schedules.

I recently got into using this and It's great after you get the hang of it.

Setup the info I'm looking for. Goto->Account Schedule->Overview
& From there I Can Choose Function->Export Acct Schedule to Excel.

It really depends on what you are tying to do.

You can also look at the code on Report #29 I think it is.

Cannikin

Export to Excel doesn't show up for me. The only options I have are "Insert Accoutns..." and "Setup Column Layouts..." You must have some different options/license stuff that we don't have. Man, we got screwed on our implementation.

Savatage

Export to excel only shows on the form after you have created an Account Schedule & View The output.

Use object designer to view Form 490 (for us it's called Account Schedule Overview)

& if you right click on the Function button & look at the menu items. The option we have is : Export Acc. Schedule to Excel

I printed all the Account Schedule oinfo from the online help - plus i searched all the forums for added info. It works for us.

Cannikin

Nice dude, it worked!

But now what if I want to create an Excel file from any data that I want within Navision? Similar to the report designer, I want to have looping, calculations going on behind the scenes, etc. Then when everything is said and done, it's in Excel.

Or are you saying I can just dig through the code and figure it out? I was hoping someone had a nice guide written for me. hehehe

Savatage

The AS is more for looking at Numbers from the G/L

if you want to export say a customer list there are other ways of doing that.

Figuring most Automation was more time than I'm willing to commit to.
I can create any report I want using Crystal Reports. & Crystal has a built in Export feature that allows me to export any report into
Excel
Word
PDF
TXT
CSV
HTML
and about 10 more ways
If only Navision Report had that kind of functionality [-o<

You can view this doc file so see a sample Account Schedule Report
http://www.geocities.com/navision_attain/downloads/TestAccSch.doc

Are we way off topic now? Sorry

Cannikin

How do you get the data from Navision to Crystal?

I thought I saw a post from someone where using the Automation Server let you open up Excel/Word files and write to them from Navision. Basically you define where everything is going, what column, what row, etc. I assumed it worked for ANY data that you had access to from Navision. Was I completely off?

Rob

Savatage

Crystal gets the data right from the Tables using the C/ODBC.

Then you can create anything you want.
Much more user friendly than the Navision Report Writer.
When you create a new report :
1)You select the table(s) you need the data from
2)Link them
3)Drag & drop the fields into the report or create your own
ex Profit = Sales($) - Cost($)
4)Easily Total anything
5)Page Numbering is a snap
6)Create your own report parameters ex/Start Date - End Date

& On & On

I can create a detailed report in Crystal in 5 Minutes & using the Navision Report Designer it might take an hour.
Some people say speed is an issue when generating the report & 99% of the time it's not noticable.

& I can create as many reports as I want not just 100 - having to save using the range 50000 to 50099 that Navision gives you (yes you can BUY more).

That's my opinion & I sure there are some on both sides of the fence.

http://www.businessobjects.com/products/reporting/crystalreports/default.asp
& plenty of documentation of how to do things
ex/ http://support.businessobjects.com/comm ... _excel.pdf

Charleh

Cannikin, assuming you are familiar with C/AL (and have the required license) you can have a play with the automation and get familiar with it.
The best table to look at is 370 (Excel Buffer) as this pretty much handles most of exporting to Excel for you.

Automation variables are simply pointers to objects in automation libraries which you can define in your C/AL code. For instance, in your case, you would want to create a few automation variables to handle your Excel exports that would point to certain objects within the Microsoft Office xx.x Library (x being the version of the dll you have on the system) such as the Application object, the Workbook object and the Worksheet object. Once these vars are declared you can manipulate most of the functionality of the application via their methods and properties.

You don't really need to worry about setting these up though if you use the Excel Buffer table. This table allows you to insert rows of data (any specific data you choose) and then will automatically handle the building of the spreadsheet for you.

I'ts as simple as this:-

Pseudocode:

- Define a variable to point to the Excel Buffer
- Clear the Excel Buffer 
- Add rows/columns to the buffer
- Use the tables functions CreateBook(); CreateSheet(); and GiveUserControl() to finalise;
- Clean up

Clearing the Excel Buffer is optional, it's usually best to do so just in case some data is left over from another transaction, but it's good practice to clear the data after your function has completed anyway (you could use temporary tables for this)

The last 3 functions simply create the workbook and populate the worksheet with the data, and then make Excel visible and give the focus to it.

This method will also create a scroll bar on screen notifying the user of how much of the export is completed.

If you haven't found a solution yourself yet I can post you some example code.

Cannikin

Charleh,

Thanks man! Any example code would be extremely helpful. I'm still learning C/AL as I go, and since there's no real documentation anywhere, it's not all that fun. F1 is my best friend.

So the Automation stuff isn't a portal or anything that we need to purchase, it's already part of the system?

Also, does this let you insert data into an existing spreadsheet or template? Let's say I have a sheet that has all kinds of fancy calculations in it, can I just insert data into columns A and B and then the equasions defined in column C will act like normal?

Cannikin

Savatage,

I tried using their ODBC driver with SQL Server's Query Builder and it seemed to work okay, but I couldn't access tables with spaces in their names. Does Crystal handle those okay?

That would be a SWEET solution if I could get it to work ... hopefully they have a trial version I can download!

Savatage

I believe they do have a trial version.
http://www.businessobjects.com/products ... wnload.asp

We connect using C\odbc. Which needs to be in your licence.
(Granule ID 1,700)

Not sure about SQL needing c/odbc maybe someone else know - we don't use SQL

Click this download to see how my table choices look
http://www.geocities.com/navision_attai ... Sample.doc

Charleh

Hi Cannikin,

This automation is just part of windows, you can plug into a lot of different programs that contain automation libraries - it's just as if the user is clicking the mouse and manipulating data.

Yes you can insert data into a formula rich spreadsheet template, you can also add any formula you want to a cell within the C/AL code (it's all handled by the Excel Buffer too if you don't want to get your hands dirty)

Heres an example with code, we will not base this on any tables just for simplicity:

Create an empty test form (give it a name when you save it 'Excel Test' will do)

Add a button to the form and add any caption you want (I labelled mine 'Test Export') then call this control 'TestButton'

You will also need a global variable that holds a reference to the Excel Buffer table. I've created mine as..

grecExcelBuffer - Record - "Excel Buffer"

Now add this code to the onpush event for the button..

TestButton - OnPush()
grecExcelBuffer.DELETEALL; // clear excel buffer
AddToExcel(1, 1, 'This is a test', TRUE); // add some data to row 1, col 1 and make the cell BOLD
AddToExcel(2, 1, 'This is on the 2nd row', FALSE); // add more data etc..
AddToExcel(1, 2, 'This is in the 2nd column', FALSE);
AddToExcel(2, 2, '..and this is in the 2nd column 2nd row, you get the picture!', FALSE);
grecExcelBuffer.CreateBook(); // Create the excel app and make a book
grecExcelBuffer.CreateSheet('Excel Test', 'Header', COMPANYNAME, USERID); // put all the data in the excel sheet
grecExcelBuffer.GiveUserControl(); // make excel visible and give it focus

And create this function which handles adding data to the Excel Buffer table..

AddToExcel(lintRow : Integer;lintCol : Integer;ltxtCellValue : Text[250];lblnBold : Boolean)
grecExcelBuffer.VALIDATE("Column No.", lintCol); // validate data passed via function
grecExcelBuffer.VALIDATE("Row No.", lintRow);
grecExcelBuffer.VALIDATE("Bold", lblnBold);
grecExcelBuffer.VALIDATE("Cell Value As Text", ltxtCellValue);
grecExcelBuffer.INSERT(); // insert the data into the table

Now compile and run the form and it should pop up Excel with your data in it!

If you want to see some of the other methods and properties of the Excel Buffer just go back into the C/AL code for your form then hit F5 to bring up the symbols menu, you can click on your global variable (grecExcelBuffer) that points to the Excel Buffer table and view all it's methods/properties. You could also browse the code in the Excel Buffer table if you wish.

Any problems just let me know!

Cannikin

Thanks Charleh!

Now the only problem is that I don't know how to do half the stuff in your walkthrough. LOL I can't bring up the code behind a button (which is where I assume the OnPush handler is), the C/AL Globals list is greyed out when I'm looking at a form, and I don't know where to define new functions at.

I really haven't had any development training on this system at all, I've just had to figure everything out on my own. Are there any manuals on the CD that can help me out? The stuff you're talking about sounds just like what we need, I just have to figure out how to get there.

Cannikin

Savatage wrote:

I believe they do have a trial version.
http://www.businessobjects.com/products ... wnload.asp

We connect using C\odbc. Which needs to be in your licence.
(Granule ID 1,700)

Not sure about SQL needing c/odbc maybe someone else know - we don't use SQL

Click this download to see how my table choices look
www.geocities.com/navision_attain/Cryst ... Sample.doc

Thanks man, I'm installing the trial version as we speak. Now I just have to get the Navision CD from the IT department. We just moved buildings this past weekend so their discs are everywhere. Hopefully tomorrow they said.

Savatage

Do you have a developers license? Without it you will not be able to see all the code...unless it's a report or form you design yourself.

here are some downloads that might help you.

http://www.geocities.com/navision_attain

Cannikin

Savatage wrote:

Do you have a developers license? Without it you will not be able to see all the code...unless it's a report or form you design yourself.

here are some downloads that might help you.

http://www.geocities.com/navision_attain

Ahhhhhhhhh ... that's probably why. I was wondering why I wasn't able to access code and/or variables in certain places. I suppose a developer license costs pretty big bucks, huh?

We had a WebEx demo with the folks from Jet Reports today -- it looks pretty good! Looks like it's simple enough for the managers around here to use. I'm also playing with Crystal Reports right now. Maybe I'll use that as my own interface and let these guys play with JR. Thanks again for the help in this thread!

Charleh

You should still be able to see the code associated with your form, I'm assuming you have a form designer license, this allows you to create your own forms and edit them, as long as you save them within your licensed forms number range. You cannot, however, view code or edit code from other forms.

C/AL code is stored in loads of places within a form so there will be code embedded in the form itself. If you just click on the 'New' button in the 'Forms' tab of the Object Designer (F12) you should get the form creation wizard up, if you click on the 'Create Blank Form' option and hit OK it should bring you up a new form, in which you can press F9 to view the code. If not then I'm afraid your stuck :P but good luck anyway - and yes if you aren't planning to use a developers license much (for professional development for example) then there isn't much point in getting one.

Faulcon

We have a number of reports that have an option to print to excel here, I can send you one of the objects if you would like. Here is the basic process though.

First put a checkbox on the request options form to indicate whether you want to export to excel or not, say bExportExcel.

You'll need to create some global variables (like that boolean one from the request options form), especially an automation object "'Microsoft Excel 10.0 Object Library'.Worksheet" which I've called ExcelWorkSheet. I also have a boolean for bold, a text for the current text to be passed, and an integer for the row number.

Then in the OnPreDataItem for your first item you'll want something like this (the formatting is just how this particular report was setup):

IF bExportExcel THEN
BEGIN
  CLEAR(ExcelApplication);
  CLEAR(ExcelSheet);
  CLEAR(ExcelWorkSheet);

  CREATE(ExcelApplication,TRUE);
  ExcelApplication.Visible := TRUE;
  ExcelApplication.Workbooks.Add;
  ExcelSheet := ExcelApplication.ActiveWorkbook.Worksheets;

  ExcelWorkSheet := ExcelSheet.Item('Sheet1');
  ExcelWorkSheet.Name := '<Report Name>';

  ExcelWorkSheet.Range('A1').Value := '<Report Heading>';
  ExcelWorkSheet.Range('A1').Font.Bold := TRUE;
  ExcelWorkSheet.Range('A2').Value := COMPANYNAME;
  ExcelWorkSheet.Range('G1').Value := FORMAT(TODAY,0,4);
  ExcelWorkSheet.Range('G2').Value := USERID;

  ExcelWorkSheet.Range('A5').Value := 'Filter One:';
  ExcelWorkSheet.Range('A6').Value := 'Filter Two:';
  ExcelWorkSheet.Range('A7').Value := 'Filter Three:';

  ExcelWorkSheet.Range('B5').Value := vFirstFilter;
  ExcelWorkSheet.Range('B6').Value := vSecondFilter;
  ExcelWorkSheet.Range('B7').Value := vThirdFilter;

  ExcelWorkSheet.Range('A9').Value := 'Column 1 Header';
  ExcelWorkSheet.Range('A5:A9').Font.Bold := TRUE;
  ExcelWorkSheet.Range('B9').Value := 'Column 2 Header';
  ExcelWorkSheet.Range('C9').Value := 'Column 3 Header';
  ExcelWorkSheet.Range('D9').Value := 'Column 4 Header';
  ExcelWorkSheet.Range('B9:D9').Font.Bold := TRUE;

  i := 9;
END;

That gets the header sections done, then the meat is in a function you create with the requisite number of paramters, one for each column you want to fill, eg

ExcelLine(lcolumn1 : type;lcolumn2 : type;lcolumn3 : type;lcolumn4 : type)

IF bExportExcel THEN
  i += 1;
  ExcelWorkSheet.Range('A'+FORMAT(i)).Value := lcolumn1;
  ExcelWorkSheet.Range('B'+FORMAT(i)).Value := lcolumn2;
  ExcelWorkSheet.Range('C'+FORMAT(i)).Value := lcolumn3;
  ExcelWorkSheet.Range('D'+FORMAT(i)).Value := lcolumn4;
  ExcelWorkSheet.Range('A'+FORMAT(i)+':D'+FORMAT(i)).Font.Bold := vBold;
END;

Finally, under view->sections, you'll want to put this in the body section (or whichever section you use to print a line)

IF CurrReport.SHOWOUTPUT THEN
//Only want it in excel if it would be printed on the report
BEGIN
  vBold := FALSE;
//Or TRUE if you want bold
  ExcelLine(var1,var2,var3,var4);
END;

The ExcelLine function can be duplicated as something like ExcelFooter if a different format is desired for a footer section, and the header section can be broken up if part of it might want to be repeated.

Hope that helps somewhat.

S0918423470

Hello,
I have a question. I have a report that uses Export To Excel. My problem is that the client want to Export to excel without Preview the report.
But as far as i know when you create a new report if you choose property
: ProcessingOnly := no , you always Preview the report anh then Export To Excel . If that property is Yes , the report Must Not have section .
I dont want to make another report the same old report to do that.
How can i do in this case ?

Faulcon

There's no real need to put the excel call in the sections part of a report, this is just normally the most convenient as it takes care of all the looping for various groupings. The excel call could be just as easily made from within the data item code where you work out what you want to show (presumably OnAfterGetRecord).