Hi,

Habitually, I export reports from Baan to Excel but simple data.
I would like to improve the formats, exactaly following things:

- Size font
- bold headers
- Filters headers
- To set print area and configuration of page.

Thanks a lot,

María

Hi,

You can use an Excel macro to do the formatting for you. In this thread (http://www.baanboard.com/baanboard/showthread.php?t=29235&highlight=macro) I have posted an excel macro that will build a graph according to the data. You will have to know VBA to build your own macro. However, you can use the "Record macro" in Excel to write the function for you.

I hope it helps,

- George

Hi,

I use next function to launch Excel and it works ok

app_id = app_start("C:\Archivos de programa\Microsoft Office\Office11\Excel.exe" & " C:\" & name_file & " " & "C:\" & name_file, "", "", "", "")

But, if I want to launch your file with a macro, what I have to do? I tried with next line but it didn´t run well:

app_id = app_start("C:\macrosbaan\george7a\macro.xls" & " C:\" & name_file & " " & "C:\" & name_file & " C:\" & name_file & " " & "C:\" & name_file, "", "", "", "")

where macro.xls is a xls file with a macro.

Thanks,

María

Hi,

Inside the macro code (open the excel file & click on alt+F11), you can specify the location of the input data file. I can see that it opens report.txt that is in the same directory of the excel file. Feel free to change that code to whatever you want.

So the app_start will have to execute the macro alone without any arguments and the macro will open any file you want.

Let me know if you have any more questions,

- George

George,

but you are running the excel file out of baan, and I need launch excel, with an specific format, from a baan session.

I dont`t know if I didn`t understand you well.

Thanks,

María

María,

I have downloaded the zip file that was attached in the thread I mentioned earlier and extract it under "C:\temp". So now I have an "Excel" directory under my "c:\temp". The name of the excel file is "template.xls". The command I tried just now & worked for me is:
app_start("template.xls", "C:\temp\Excel", "", "", "")
I can see that you have the excel file under "C:\macrosbaan\george7a" and the name of your xls is "macro.xls". In order to open the file from baan you will have to run this commandapp_start("macro.xls", "C:\macrosbaan\george7a", "", "", "")
Make sure that report.txt exists under "C:\macrosbaan\george7a" too, since the macro will try to open it (if you didn't edit the original macro).

Did it work for you?

- George

Hi George,

ok, with your sentence I´ve launched excel file template.xls from my Baan session.

Now, my problem is VB, can you see this code

Sub Macro1()
'
' Macro1 Macro
' Macro grabada el 16/04/2007 por .
'

'
Dim local_tmp As String

local_tmp = ThisWorkbook.Path

If Dir(local_tmp + "\report2.txt") = "" Then GoTo endsub
ChDir local_tmp

Workbooks.OpenText Filename:=local_tmp + "\report2.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 2))

Rows("1:1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Negrita"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.Select
ActiveWindow.Zoom = 75
Range("C8").Select

Windows("report2.txt").Activate
ActiveWindow.Close

endsub:
End Sub

Thanks a lot

Hi,

I am glad it helped.

Can you please specify what exactly is you VB problem?

- George

In the code in my last post I am trying to format headers, and size font.

When I run the session, the Excel file opens but empty and, when I close the file, Baan is closed too.

Thanks,

María

To activate the macro immediately when the XLS file is opened you will have to name the macro "Auto_Open()" instead of "Macro1()".

What do you by:
when I close the file, Baan is closed too.

BW is closed? The current session is closed?
Do you have any code in your Baan script after the "app_start" command?

- George

Hi George,

I´ve changed the name to Auto_Open() and, when I run the session, the excel file list the date with the desired format. There was a problem with my code and then Baan was closed but that is solved.

1)When the Excel file is launched, it is opened automatically a windows asking for Close, Save or Canceled the file. I would like this window did not open.
2)I would like that the name`s file was dinamic because then, a user could open several files at the same time.
3)I can't define a filter in a empty file with a macro. How can I define a filter for the header's file?

Thanks

I thought I would add that what we did in baan was to create a table to hold basically the report name, download file name, macro, macro location to run. So when the users picks the device called PCEXCEL it runs our device script which takes the report name and looks up the information in the table. It then downloads the text file, launches excel with the specified macro. Originally we had 3 sites running excel in various locations and macros at each site. Now we have one common directory and all sites launch excel the same way.

The way we create macros - is first record the basics. Then we just take that and edit them to do exactly what we want.

Just something to think about. Of course Nazdaq already has a tool and ~Vamsi posted another version in the code and utilities.

As I understand the "Baan" issue is solved and the VB is still opened. I can help you with the Macro (VB) issues off-line since it does not apply to this thread. Please send me an email with your problem, and I will reply ASAP.

Also for more VB help, you can use this good VB forum (http://www.xtremevbtalk.com/).

- George

Thanks a lot,

I`ve solved last VB doubts. The problem with Baan is perfectly solved.

I´m going to continue working with the idea to generate reports with formats and with different names.

Thanks again,

María

Marc,

what do you want to say with:
"So when the users picks the device called PCEXCEL it runs our device script which takes the report name and looks up the information in the table. "

How you can configure a device? It`s a device from the session ttaad3100m000?

Thanks,

María

See the code and utilities forum for creating your own device driver. You can see the device in the attached jpg.

Here is part of what the code does.

spool.fileout = toupper$(spool.fileout)
select tuddc905.*
from tuddc905
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))

Spool.fileout is used so we can have multiple macros for the same report - this used to be used by site. After the above code server2client is used to download the file to where the macro expects it.


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"
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
}

The above code used launch excel from the tuddc906.loca variable - but as I said in the earlier post excel is now launched at all locations using plain old excel(we could remove this code). This should give you an idea of how we do things.

Hi Marc,

Could you send me the code for tuddcconvl?

Another question, with a table we can save different formats but, do you think that there is a way to launch a number of reports so big as we need, at the same time, with a common macro behind?

Thanks a lot,

María

Sent you PM. Well typically we write 1 macro for each report format that we want. We have kept it pretty simple. In a couple of places we did create spreadsheets to do a process. For example - user clicks a button and data is pulled from their own spreadsheets and formated for baan. User then runs a baan session that reads this data and processes(which downloads data back to the users PC). The user closes baan session, goes back to the spreadsheet and pushes another button which pulls in the downloaded data and formats it. I think this can be simple or as complicated as you want it to be. As for a common macro - I can't really say. I think you can possible use certain type functions for each report. I think what will really happen is that if your users get this they will say - can you sum this column, add these columns, format it, sort it differently, etc. So then you get specific actions for specific reports.

Hi Mark,

could you send me a dump from the tables

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


Thanks,

María

I really recommend you re-design it to meet your needs. I can send a dump file, but they are simple tables(see the screen shots). I also do not need the 906 table anymore.