hi all gurus out there,
i am very new to baan programming - and hence the question - may be stupid - but would appreciate if any of you gurus could answer it.

the question is : is it possible for me to export a report directly to excel. my users are asking for it and i am not sure how to do it? I am working in baan5c - so any hint or codes snippets would be appreciated

thanks

Several solutions are posted in this forum and the code and utilities forum. At our site we use excel macros to format reports. We have a device called PCEXCEL that looksup the report number, finds the download file name and then launches the appropriate macro. Just one of the many ways to do this. So you can search for EXCEL and get other solutions. I think ~Vamsi posted a method on how to create the xls files.

Hi mark,

Is it possible that you share your Excel macors so we can also benifit from your idea.

Thanks in advance
MRF

I am not sure my macros would help others. Most of our Macros are just recording the steps to format a report. Really basic stuff. The macro below is no longer used. What it did was take a home made report, format it, then run through and pull some additional fields from Baan. I had to do this because nobody wanted me to change the original session or report.


Attribute VB_Name = "Module1"
' Set-up the variables
Dim BaanObj As Object
Dim B_function As String
Dim B_function2 As String
Dim Query As String
Dim temp_string As String
Dim value_string As String
Dim query_id As Long
Dim RetVal As Long

' Routing Stuff
Dim Item As String
Dim operation As Long
Dim routing As String
Dim setup As Long
Dim order_status As Long
Sub auto_open()
' Call Performance_details
End Sub
Sub Performance_details()
'

'
' Application.WindowState = xlMinimized
'Step 1 Convert text to columns
Workbooks.OpenText FileName:="C:\baan\tmp\perfdetails.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 2), Array(9, 1), Array(10, 1))
Sheets(1).Name = "Details"


Row = 2
workcenter = UCase(Sheets(1).Cells(Row, 1))
subwc = UCase(Sheets(1).Cells(Row, 2))
While subwc <> ""
If (Left(workcenter, 1) <> "3") Then
'Rows(row:row).Select
Sheets(1).Rows(Row).Delete
'Selection.Delete Shift:=xlUp
Else
Row = Row + 1
End If
workcenter = UCase(Sheets(1).Cells(Row, 1))
subwc = UCase(Sheets(1).Cells(Row, 2))
Wend
holdrow = Row

Call GetOrderStatus

' Save as for the sub-totals
ChDir ("c:\baan\tmp")
FileSaveName = Application.GetSaveAsFilename(initialfilename:="perfdetails.xls", _
filefilter:="Microsoft Excel Workbook (*.xls),*.xls")
ActiveWorkbook.SaveAs FileName:=FileSaveName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' Get ready to do subtotals
Columns("I:M").Select
Selection.EntireColumn.Hidden = True
Columns("O:P").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=-6

' Select columns for sub-totals
Columns("A:R").Select

' Step 4 Do main work center sub totals
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 14, _
17, 18), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
' Select range
' Sheets(1).Range(Cells(1, 1), Cells(holdrow, 10)).Select
' Step 5 Do sub work center sub totals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 14, _
17, 18), Replace:=False, PageBreaks:=False, SummaryBelowData:=True

' Step 6 Do percentages calculations
Row = 2
std = Sheets(1).Cells(Row, 5)
act = Sheets(1).Cells(Row, 6)
perc = Sheets(1).Cells(Row, 7)
While std <> "" And act <> ""
If (perc = "") Then
If (act = 0) Then
Sheets(1).Cells(Row, 7) = 0
Else
Sheets(1).Cells(Row, 7) = std / act * 100
End If
End If
Row = Row + 1
std = Sheets(1).Cells(Row, 5)
act = Sheets(1).Cells(Row, 6)
perc = Sheets(1).Cells(Row, 7)
Wend
' Format the columns as numbers
Columns("E:G").Select
Selection.NumberFormat = "0.00"

' Step 7 Do save as to allow user to save worksheet
ActiveWorkbook.SaveAs FileName:=FileSaveName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


Sub GetOrderStatus()
' The DLL "ottdllsql_query" contains a function to convert this string to a Baan query and
' other functions to parse and execute the query. The DLL also contains functions to retrieve
' the result of the query, e.g. function "olesql_getstring".
On Error GoTo CannotCreateBaan

