The original piece of this code did not take much time to develop. But it took me more than six months to post it to the board :).

After I worked on it initially, I never went back to it. My users are happy with it. They have asked for improvements which were part of my initial design scope. Never got around to coding that stuff. Hopefully there are enthusiasts amongst you who will finish the project.

Things to do:
Add Type-3 report support
Create output file in native excel format. To get specs on Excel and other spreadsheet/database program file formats go to www.wotsit.org



|******************************************************************************
|* zusfwexcel 0 VRC B50C b dev
|* Title : BaanXL
|* Author : Vamsi Potluru
|* Co-Author : Praveen Ambekar
|* Date : 2001-12-17 [17:17]
|******************************************************************************
|* License :
|* Copyright 2001 by Vamsi Potluru
|*
|* All Rights Reserved
|*
|* Permission to use, copy, modify, and distribute this software and its
|* documentation for any purpose and without fee is hereby granted,
|* provided that the above copyright notice appear in all copies and that
|* both that copyright notice and this permission notice appear in
|* supporting documentation.
|*
|* Vamsi Potluru DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS
|* SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
|* AND FITNESS, IN NO EVENT SHALL Vamsi Potluru BE LIABLE FOR ANY
|* SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
|* WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
|* WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER
|* TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE
|* OR PERFORMANCE OF THIS SOFTWARE.
|******************************************************************************

long ret | general return variable

extern domain tcmcs.str215 input.file | input file - created by Baan
extern domain tcmcs.str215 argument

function main()
{
input.file = creat.tmp.file$(bse.tmp.dir$())
argument = argv$(3)

wait.and.activate("ttstpconv", argv$(1), input.file, "ASCII", argv$(4))
start.session(MODAL,"zusfw1400m000","","")
ret = seq.unlink(input.file)
}
|*********************************** end of script ****************************


I have a user manual for this one at work. Expect it when I post it :)

I have posted the user manual below. 19th July, 2002 8:35 AM PDT

Attached is the file for creating the forms. Couple of them are painful :).

Here is form 1.

Read the form definitions carefully. What you see as "Save Defaults" and "Get Defaults" are not standard Baan options, but user options. They link to custom functions.

Here is form 2. This form is here for future options to be added. This will enable data in the before.fields in type-3 reports to be made part of the columns. Any takers?

Here is form 3

Here is form 4

Setup two devices as shown below:
The path argument can also be filled. Choose a central directory where you want the config files to be stored. If you leave it blank like shown in the pics below, each user will have their own configs.

Do not play with the pagelength settings. If I remember correctly they are hard-coded in the script.

Here is the User guide to BaanXL.

Enjoy!

Dear Mr. Vamsi Potluru,

With great interest I read your article about your product BaanXL at
www.baanboard.com. It sounds realy good but I would have some questions for
which I'm sure you can give me the answers:
1) When I am using the logical BAANXL-Printer for the first time, I will
have to state all the necessary information (as shown in your manual).
Finally I have to save these defaults for this one report. Will every user
now be able to print this report without stating all the necessary datas, or
does the saving of the defaults apply only for me?

2) Where are all these settings stored? (Is the report going to be changed?
Are the informations stored in a special table?)

3) Do I understand you correctly when I repeat the implementation like this:
I have to create the forms like given in your "forms.txt", I need two
scripts (zusfw1400 and zusfwexcel) and finally I would have to create one
session, called zusfw1400m000 (Format Data for Excel). And (not to forget)
of course I have to declare one Printer.

In advance many thank's for your request and your efforts!

best regards
Juergen Strasser

Juergen,

