Below is a sample of device driver which could be used in replace of ttstpconv. The original source was posted by Kevin Brock. I modified it for my use on our system. This is just a sample of what you could do. I have another version that uses a table and tells which macro to launch for excel.
See the attached zip file for a copy of the device data.


|***************************************************************************
|* tuddcconv2 0 VRC B40C c3 dev
|***************************************************************************
|* Title : tuddcconv2 - Download file, start excel, clean up
|* Description: : This program removes downloads a report, starts excel,
|* and cleans up after itself.
|* Original Author : Kevin Brock
|* Modified by : Mark Holland
|* Date : 11/29/01
|***************************************************************************
|* Category : Utility
|* Platform : UNIX
|*
|* License for tuddcconv2
|*
|* Copyright 2001 by Mark Holland
|*
|* 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.
|*
|* Mark Holland and Baanboard.com DISCLAIM ALL WARRANTIES WITH REGARD TO THIS
|* SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
|* AND FITNESS, IN NO EVENT SHALL <author> nor Baanboard.com 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.
|***************************************************************************
|
|*************************** DECLARATION SECTION ***************************
#pragma nowarnings
#include <bic_tt> |include tools functions

function main()
{
| This session will run the standard program conversion session
| ttstpconv using a temporary output file and the arguments
| passed in 3+ verbatim.
| Arguments are passed from Baan spooler as follows:
| 1 = Name of input (temporary) file contain dev independent report
| 2 = Name of output file
| 3 = Additional arguments (from Device data)
| 4 = Specilar parm for ttstpconv (unknown reason)

string tmp.file(1024) | Temporary file name.
string local.path(1024) | Location on client for report.
long rc | Return code.
string appl(132) | Application to start.
long app_id | Application id.
string start(1024) | Parameter for the app_start
| function which has application
| and file to use.

| Convert the report file into the temp file.
tmp.file = creat.tmp.file$( bse.tmp.dir$() )
wait.and.activate("ttstpconv", argv$(1), tmp.file, argv$(3),argv$(4))

| Set the path on the client's pc and send the file down. Let the user
| enter the path and filename to be downloaded.
local.path = strip$(spool.fileout)

| Copy file to the client.
send.report.to.client(tmp.file, strip$(local.path))

| Start the file.
appl = "C:\program files\microsoft office\office\excel.exe"
start= appl&" "&local.path

| Start the apllication
app_id = app_start(start,"","","","")

|Check status of Application.
rc = app_status(app_id)
if(rc = 0) then
message("Application start failed.")
endif

| Remove our work file
seq.unlink(tmp.file)

}

function send.report.to.client(const string src(), const string dest())
{
long err

err = server2client(src, dest, 1)
if (err) then
message("Error %d copying file to PC (may not have permission)",
err)
endif
}

Hi Mark,

I'd tried to install your program, but the error status isn't cleared (I used compile flag -b ottdllbw) :
When script type = 4:
tuddcconv20:
std_gen6.1 -b ottdllbw -s ptuddcconv20 -pacc B40CUS03 -qe /baanprog/bse/tmp/tmp059325143
bic6.1 -Pvariant=0 -b ottdllbw ptuddcconv2g -u -d ottstpstandard -o otuddcconv2
-f /baanprog/bse/tmp/tmp059325142 -pacc B40CUS03 -qe /baanprog/bse/tmp/tmp05932
5144

So no error message is shown, but it doesn't work. What 's wrong ?

Now we are using a device "EXCELIT" using as 4GL-program: ttstpconv and argument: ASCII:excel. This works good, but it is annoying that always a new excel application starts, also when excel is already running. Is this resolved with your script, and/or are there other advantages ?

Thanks

Thomas

I use the -d ottdllbw option on the compile. I am sorry to say that it will not solve the problem of launching multiple copies of excel. I have never really thought about that. I am not really sure how you stop that. Now that I think about the way that I use it I would prefer it to start multiple copies of excel. Usually (not always) when I down load to excel I kick off a macro. This way I can run the same report(different parameters) multiple times and have multiple macros running at once. See below.

This program was mainly just a sample of what you could do on your own. The way we use it hear at this site actually involves a couple of tables. One to hold the application to start and one to hold the macro that gets kicked off at the start of the app.

Examples:

table 1
report name(key) : rtisfc960001000
site (key) : lou
download filename: c:\baan\tmp\somefile.txt
macro name : somemacro.xls
macro location : f:\common\macro