' Setup Column titles
Sheets(1).Cells(1, 13) = "Status"
Sheets(1).Cells(1, 14) = "Setup"
Sheets(1).Cells(1, 15) = "Runtime"
Sheets(1).Cells(1, 16) = "Qty."
Sheets(1).Cells(1, 17) = "Total Run Time"
Sheets(1).Cells(1, 18) = "Total Std."


'run Baan Application
Set BaanObj = CreateObject("Baan4.Application.ole701")
Sheets(1).Activate
BaanObj.Timeout = 10
On Error GoTo BaanAutomationError

' Setup the info to work through the spreadsheet
Row = 2
Column = 3
prod_order = UCase(Sheets(1).Cells(Row, Column))
While prod_order <> ""
operation = Sheets(1).Cells(Row, Column + 1)
index_string = Chr(34) & Chr(34) & Item & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & routing & Chr(34) & Chr(34) & "," & operation

Query = "select tisfc001.osta from tisfc001" & _
" where tisfc001._index1 = {" & prod_order & "}"
'MsgBox Query
' Execute query
B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function

' If this function fails the ReturnValue is equal to zero, otherwise
' the function olesql_parse returns a identification number of the query
' Convert the (string) ReturnValue to a long variable using the function Val
query_id = Val(BaanObj.ReturnValue)
If query_id = 0 Then
MsgBox "function olesql_parse fails"
GoTo BaanAutomationError
End If

' Fetch the record
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
runtime = 0
setup = 0
If BaanObj.ReturnValue = 0 Then
' retrieve query result and store it in the second argument of the function olesql_getstring
' Get Setup Time Number
temp_string = "tisfc001.osta"
order_status = 0
'order_status = String(20, " ")
B_function2 = "olesql_getint(" & Chr(34) & temp_string & Chr(34) & "," & order_status & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
temp_string = Mid(temp_string, 30, 10)
value_string = ""
found = False
For i = 1 To 10
If (Asc(Mid(temp_string, i, 1)) >= 48 And Asc(Mid(temp_string, i, 1)) <= 57) Then
value_string = value_string & Mid(temp_string, i, 1)
found = True
Else
If (found) Then
Exit For
End If
End If
Next i
order_status = Val(value_string)
End If
' Write setup time
Sheets(1).Cells(Row, Column + 10) = order_status
Sheets(1).Cells(Row, Column + 13).Select

' Get next order status
Row = Row + 1
prod_order = UCase(Sheets(1).Cells(Row, Column))

' stop and remove query
B_function = "olesql_break(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
If BaanObj.ReturnValue <> 0 Then GoTo BaanAutomationError
B_function = "olesql_close(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
Wend

' exit Baan
BaanObj.Quit
Application.Wait (Now + TimeValue("0:00:10"))
Set BaanObj = Nothing

Call Getsetupruntime
Exit Sub

' error handling
CannotCreateBaan:
MsgBox "Unable to start Baan"
Exit Sub

BaanAutomationError:
MsgBox "Baan IV automation error: " & BaanObj.Error
MsgBox "Return value function: " & B_function & " " & BaanObj.ReturnValue
BaanObj.Quit
Set BaanObj = Nothing
Exit Sub

End Sub
Sub Getsetupruntime()
' The DLL "ottdllsql_query" contains a function to convert this string to a Baan query and
' other functions to parse and execute the query. The DLL also contains functions to retrieve
' the result of the query, e.g. function "olesql_getstring".
On Error GoTo CannotCreateBaan

'run Baan Application
Set BaanObj = CreateObject("Baan4.Application.ole701")
Sheets(1).Activate
BaanObj.Timeout = 10
On Error GoTo BaanAutomationError

' Setup the info to work through the spreadsheet
Row = 2
Column = 3
prod_order = UCase(Sheets(1).Cells(Row, Column))
While prod_order <> ""
operation = Sheets(1).Cells(Row, Column + 1)
index_string = Chr(34) & Chr(34) & Item & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & routing & Chr(34) & Chr(34) & "," & operation
'index_string = Chr(34) & Chr(34) & Item & Chr(34) & Chr(34)

Query = "select tisfc010.sutm,tisfc010.rutm,tisfc010.qpln from tisfc010" & _
" where tisfc010._index1 = {" & prod_order & "," & operation & "}"
'MsgBox Query
' Execute query
B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function

' If this function fails the ReturnValue is equal to zero, otherwise
' the function olesql_parse returns a identification number of the query
' Convert the (string) ReturnValue to a long variable using the function Val
query_id = Val(BaanObj.ReturnValue)
If query_id = 0 Then
MsgBox "function olesql_parse fails"
GoTo BaanAutomationError
End If

