Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 19th July 2002, 07:04
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Lightbulb Code: Yet another Baan to Excel utility.

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
Attached Files
File Type: bc zusfw1400.bc (16.6 KB, 1734 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #2  
Old 19th July 2002, 07:10
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Form definitions

Attached is the file for creating the forms. Couple of them are painful :).
Attached Files
File Type: txt forms.txt (35.3 KB, 1172 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #3  
Old 19th July 2002, 07:10
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Form - 1

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.
Attached Images
File Type: gif form1.gif (117.9 KB, 2781 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #4  
Old 19th July 2002, 07:11
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Form 2

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?
Attached Images
File Type: gif form2.gif (121.3 KB, 2424 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #5  
Old 19th July 2002, 07:12
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Form 3

Here is form 3
Attached Images
File Type: gif form3.gif (98.8 KB, 2243 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #6  
Old 19th July 2002, 07:12
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Form 4

Here is form 4
Attached Images
File Type: gif form4.gif (98.7 KB, 2087 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #7  
Old 19th July 2002, 07:14
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Device setup

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.
Attached Images
File Type: gif devicedata.gif (99.9 KB, 2241 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #8  
Old 19th July 2002, 17:36
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
User Guide

Here is the User guide to BaanXL.

Enjoy!
Attached Files
File Type: zip baanxl_user_guide.zip (229.4 KB, 1041 views)
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Sponsored Links
  #9  
Old 25th July 2002, 18:42
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
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,
  1. 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
  2. See above answer
  3. 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
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #10  
Old 20th September 2002, 18:53
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Baan IV anyone?

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
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #11  
Old 26th September 2002, 06:26
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
Here are the Baan IV changes. Done by the same person who needed them :)

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.
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #12  
Old 7th October 2002, 12:23
Binnicol's Avatar
Binnicol Binnicol is offline
Junior Member
 
Join Date: Mar 2002
Posts: 22
Binnicol is on a distinguished road
Unhappy Doubts in BAANXL

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
Reply With Quote
  #13  
Old 7th October 2002, 20:35
~Vamsi's Avatar
~Vamsi ~Vamsi is offline
Guru
 
Join Date: Aug 2001
Location: San Diego CA, USA
Posts: 590
~Vamsi will become famous soon enough
Baan: ~*~ - DB: ~*~ - OS: ~*~
[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.
__________________
~Vamsi
Vamsi Potluru
Baan XL Yet another tool to format Baan output in Excel from Baan Board
This one just happens to be free :)

Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #14  
Old 7th November 2002, 15:45
jriveros's Avatar
jriveros jriveros is offline
Newbie
 
Join Date: Feb 2002
Location: Bogota - Colombia
Posts: 2
jriveros is on a distinguished road
Baan: BaaN IV C2 - DB: Informix - OS: AIX, HPUX, NT40, NT2000
Question BaaNXL for BaaN IV

Does anyone have a dump of this session, including forms and program for Baan IV?
__________________
smile
Reply With Quote
  #15  
Old 18th November 2002, 13:16
günther günther is offline
Guru
 
Join Date: Jan 2002
Location: Ehingen, Germany
Posts: 570
günther is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Number formats

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
}
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code Management in BaaN IV migramir Tools Administration & Installation 9 6th February 2003 21:20
Baan World Users conference in Orlando chadwickh General Discussion & Chat 7 3rd November 2001 21:55
Availability of Web-Based DEM (Baan Press Release) Pierre B. General Discussion & Chat 0 5th September 2001 05:59
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 19:44


All times are GMT +2. The time now is 08:21.


©2001-2017 - Baanboard.com - Baanforums.com