table 2
site(key) : lou
appl : c:\somedir\excel.exe

I designed this for excel, but others discovered you could set up other applications. To get this to work the user selects the device PCEXCEL and puts the site key in the output file. Not elegant , but it is functional.


Mark

Can you please tell where function server2client() can be found
or give me some idea of what is required.

(We use baan4c4)

Your help will be appreciated

Terry

It is located in ttdllbw.

you can see many threads related with its use in the forum.

Include this line in your code and then use it.
#pragma used dll ottdllbw

If you want more infromation use (in Unix), bic_info6.1 ottdllbw -eu, or use session "Print Objects by Program Script / Libraries" (ttadv2439m000)

Hope this helps.

Originally posted by alejandro
It is located in ttdllbw.

you can see many threads related with its use in the forum.

Include this line in your code and then use it.
#pragma used dll ottdllbw

If you want more infromation use (in Unix), bic_info6.1 ottdllbw -eu, or use session "Print Objects by Program Script / Libraries" (ttadv2439m000)

Hope this helps.

Thank you I will try it right away.

I didn't know about bic_info

Regards

Terry

Thank you I will try it right away.

I didn't know about bic_info

Regards

Terry

Hi mark,

i read your post on how you use excel together with a macro.

we plan to do the same, but until now i could not find the way to start excel with a special macro.

can you please tell me the syntax how to start excel with a specific file AND launch a macro after opening this file.

Thanx,
jochen

To launch excel with a macro just do:

excel somepath\macrofile.xls

Please note that Excel must be in your path or you have to include the path with excel. The way I do this is the macrofile.xls contains the macro code. It uses the auto_open() subroutine to start processing. This macro knows where the data file is located and pulls it in to process it.

So I have a table that has report name and download file location and macro. My ttstpconv program looks up the report name and gets the download location. It the uses server2client to download the file to that location. Next my ttstpconv launches Excel with the macro from the table.


Mark

Hi Mark!

How can Your ttstpconv replacement know about the report that it was called by?