When the BaanXL device is created if you do not specify a directory then the defaults would be per user login. They then stored in the user's home directory. I would suspect that you will want the defaults company wide. In this case please create a directory under $BSE named baanxl. In the device for arguments use BAANXL, ADMIN, ${BSE}/baanxl
See above answer
Correct to the part that you will need to create a session, four forms, two scripts. You will need to create atleast two devices. One called BAANXL and the other BAANXL-ADMIN (these two devices should ideally have the ${BSE}/baanxl. Optionally you can create two more devices named MYBAANXL and MYBAANXL-ADMIN. These devices will not have the directory specified and so can have user specific options.

Hi,

I have received a PM about this script not working in Baan IV. I do not have access to Baan IV. Is there someone who adapted this to Baan IV? If you are using this on Baan IV, please please share the changes needed to make this work.

Bärbel Leucht wrote on 24th September 2002 06:19:
Vamsi,
we did it!
With some changes we can use BaanXL for Baan IV.
We did the following changes:

Script zusfwexcel:
Baan 5 :
start.session(MODAL,"zusfw1400m000","","")
Baan IV:
zoom.to$("zusfw1400m000",Z.SESSION,"zusfwexcel","",0)

Script zusfw1400:
Baan 5 :
(included in Forms definition)
Baan IV:
in Choice Section
choice.user.0:
on.choice:
preview.report()
choice.user.1:
on.choice:
display.ruler()
:
Baan 5 :
before.display.object:, disable.fields and disable.commands
Baan IV:
not possible (and not yet needed)
Baan 5 :
local.filename = "${BSE_TMP}\" & strip$(str$(utc.num())) & ".csv"
Baan IV:
local.filename = "${BSE_TMP}\" & strip$(str$(time.num())) & ".csv"
In function process.file:
Field separator "," to ";" (because of the specifics of Excel)
In function remove.commas:
Comma "," to dot "." (because of country-specifics)

Additional to the steps in your documentation we had to define:
- domain zusfw.dtyp
- question zusfw1400.1

Thank you for your efforts!

Best regards
Bärbel Leucht

~Vamsi wrote on 20th September 2002 18:54:
Bärbel,

I do not have access to Baan IV. I have posted in the same thread as BaanXL for someone to post changes required for Baan IV. If no one responds to that, I will definitely make the changes myself.

Hello Vamsi,

I would like some questions, about your product BaaNXL.

Fisrt, congratulations for your work, I think that is very usefull and interesting

Questions:

1 .- I've four forms for the sesion, but it's only active first, Why?




2 .- What is and Where is :

disable.fields("header.f", "header.t", "grp.before.f", "grp.before.t","grp.after.f", "grp.after.t")

and
disable.commands("populate.group.fields")


3.- What is "zusfwexcel", is a function, a DLL, a sesion, ...?



Thank's a lot

[list=1]
Have you attached all four forms to the session? If you have, please check to see that the correct standard options for the forms are enabled. I believe all the options that need to be enabled are listed in the file forms.txt which is attached in one of the posts at the top of this thread.
disable.fields and disable.commands are BaanERP functions. If you are on Baan IV please look at the posting just above yours to see what changes need to be made to make BaanXL work for Baan IV.
zusfwexcel is a script of type '3GL (Without Stnd. Prgr.)'. We have this in a package 'zu'. You can have this in any other package .. for eg. 'tu' or 'tc'.
[/list=1]

Would someone who has gone through the installation of BaanXL be willing to write some documentation on installation.

Does anyone have a dump of this session, including forms and program for Baan IV?

I had to make a minor change concerning the translation of numbers. Some (finance) reports write amounts within one column in a format depending on the currency, e.g.
USD 1,000,000.00
EUR 1.000,000,00

I decided to implement a *heuristical* version that depends on a few assumptions ( that numbers a written left to right, either . or , are used as thousand sign, either . or , is used as decimal sign).

Here is the code replacement for the function remove.commas():


function fix.numbers(ref string hold.num())
{
long dot.pos, comma.pos

string dsgn(1) | decimal sign
string tsgn(1) | thousand sign

comma.pos = rpos(hold.num, ",")
dot.pos = rpos(hold.num, ".")

if dot.pos > comma.pos then
| NNN,NNN,NNN.NN e.g. english
tsgn = ","
dsgn = "."
else
| NNN.NNN.NNN,NN e.g. german
tsgn = "."
dsgn = ","
endif

while true
long tpos

tpos = pos(hold.num, tsgn)
if tpos then
hold.num = hold.num(1; tpos - 1) & hold.num(tpos + 1; 40)
else
break
endif
endwhile

if dsgn <> "." then
long dpos

| excel's csv needs decimal point, not comma
dpos = pos(hold.num, dsgn)
if dpos then
hold.num(dpos; 1) = "."
endif
endif
}

Nice work!
I'd like to use this feature for my users.
But I can not find anyware the definitions for
- domain zusfw.dtyp
- question zusfw1400.1.
I am using BaanIV c4.
Thanks in advance.

Mihai

zusfw.dtyp
10 num Number
20 str String
30 date Date

zusfw1400.1
Defaults already exist for the report.\nOverwrite?

There is a demo prog on the Microsoft web that creates a BIFF file:
http://support.microsoft.com/default.aspx?scid=kb;en-us;150447

Problem is: it is in c++ for the Windows platform.
Anyone willing to cooperate on porting this / building this into BaaNXL ?

regards,
Robert

Robert,

Please let me know what direction you want to take. From first looks at the Microsoft website and the program that you quoted, it is Windows specific. Especially in its use of OLE. I have in the past looked at complete standalone programs written in Perl that do this.

The idea of keeping BaanXL completely in Baan is extremely tempting to me. This is the reason why I had in the past chosen not to use the Perl program I quoted. If we can find a program written in C completely without using any APIs specific to a platform, our job of creating the BIFF in Baan would be easy.

After I wrote the above, went looking for other options. Here is what seemed a simple but elegant solution. To write HTML output, but have an .xls extension. This is described here (http://mail.python.org/pipermail/python-list/2002-April/097995.html).

The caveat is that it *may* only work with Excel and not any other spreadsheet program.

Hai
I have one basic doubt regarding this product.
with out writing any code we can take the report inot excel by creating
a device with attaching monarch excel.exe.
or else any text file u can open in excel.
what is the advantage of this product above the standard excel devise?
pls clear my doubt.

regards
Juvenile

Received an email from maurixgr:
Hi:

I want to know if need I some kind of permision from you to use the product?


Thanks

No you do not need any permission. Make sure that the copyright statement is not stripped. Also if you distribute the binaries, you will have to have the copyright statement made available in plain text.

hi,anybody can help me?
BaaNXL is a nice application for my company,i like it!
but i have some problem in it:
1.how to define the domain zusfw.dtyp?
2.what is zusfw.dtyp.num,zusfw.dtyp.str in the zusfw1400.bc?
3.may i replace all the zusfw.dtyp domain,and create a new domain in package TC(tcmcs.dtyp)?

thank you very much.

compile error below images,any body can help me?
thanks

1.how to define the domain zusfw.dtyp?
2.what is zusfw.dtyp.num,zusfw.dtyp.str in the zusfw1400.bc?
3.may i replace all the zusfw.dtyp domain,and create a new domain in package TC(tcmcs.dtyp)?

1. The definition is a few posts above.
2. Those are specific enum values of the domain defined in 1.
3. Yes you can create the domain in any package of your choice.

If there is a compile error I do not see it. Warnings are ok - it indicates that the author was lazy to not have paid attention to things that may come back and bite him some day. Hopefully that day is not today :-).

The short story - if you see lines that say "warning" those are ok. If you see any lines with "error" then you have a problem.

thank you very much for your help.but another 2 problems on this session.

when i use printer "BaaNXL",why i can not get the date?
please see the attachment file 1 and 2.

when select printer "BaaNXL",there is no data in the form fields.

by the way,there are 4 forms is this session.when i click the form one by one,like this form1>form2>form3>form4 or form4>form3>form2>form1, no message. but when click like this step:form1>form3(or form4), the error message display ( picture 3).

thank you in advance.

I have actually never looked into this utility, so am not giving you advice specifically for this utility, but more in general...

You mean why the fields are greyed out? Did you define them maybe as display fields? Or maybe you only have read authorization? It is something to do with the definitions... Oh, and I see you have an error "can not reach form"; did you compile all the forms..? And are they linked to the session?

Hope this helps,
En

dear en@frrom,thank you for you advice.
I defined them as input fields according to the readme file.
I compiled all the forms.and all the forms were linked to the session?

You also have to make sure that the form standard commands are alright:
the commands like modify record, and list of forms, first, next, last, previous should be on when there are multiple forms. This shall resolve your problem. Add the necessary standard commands to the relevant forms, compile forms and session, and let us know if it's all working fine now...

Good luck!
En

¿Cómo funciona la función argv$()?

¿Si en la definición del dispositivo, en el campo "4GL program" introducimos ozusfwexcel, por qué pasamos como primer argumento de la función wait.and.activate "ttstpconv"?

FUNCTION MAIN
"input.file = creat.tmp.file$(bse.tmp.dir$())
argument = argv$(3)
wait.and.activate("ttstpconv", argv$(1), input.file, "ASCII", argv$(4))"


Muchas gracias

Sorry, but please post in english in this forum. From just glancing at what ~Vamsi set-up this looks like a device driver. When this script gets called the arguments that are passed from the Baan spooler are as follows:
1 = Name of input (temporary) file containing report - argv$(1)
2 = Name of output file - argv$(2)
3 = Additional arguments (from Device data) - argv$(3)
4 = Specilar parm for ttstpconv (unknown reason) - argv$(4)

Not sure what the "argument = argv$(3)" is for, but the wait.and.activate launches ttstpconv converts filename argv$(1) into a plain ascii format and places the output into the input.file file. Does this answer your question?

Hi Mr Vamsi,

Using your program, when i print a report to Excel, does it launch Excel first?
I need a program that just prints report to Excel format without lauching the Excel application and then user have to "save as" into a file.

Best regards and thanks in advance

Dear Friends,

I have created a session with all 4 forms and copied the script but.

how to/ where to callthe function save.default.values()


Regards
kopson

Check forms.txt file on about 3rd post. These are user defined on the forms. For example:

Form Commands
------------------------------------------------------------------------------------------------------------------------------------
Bind Type ¦ Seq. ¦ Activate a ¦ Menu/Session/Function ¦ Short ¦ Label ¦ Sep. ¦ Button ¦ Execute Save
¦ ¦ ¦ ¦ Cut ¦ ¦ ¦ ¦
-----------+------+----------------------+------------------------------+-------+-------------------+-------+--------+-------------
Form ¦ 1 ¦ Function ¦ exec.cont.process ¦ Y ¦ Continue ¦ Yes ¦ Yes ¦ No
Form ¦ 4 ¦ Function ¦ preview.report ¦ ¦ Preview Report ¦ No ¦ Yes ¦ No
Form ¦ 5 ¦ Function ¦ display.ruler ¦ ¦ Display Ruler ¦ Yes ¦ Yes ¦ No
Form ¦ 2 ¦ Function ¦ save.default.values ¦ ¦ Save Defaults ¦ No ¦ Yes ¦ No
Form ¦ 3 ¦ Function ¦ load.default.values ¦ ¦ Get Defaults ¦ Yes ¦ Yes ¦ No

Hi, Mark,

have B40c4 but i don't know how to use functions as button
or is it available in B40c4, if yes how to use it, Pls. help.

Regards
kopson

What you do is put a user defined button on the form. Something like choice.user.x. Then in the on.choice section just put a call to those two funtions.

Attached is the file for creating the forms. Couple of them are painful :).

