Hi friends,

I have a new requirement like printing the specific values to a separate sheets using macro in the same excel document but i want to know how should i attach the macro with the baan report so please tell me the solution.


Regards,
Mathew

Hello,

We usually don't use report when running Excel macros, but we create csv file directly in script.

The Excel file containing macro is stored on BaaN Server, so when users use the session, they always use last version of this file.

BaaN Script

#pragma used dll ottdllbw


file1 = "/home/" & strip$(logname$) & "/spsls1450.txt"
file2 = "${TEMP}" & "\spsls1450.txt"
file3 = "${BSE}" & "/Macro.xlsm"
file4 = "${TEMP}" & "\Macro.xlsm"


remove.local.file(file2)
remove.local.file(file4)
file1.id = seq.open(file1,"w+")
if file1.id >= 1 then
read.main.table()
seq.close(file1.id)
server2client(file1, file2, true)
server2client(file3, file4, false)
err = app_start(file4, "", "", "", "")
endif


function read.main.table()
{
|construct your file here
seq.puts(LineString,file1.id) |we use pipe "|" separator

}


Excel Macro

Dim local_temp_path

Public Sub auto_open()

Application.StatusBar = "Please Wait ..."
Application.ScreenUpdating = False

If Range("A1").Value = "BaaN" <> 0 Then
Range("A1").Select
Selection.ClearContents
local_temp_path = Environ$("TEMP")
Workbooks.OpenText Filename:=local_temp_path & "\spsls1450.txt", Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"

Range("A1").Select

'Do what you want here

End If

Application.StatusBar = False
Application.ScreenUpdating = True


End Sub


Olivier

Hi friend,

Where to save this excel macro ? Sorry for giving trouble to you and please explain me in detail.

Regards,
Mathew

The Excel macro is in the Excel file.

BaaN script genrates a csv file on server, and copy it to client in temp folder.
Then BaaN script copy a Excel file containing macro from server to client.
Then BaaN script start this Excel file on client.

As the Excel file contains a macro named "auto_open()", it will excecute it.
--> read the baan file and do all you want (graphs, filters, new sheets, pivot table...)

Thanks a lot friend, i will try and inform you.

Regards,
Mathew

Hi friend,

I tried this script. When i was pressing on continue in the session it automatically opening the excel macro file but not copying the data from notepad to excel, so please tell me what to do. Also i wrote the code in excel what you given to me but it is not working. Also my excel version is MS Excel 2003.

Regards,
Mathew

i would recommend using SSRS (sql server reporting services), comes free with your sql server cd, you can output to excel real-time data.

I can't speak to what benito recommends, but what we did was write a device driver for download files to excel and running macros. We created a device called like pcexcel and put this driver into the 4gl program of the device with argument of ascii. Then we define the location of the macro in tuddc905 and where excel is in tuddc906. In the past the sites we represented ran excel from different places - this is standard now so we could remove this piece, but opted to leave it alone. Next in tuddc905 you define the report, the site the macro download file name, the macro to run and the macro location(this is standard now also).

After that the user can run any report defined above and execute a macro. So for example a user in California can run a report like tisfc9420m000, pick device pcexcel, put in the output file name the site of the macro they want to run. So for example the most common site name is all - this usually means all the sites use the same macro. But depending on the site we can then create different macros - typically everybody uses the same macro. Now the device downloads the report using server2client in the exact location the macro expects. Then the device launches excel and the specified macro. Boxer14 already posted a sample macro with the auto_open feature. Our macros are a little more complicated, but the basics are there.


| Declare api - not needed, just testing.
#pragma used dll ottstpapihand
#pragma nowarnings
#include <bic_tt> |include tools functions

table ttuddc905 | Excel Macro Locations
table ttuddc906 | Excel locations by Site

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)
string local.path(1024)
string mdrive(1024) |20061025

| In this conversion program spool.fileout contains the site where
| the macro will be run from. This allows the user to input a lower
| case site. Site can be any three character field.
spool.fileout = toupper$(spool.fileout)
select tuddc905.*
from tuddc905
|where tuddc905.rprt = :spool.report
where tuddc905._index1 = {:spool.report,:spool.fileout}
selectdo
selectempty
message("Unable to locate report macro info.")
exit()
endselect

| Convert the report 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
| local.path ="c:\baan\tmp\"&tuddc905.dnld
| 092602.st - Change to C:\program files\baan\tmp.
local.path ="C:\Program Files\Baan\tmp\"&tuddc905.dnld
| 092602.end - Change to C:\program files\baan\tmp.
mdrive = "m:\baantmp\"&tuddc905.dnld |20061025
| write_excel_filename(tuddc905.dnld)

| send.report.to.client(tmp.file, strip$(local.path))
send.report.to.client(tmp.file, strip$(local.path), strip$(mdrive)) |20061025
start_excel_with_macro()

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

| Kick off excel with the macro.

}

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

err = server2client(src, dest, 1)
if (err) then |20061025
dir = create.local.directory("m:\baantmp\") |20061025
| if (dir) then |error 183 must mean it already exists
| message("Error %d creating m:\baantmp\", dir)
| endif
err = server2client(src, dest2, 1) |20061025
if (err) then |20061025
message("Error %d copying file to PC (may not have permission)",
err)
endif |20061025
endif
}
function start_excel_with_macro()
{
string appl(132), parm(215),start(1024)
long app_id, rc
| Setup application to start and macro name

select tuddc906.*
from tuddc906
where tuddc906.site = :spool.fileout
as set with 1 rows
selectdo
endselect
appl = tuddc906.loca
|appl = "C:\program files\microsoft office\office\excel.exe"
| macl - macro location, macr is macro name
parm = strip$(tuddc905.macl) & tuddc905.macr
start= appl&" "&parm
| Start the apllication
app_id = app_start(start,"","","","")

rc = app_status(app_id)
if(rc = 0) then
message("Application start failed.")
endif
}

Hi friend,

I don't know where is the path for file1 and how to save the file in that path. Also "if" condition have BaaN and do i want to change the name or something else because nothing happening after the opening of the macro file. Also only one user can open the macro file to run or what? so please tell me what to do.


Regards,
Mathew

Hi Mark_h,

I don't understand the script first, so first tell me how to define the form and input fields using this ?


Regards,
Mathew

The script above is a device driver. It is a 3gl script. So when a baan report program runs, and after data input, the first thing it does is prompt the user for the output device. In our case our users enter PCEXCEL and enter a 3 character site int he output file name(ie. ALL, LVL, ABR, etc.). The report runs and this device driver takes the tmp report file, converts it to ascii, downloads to the client PC(or network drive), then runs the excel macro. Keep in mine the download file name is in one of the tables and so is the macro filename. That is how it knows where to download the file and how it knows which macro to launch.

You can look in Code and Utilities Forum (http://www.baanboard.com/baanboard/forumdisplay.php?f=33) for how to write your own device driver. You can also find other examples exporting to excel. The example I included only launches excel with a report - not with a macro, but it should help.

Hello,

Sorry for late answer, i was away from office.

I wrote a fast sample attached to this post.

mathew.xlsm should be in ${BSE} directory (same as bse.dir$( ), i think)

${TEMP} should return TEMP client local variable.

That's for this example. You can use other directories.

As mathew.xlsm will be copied from server to client at run, several users can use it at same time

i hope this will help.

Olivier

Hi Olivier,

Thanks. I will try and inform you.

Regards,
Mathew

Hi Olivier,

I tried your script and its working fine. Thanks a lot to you friend.

Regards,
Mathew

Hi mark_h,

Thanks a lot to you. Your script helped me to take some of the important lines.

Regards,
Mathew