Is there a magic variable to import?
Or do You just parse the report data for a heading (which is the least best I'd like)

I'd be interested in the technical report name, same as in Your "table 1" mentioned earlier in this thread, because in my ttstpconv replacement (or wrapper actually) I want to do some processing dependig on the report name

Thanks a lot

Hrmm, hrrm,

very embarrassing! That one was a clean RTFM!

There are predefinde variables for this:
(it's even in the online-manual)


DESCRIPTION
The following predefined variables are used within the spooler functions:


long spool.date date to print
long spool.fontnumber number of font, 1=LARGE, 2=SMALL,
3=MIDDLE
long spool.id identification of spooler
long spool.left.mrg left margin
long spool.pr.copies number of copies
long spool.pg.length length of page
long spool.pg.width width of page, incl left margin
long spool.time time to print
long spool.view.rtl report in bidirectional language,
true = yes, false = no
string spool.device(14) name of spooler device
string spool.fileout(100) path name of spooler output file
string spool.paper.type(6) type of paper
string spool.pr.line(300) line to print
string spool.report(20) report to be printed


Those varables are available to ttstpconv or a replacement program

Thanks anyway

I'm trying to make the clientfile read-only, but haven't succeeded sofar....
I am able to make the file in the Unix dir read-only, but what I am trying to achieve is not letting server2client overwrite the file on clientside...
Any ideas?

Greetz

Hi Pilekes,

assume You have a bwclient, just got this idea (untested):

string dest(1024) | Temporary file name holds full client-side file path (i.e. C:\temp\foo.txt)

| do the server2client(src, dest, 0) stuff here
...
| now set dest to r/o on BW Client
app_start("ATTRIB +R " & dest ,"","","","")

Of course, errorchecking should be done on app_start, but this shoud work.

bye

Wait a minute,
what are You trying to achieve?
With the piece of code I just posted, the next server2client() will run on an error if the destination file is write-protected (as it does if it's currently opened, btw)

What You'd need additionally is probably a code that checks the returncode of server2client() and then changes the filename (or lets the user do it).

Or You should probably generate a unique filename in the form of prefix+timestamp or something in the firstplace.

bye

I was trying to rename a file after I had read its info into a table (2 actually, but ok) and save this file clientside with a unique name.
So I thought of using an extension .000 - .999 so if server2client would fail it would mean .000 had to be increased to .001.
But since server2client overwrites the file I was trying to make it read-only...
Since I couldn't get this to work I used a date&time stamp instead ;)
So now the name of the file is like YYYYMMDDHHMMSS.txt that was a lot easier, since now I don't have to set read-only to the files.
So now my program works :)

Thanx 4 the effort anyway!

I am new in the Baan word.
I find your code very interesting y and I want to probe it, but I don´t know how to compile it.
Sorry, but can you help me.

Thanks

Ralph

I would like to know that after the parameters are passed to the script, the format of main function for getting the parameters using argv$() and checking them.

I am not sure I understand the question. Anyway you can use argc$() to see how many parameters are passed. Then you can set variables to the argv$() and check them. For example if your first argument was project, you could set a variable project=argv$(1). Then use tt.align.according.domain to get the correct justification and then check against a table. I am not sure if the variables are all passed as strings.

Mark

this program started excel, but the data is in text format and each line of data is in one cell/row. How can this work so the the rows/columns of data are separated??

The code and thread were just an example of how you could write your own device driver. There are actually several ways to accomplish this - use the tool ~Vamsi posted, NAZDAQ program, etc. Or you could modify a report to put the data in a comma delimited format, download and run it. Or as you see at the beggining of the thread a method where we created a table to hold macro information by report. When our device is selected it downloads the file to the PC and launches an excel macro to format the report according to our users wishes. The macros are stored by site and report name - this way we could format the same report differently for each of our sites.

Our simple excel device does not use a custom "ottstpconv" program. It downloads it and launches excel. The data is in the format you mentioned - all in one row. Our users know to use the text to columns option under data on the menu.

Mark

Thank you very much for the sample, it is very interesting.

We modified the sample so that it would email the Baan Print File to the users. We want to be able to pass the email data;

email subject
email to address
email from address
email message body

from the calling program to the tuddcconv4 device driver.

If we print manually, and put the email data into the input string variables, it sends the bpf as expected. We are using 'BLAT' to actually send the email.

Now my problem:
I can't make the tuddcconv4 script import the email data that I have exported in my source program.

Is there a special function to import variables from a 4GL script into a 3GL script ?

main program :

choice.cont.process:
on.choice:
export("email.from", email.from)
export("email.to", email.to)
export("email.body", email.body)
export("email.subject", email.subject)
export("email.attach.send", email.attach.send)


tuddcconv4 :

|******************************************************************************
|* Title : tuddcconv4 - email bpf to user
|* Author : John Roberts
|* Co-Author : Bert Klunder
|* Date : <2005-Nov-22>
|******************************************************************************
|* Script Type : Library
|* Description :
|* Category : Utility to email bpf
|* License :
|* Copyright 2005 by Bert Klunder & John Roberts
|*
|* 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.
|*
|* Bert Klunder, John Roberts and Baanboard.com DISCLAIM ALL WARRANTIES WITH REGARD TO THIS
|* SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
|* AND FITNESS, IN NO EVENT SHALL <author> nor Baanboard.com 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, rc, i
extern domain tcmcs.str100 email.attach, email.from, email.server, email.to, email.body, email.attach.send, email.subject
string application(300)

#pragma used dll ottdllbw
#pragma used dll ottdllinputstr

string path.file(128) |Log file path
long fp.file

function main()
{

email.from = strip$(argv$(2))
email.server = strip$(argv$(3))
email.attach = strip$(argv$(1))
write.log.line("Temp File Name : " & email.attach)
write.log.line("Email.From : " & email.from)

get.var(parent,"email.to",email.to)
get.var(parent,"email.attach",email.attach)
import("email.from", email.from)
import("email.to", email.to)
import("email.body", email.body)
import("email.attach.send", email.attach.send)

message("email.to :" & strip$(email.to))
message("email.attach :" & strip$(email.attach))
if not job.process then
if strip$(email.to) = "" then
ret = input.string("Destination", "Enter list of recipients seperated with a ',' no spaces", 100, 50, email.to)
endif

for i = 1 to len(email.to)
if email.to(i;1) = chr$(34) then
email.to(i;1) = "."
endif
endfor
if strip$(email.subject) = "" then
ret = input.string("Subject", "Please enter a subject for the email", 100, 50, email.subject)
endif

for i = 1 to len(email.subject)
if email.subject(i;1) = chr$(34) then
email.subject(i;1) = "'"
endif
endfor
if strip$(email.body) = "" then
ret = input.string("Email Body", "Enter text for the email body", 100, 50, email.body)
endif

for i = 1 to len(email.body)
if email.body(i;1) = chr$(34) then
email.body(i;1) = "'"
endif
endfor
if strip$(email.attach.send) = "" then
ret = input.string("Attachment", "Enter a name for the file to be sent", 100, 50, email.attach.send)
endif

| check to make sure no illegal characters have been used for the attachment name. Change to a "." if it has
for i = 1 to len(email.attach.send)
if email.attach.send(i;1) = "/" or
email.attach.send(i;1) = "\" or
email.attach.send(i;1) = ":" or
email.attach.send(i;1) = "*" or
email.attach.send(i;1) = "?" or
email.attach.send(i;1) = chr$(34) or
email.attach.send(i;1) = "<" or
email.attach.send(i;1) = ">" or
email.attach.send(i;1) = "|"
then
email.attach.send(i;1) = "."
endif
endfor

else

endif

email.file()

}

function email.file()
{
long appid

| make sure the final file has a valid extension
appid = file.cp(email.attach, email.attach.send & ".bpf")

for i = 1 to len(email.attach.send)
if email.attach.send(i;1) = "/" then
email.attach.send(i;1) = "\"
endif
endfor

application = "/c blat c:\temp\blank.txt -t " & strip$(email.to) &
" -f " & email.from &
" -attach " & chr$(34) & email.attach.send & ".bpf" & chr$(34) &
" -server " & email.server &
" -body " & chr$(34) & email.body & chr$(34) &
" -S " & chr$(34) & email.subject & chr$(34)
& " -log c:\temp\blat.log"


appid = run.prog("cmd.exe", application, RP_NOWAIT)
}


|**************************** end of script *****************************

In the old days (before management rejected my program) I had an email device. I can not find a copy of the calling program. I do remember it called a 4gl session to prompt for the subject, etc. I could have sworn I used import/export.

Try making it(your conversion session) a 4gl program - the form can just be blank. I believe the argv params will still be the same. Then in the init.form section you can do something like this:


form.1:
init.form:
import("mess", parm)
execute(cont.process)

Hi John,

use get.var in your 3 GL script, in following example I get the name of the session printing the report:


function main()
{
extern string print.session(20)
extern long session.id

import("parent", session.id)
get.var(session.id, "prog.name$", print.session)
.....
}

Thanks for the help,

I changed my import statements to be get.var statements :


import("parent", session.id)
get.var(session.id, "email.from", email.from)
get.var(session.id, "email.to", email.to)
get.var(session.id, "email.subject", email.subject)
get.var(session.id, "email.attach.send", email.attach.send)
get.var(session.id, "email.body", email.body)


and now the script works, and emails users bpf files.

Thanks very much for the help.
John

Hi John,

glad to e of some help! Maybe in return you can tell me how you create your pdf's.

Using the other posts in this thread & ghost script,
we have tested the creation of pdf's before,
but we are distributing bpf's via the automated e-mail.

Sorry,
John

oh I get it bpf as Baan Print File!

Sorry I misread!

Hi Mark,

Will your suggestion above, launch excel everytime during print.

Can I print to file with excel format without launching excel.

Thanks and regards,

With this solution you can pretty much make it do what you want. In this example it should launch excel every time. We have another version that launches excel with a different macro for a defined report.

Hi Mark,

Sorry, but i do not understand you.

I just want a version that does not launch excel at all. But I want it to save the file in excel format. I will email this file to user and the user can just open it with excel without needing to go to Open file wizard to set the delimiter.
ie. it should already be in xls format.

thanks and regards

Hi, Mark,
I found a very strange problem,
If add a "XML" parameter, then the convert problem cannot work.

And if we don't use parameter, then it works.

What's the problem?

Not sure about your system, but on mine it gives me a message - "Conversion to XML is not support yet." I am on 4c4 and I do not know what is supported on 5 and LN, so I will not be much help.

any update on this..

i want the export file to open with excel and the delimiter will auto split into cell.

all,

i am using windows operating system.

please send me script for the same.

all,

i am using windows operating system.

please send me script for the same.

Hello,
the script is located in the ZIP-File from Mark.

Download and install the file.
Modify for your use as necessary.

Regards

How to capture the temp report file name. like "tmp055915121_20972.bpf" whether any body tried it successfully.

spooler.fileout etc... not giving any output file name.

thanks

Do you mean spool.fileout - not spooler.fileout? Of course if this is ln then I really cannot help since I am on 4c4.

Do you mean spool.fileout - not spooler.fileout? Of course if this is ln then I really cannot help since I am on 4c4.


in LN it's
string spool.fileout(100) Path name for spooler output file.
also.

Regards

Hi Mark, I am unable to download your attachment. Can you post it back?

I did not have a problem with downloading the original attachment zip file, but here are the two files it contains.

Thanks Mark. I will try it

Hi experts

After using the script in this thread, i am getting this error
Error -2 during seq.open

Does anyone know how to resolve this?

thanks n regards,

It means that the file does not exist. Which seq.open - make sure that the file on the client is not in a path with spaces in it.

Hi experts

After using the script in this thread, i am getting this error
Error -2 during seq.open

Does anyone know how to resolve this?

thanks n regards,

Hello,

it's a indicator for OS error !
-2 cannot open the master pty

Check directory rights, spaces in filenames, .......

Regards

Hi Mark

I am getting two different text files.

When I use the normal ASCIF device, i get the textfile zz208437. This is correct.

But when I use your script in this thread, the output file is like zok208437. I do not wish to have the special characters in this file.

What must I do? Please advise

thanks and regards,

Basically what this script does is take the baan created report in the tmp directory(this should be file 1 with the special characters), convert it ascii(into a temp file), then download this file to some destination(in spool.fileout). Then launch excel to pull in the ascii test file. The final step is to remove the tmp file it creates. So you should end up with 1 normal baan report on the baan server and 1 ascii text file on the client. Bottom line - you end up with 2 reports, excel only launches against the plain text file that was created and downloaded to the client.

The normal baan report left on the server should be handled by whatever is setup on your system. We are on 4c4 and I think we set it to remove using the delete delay time(1 hr) on the user setup. This prevents the tmp directory from filling up.

i'm getting the above error on this line:

activate("ttstpconv", argv$(1), argv$(2), argv$(3), argv$(4))

full error is:

Cannot open file (error -2)
Conversion cancelled

I thought i should change the object ostpconv to 755 permissions but it didn't help. anything else i should check? this is a test system and env is similar to Prod. i also tested on BaanIVc4 and LN FP8. the same error.

thanks
ben

Well I use wait.and.activate, but I am not sure that is the problem. It sounds like one of the filles cannot be opened. So the question is - which file? So in this code:

| Convert the report file into the temp file.
tmp.file = creat.tmp.file$( bse.tmp.dir$() )
wait.and.activate("ttstpconv", argv$(1), tmp.file, argv$(3),argv$(4))

In debug mode I would make sure that the tmp.file is created under the baan tmp directory. Then make sure that argv$(1) points to the report that was created. Make sure the report is there and has data in it for the conversion. Then right after the ttstpconv you should be able to more the tmp.file and see the ASCII or whatever report.

thank you mark. i have made sure that it created a temp file. i even created the temp file in my home directory just to make sure that it's not a permission issue. that's how far my debug session could go because i get that error almost immediately. almost leads me to believe that it's not opening the ttstpconv.

Did you check the argv$(1) - make sure that file was available? When you run the session and pick the device - are you putting a file name into the output file name? Not sure that makes a difference. My ttstpconv on 4c4 is 660 for permissions and is owned by bsp and in the bsp group.

thanks mark. i got over this issue. the problem was i was working on a dev server that wasn't setup properly so the environment variables were screwed up. i am actually working on html device to print logo. i do have an issue but it seems similar to the other poster.

Hi Mark H
and all
Thanks for this.
It works and solves my problem.

Thanks

Gerard

Hello Mark

Where are you exactly prompting the User to mention the Path? And How?


| Set the path on the client's pc and send the file down. Let the user
| enter the path and filename to be downloaded.
local.path = strip$(spool.fileout)

Hi Ajesh,

Its the path you see when system pops up device selection form(ttstpsplopen). If you are on latest TIV, the path will have a check if the folder exists on server. In that case, you can allow the user to put only file name in device path and then make a little change in the script -

| Set the path on the client's pc and send the file down. Let the user
| enter the path and filename to be downloaded.
local.path = "${BSE_TMP}\" & strip$(spool.fileout)

This will make sure the file is transferred from the temp file to users tmp folder and the file name is taken as the user entered in the Device selection form.