' Fetch the record
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
runtime = 0
setup = 0
If BaanObj.ReturnValue = 0 Then
' retrieve query result and store it in the second argument of the function olesql_getstring
' Get Setup Time Number
temp_string = "tisfc010.sutm"
setup = 0
'order_status = String(20, " ")
B_function2 = "olesql_getint(" & Chr(34) & temp_string & Chr(34) & "," & setup & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
temp_string = Mid(temp_string, 30, 10)
value_string = ""
found = False
For i = 1 To 10
If (Asc(Mid(temp_string, i, 1)) >= 48 And Asc(Mid(temp_string, i, 1)) <= 57) Then
value_string = value_string & Mid(temp_string, i, 1)
found = True
Else
If (found) Then
Exit For
End If
End If
Next i
setup = Val(value_string)
'Get Runtime Time Number
temp_string = "tisfc010.rutm"
runtime = 99999.999
B_function2 = "olesql_getfloat(" & Chr(34) & temp_string & Chr(34) & "," & runtime & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
temp_string = Mid(temp_string, 30, 15)
value_string = ""
found = False
For i = 1 To 15
If ((Asc(Mid(temp_string, i, 1)) >= 48 And Asc(Mid(temp_string, i, 1)) <= 57) Or _
(Asc(Mid(temp_string, i, 1)) = 46)) Then
value_string = value_string & Mid(temp_string, i, 1)
found = True
Else
If (found) Then
Exit For
End If
End If
Next i
runtime = Val(value_string)
'Get qty planned
temp_string = "tisfc010.qpln"
qty = 99999.9999
B_function2 = "olesql_getfloat(" & Chr(34) & temp_string & Chr(34) & "," & qty & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
temp_string = Mid(temp_string, 30, 15)
value_string = ""
found = False
For i = 1 To 15
If ((Asc(Mid(temp_string, i, 1)) >= 48 And Asc(Mid(temp_string, i, 1)) <= 57) Or _
(Asc(Mid(temp_string, i, 1)) = 46)) Then
value_string = value_string & Mid(temp_string, i, 1)
found = True
Else
If (found) Then
Exit For
End If
End If
Next i
qty = Val(value_string)
End If
' Write setup time
Sheets(1).Cells(Row, Column + 11) = setup / 60#
Sheets(1).Cells(Row, Column + 11).Select
Selection.NumberFormat = "0.00"
' Write runtime
Sheets(1).Cells(Row, Column + 12) = runtime / 60#
Sheets(1).Cells(Row, Column + 12).Select
Selection.NumberFormat = "0.00"
' Write setup time
Sheets(1).Cells(Row, Column + 13) = qty
Sheets(1).Cells(Row, Column + 13).Select
Selection.NumberFormat = "0.00"
' Do total Runtime
Sheets(1).Cells(Row, Column + 14) = (qty * runtime / 60#)
Sheets(1).Cells(Row, Column + 14).Select
Selection.NumberFormat = "0.000"

' Do total
Sheets(1).Cells(Row, Column + 15) = (qty * runtime / 60#) + (setup / 60#)
Sheets(1).Cells(Row, Column + 15).Select
Selection.NumberFormat = "0.000"

' Get next order status
Row = Row + 1
' Item = UCase(Worksheets("Logfile").Cells(Row, Column))
prod_order = UCase(Sheets(1).Cells(Row, Column))

' stop and remove query
B_function = "olesql_break(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
If BaanObj.ReturnValue <> 0 Then GoTo BaanAutomationError
B_function = "olesql_close(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
Wend


' exit Baan
BaanObj.Quit
Set BaanObj = Nothing
Exit Sub

' error handling
CannotCreateBaan:
MsgBox "Unable to start Baan"
Exit Sub

BaanAutomationError:
MsgBox "Baan IV automation error: " & BaanObj.Error
MsgBox "Return value function: " & B_function & " " & BaanObj.ReturnValue
BaanObj.Quit
Set BaanObj = Nothing
Exit Sub

End Sub

You can use a product called B2Win. This will allow you to output it to word or excel

I am doing a new tool to export to Excel.
My way is by XML format.

You can create CSV file by using baan output.This is One of the ways. Otherwise there are some utulities for it but you should buy licence for them like B2win. I recommend you should examine all boards threads , I am sure you find best for yourself.


Good luck !