CODE: BaanXL - Yet another Baan to Excel utility.

~Vamsi · #1 19th July 2002, 07:04

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

Code:

|******************************************************************************
|* 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

~Vamsi · #2 19th July 2002, 07:10

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

~Vamsi · #3 19th July 2002, 07:10

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.

~Vamsi · #4 19th July 2002, 07:11

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?

~Vamsi · #5 19th July 2002, 07:12

Here is form 3

~Vamsi · #6 19th July 2002, 07:12

Here is form 4

~Vamsi · #7 19th July 2002, 07:14

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.

~Vamsi · #8 19th July 2002, 17:36

Here is the User guide to BaanXL.

Enjoy!

~Vamsi · #9 25th July 2002, 18:42

Quote:

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.

~Vamsi · #10 20th September 2002, 18:53

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.

~Vamsi · #11 26th September 2002, 06:26

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:

Code:

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

Quote:

~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.

Binnicol · #12 7th October 2002, 12:23

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

~Vamsi · #13 7th October 2002, 20:35

[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.

jriveros · #14 7th November 2002, 15:45

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

günther · #15 18th November 2002, 13:16

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():

Code:

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
}