This is my first script trying to use Baan OLE functions in Excel.

I copied a script previously posted in this forum, and modified it in order to try to understand it.

this is the script :

Sub lookup_customers()
Dim BaanObj As Object
Dim B_function As String
Dim Customer
ErrorPositionCode = 2

Set BaanObj = CreateObject("Baan4.Application")

BaanObj.Timeout = 10

Query = "select tccom010.cuno from tccom010 " & _
"where tccom010.cuno = " & Chr(34) & Chr(34) & "004302" & Chr(34) & Chr(34)
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"
End If

' Fetch the record
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
If BaanObj.ReturnValue = 0 Then
MsgBox ("value is " & query_id)
Customer = Val(query_id)
Else
End If
Sheets(1).Cells(2, 2).Value = Customer

BaanObj.Quit
Set BaanObj = Nothing
End Sub


It returns a number, probably the Query ID.
But not the customer fetched from Baan.
This is a very simple example made just that I can understand how it all works.

Questions unclear for me is ? Should there be a query defined in Baan before I can use the OLE function in Excel or not ?

Can someone put me on the right track. Does anyone have a simple working example for me to get started with ?

Many thanks in advance !!!

Hi Patrick,

the getstring function is missing.

Try after fetch:

B_function1 = "olesql_getstring(""tfccom010.cuno"","" "")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function1
temp_string = BaanObj.ReturnCall
customer = Mid(temp_string, 35, 8)

Looks like the only thing you are missing is the olesql_getint. You should also have some examples that come your baan client - BAAN.XLS. It fetches user-ids. I think there are better examples in AFS/DDC/OLE forum. Probably should search that forum.


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)

Hi,

I tried your suggestion but BaanObj.ReturnCall is a not know object property ?

Second the table you mentioned was not correct it should have been tccom010.cuno.
So I changed that but no success ? :-(

IT stops at the ReturnCall line as unknow property

Still unclear if I need to have a Query in Baan alread predefined, before I can use the OLE query. Or is this not needed ?

can anyone tell me ?

Is there anymore information available somewhere. Not to try to reinvent the wheel again.

First as moderator - please do not cross post. I just found this and have merged the threads.

Second - no need to define anything in Baan. You do your query on tccom010 then use oleget functions to retrieve your output. So I think all you need is to use one of the olegets and your done.

Is there any documentation available somewhere.

Because I checked throught the forum and there is no example that fits our system (we use Process and not manufacturing).

As a result most of the examples posted are not helping me.

A simple working example would be OK to get started,like reading the customers or items.

Thanks

Don't hold the coding against me. :) I used your code and added a few lines. The only place I ever used this was on the manufacturing side. I no longer use any of this. I know there are better ways to do what I did.


Sub testit()
Set BaanObj = CreateObject("Baan4.Application")

BaanObj.Timeout = 10

Query = "select tccom010.cuno from tccom010 " & _
"where tccom010.cuno = " & Chr(34) & Chr(34) & "SP0900" & Chr(34) & Chr(34)
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"
End If

' Fetch the record
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
If BaanObj.ReturnValue = 0 Then
MsgBox ("value is " & query_id)
customer = Val(query_id)
Else
End If
temp_string = "tccom010.cuno"
customer = String(6, " ")
' The function olesql_getstring retrieves the query result and stores it in the second argument,
' a 'call by reference' argument. Note that the second argument must be large enough to fill in the result
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & customer & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2

' search returned string in the function call (call by reference argument)
temp_string = BaanObj.FunctionCall
customer = Mid(temp_string, 35, 6)
customer = Trim(customer)


Sheets(1).Cells(2, 2).Value = customer

BaanObj.Quit
Set BaanObj = Nothing

End Sub

Thanks.

Is there any documentation available in order to dig in more deeper.

like how may quotes " " should be placed where in the SQL statement.

And if I would like to add an other field, do I need to created an extra "temp_string" in VB etc.

All of this is not yet clear.
In order to not bug you all the time, I am willing to read through some documentation.

But where to find it ?

Sorry - I know of no documentation. What I did was play with the examples that came with the client.

For info. I don't think the OLE examples are shipped with the later releases of bw