Hi Vamsi,

As an user, I have no access into the BaaN server to make any installation nor modification previlige. But I have to do my report in Exel format by downloading data from BaaN 5.

The IT department either shortage manpower or don't care/no what todo. Leaving me suffer with the report in Excel format.

After downloading from BaaN 5 to Excel, I had experienced the following issues:

1. Two coluum from BaaN5 are merged into one coluum of Excel after the downloading from BaaN 5 to XL completed.

2. The order of each coluum from BaaN 5 are changed at XL.

3. Word wrap will not work correctly when I tried to do "auto coluum size"

Please give me a hint of how to do a one time set up on EXCEL side, to save alot of "time consuming" correction in manual mode.

4. I am currently using BaaN 2 Excel software, however, how do I active the "user configuration mode"

5. After downloading files from BaaN5 to Excel, the coluum order were different with the original file from BaaN5

6. There are spacing in the coluum which I can eliminate. Because it will make the characters in the coluum disappear.

7. Alot more of the problem which I wanted to make job more simplerer.

Please help

Million of thanks Vamsi,

formyvn

are u able to export data into excel from baan ???

That is what this complete thread is about - exporting data from baan into excel. Start on the first thread and work your way through.

can it be use in Baan IV?

what about print to excel?

Yes it can be used with baan 4.

Yes it can be used with baan 4.

how to able to use?

can guide me?

thank you.

I have not used this method so I cannot really help. The only thing I can recommend is starting and post 1 and working your way down following each step.

I'm creating EXCEL Multi Sheets reports from BaaN V by using standart BaaN XML functionality. The file can be opened by simple click on it with MS EXCEL.
Create a simple XML spreadsheet within EXCEL.
Use BaaN XML functionality without writing
"<?mso-application progid="Excel.Sheet"?>"
and save the file with xmlWritePretty as *.xls file. It will be opened with MS EXCEL.

We use SSRS and direct excel outputs using seq.*. But, wanted to try this. Works good with standard reports. I should say, Great Work ~Vamsi! :)

Thank you very much for the code.