I have been trying to figure out how to develop a VBA macro in excel to get all relevant information about a specific part number. I have been playing around with the examples in the forum just trying to get some information of any kind as a test. I am connecting to Baan, I just don't get any information back. I am trying to get locations/quantities, Past Purchase Orders, Past Adjustments, Price, Desc, and Price. It would be more just one part number at a time. I realize it is likely a problem with my query but I am not sure.

Here is what I have right now...

Private Sub CommandButton1_Click()

Set BaanObj = CreateObject("Baan.Application")

BaanObj.Timeout = 10

' Query = "select tiitm001.citg, count(tiitm001.item) from tiitm001 " & _
"where tccom010.cuno = " & Chr(34) & Chr(34) & "SP0900" & Chr(34) & Chr(34)
Query = "select tfgld011.ttyp from tfgld011 where tfgld011._compnr=100 and tfgld011.ttyp"
B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function

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

' 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


What am I screwing up here. Also does anyone know the table names I have to reference for the part info.

Thank you in advance for your assistance. :)

Yes - I did not see a query that looked correct. Here is one I played with at one point:


Query = "select tdpur040.*,tccom020.* from tdpur040,tccom020 where tdpur040.orno = " & PO & _
"and tdpur040.suno refers to tccom020 as set with 1 rows"

B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
' execute the function olesql_parse from DLL ottdllsql_query
BaanObj.ParseExecFunction "ottdllsql_query", B_function


Then I used this to extract part of the data:


Sub get_header_info()
' The function olesql_fetch reads one query result, in this example the (string) value of ttaad200.user
' The identification number of the query must be passed as argument to this function
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function

' Get Position Number
temp_string = "tdpur040.suno"
Supplier = String(6, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & Supplier & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
Supplier = Mid(temp_string, 35, 6)
Supplier = Trim(Supplier)

' get supplier name
temp_string = "tccom020.nama"
supplier_name = String(35, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & supplier_name & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
supplier_name = Mid(temp_string, 35, 35)
supplier_name = Trim(supplier_name)

' get supplier address
temp_string = "tccom020.namc"
supplier_address = String(30, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & supplier_address & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
supplier_address = Mid(temp_string, 35, 30)
supplier_address = Trim(supplier_address)

' get supplier city_state
temp_string = "tccom020.name"
city_state = String(30, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & city_state & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
city_state = Mid(temp_string, 35, 30)
city_state = Trim(city_state)

End Sub



Good Luck!

What are the standard table/field names I want to use for the info I am looking to extract into Excel? I can then modify my query for the correct info once I get it working with this data.

Not sure I understand your question - are you asking for actual table/field names? If so then you need to get a login to baan itself and look at the actual table structures. I can only relate what our system does and we are on the A&D version of Baan.

I got the table and fields names I need for our install of Baan but it still isn't returning any data. Do you see what I'm doing wrong?

Set BaanObj = CreateObject("Baan.Application")

BaanObj.Timeout = 10

Query = "select whinr140.* from whinr140 where whinr140.item = 'SUP9X14'"

B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
' execute the function olesql_parse from DLL ottdllsql_query
BaanObj.ParseExecFunction "ottdllsql_query", B_function

query_id = Val(BaanObj.ReturnValue)
If query_id = 0 Then
MsgBox "function olesql_parse fails"
End If

' The function olesql_fetch reads one query result, in this example the (string) value of ttaad200.user
' The identification number of the query must be passed as argument to this function
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function

' Get Location
temp_string = "whinr140.loca"
Supplier = String(6, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & Supplier & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
Location = Mid(temp_string, 35, 6)
Location = Trim(Location)


BaanObj.Quit
Set BaanObj = Nothing

(1) Are you sure of that item number?
(2) Try some other fields.

I copied your code, put in my table and it worked.


et BaanObj = CreateObject("Baan4.Application.503")

BaanObj.Timeout = 10

Query = "select tiitm001.* from tiitm001 where tiitm001.item = 'PLM7249020'"

B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
' execute the function olesql_parse from DLL ottdllsql_query
BaanObj.ParseExecFunction "ottdllsql_query", B_function

query_id = Val(BaanObj.ReturnValue)
If query_id = 0 Then
MsgBox "function olesql_parse fails"
End If

' The function olesql_fetch reads one query result, in this example the (string) value of ttaad200.user
' The identification number of the query must be passed as argument to this function
B_function = "olesql_fetch(" & query_id & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function
' Get Location
B_function2 = ""
temp_string = "tiitm001.ccur"
Supplier = String(32, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & Supplier & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
Location = Mid(temp_string, 35, 32)
Location = Trim(Location)
' Get Location
B_function2 = ""
temp_string = "tiitm001.csig"
Supplier = String(32, " ")
B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & Supplier & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
Location = Mid(temp_string, 35, 32)
Location = Trim(Location)


BaanObj.Quit
Set BaanObj = Nothing
End Sub


Other things to think about(that I had impact this before) - tools version, porting set and the client. I no longer use this method to get data, but I have had updates to each of these components that caused problems. So the code looks good - but I can not speak to the item number and table. What made me think of this was that you still have baan.application. I set this on the automation tab on the client - something different for each company I log into. 503 is a test company for me. Are you actually getting connected? Makes me wonder about the client.

Baan4.Application.503 doesn't work for me but Baan.Application does. I am using Baan version 5 I believe. When I go step by step...

When Set BaanObj = CreateObject("Baan.Application") is run in the code it shows the Baan Login Dialog and I login using my user/pass and I don't get an error. The option dialog is open so I think am connected.

the query_id seems to be increased by 1 each time I try to do a query (unless I close the Options Dialog and login again)

temp_string returns
olesql_getstring("whinr140.ccur"," ")

I don't understand how this is supposed to work or why it's not working. Doesn't Supplier = String(32, " ") make Supplier just 32 blank spaces?

The baan4.application.503 is what I setup when using the client. When you run the bw configurator tab for is "automation". This is where I set the connection name - the last three are the company I connect to. Of course I am running version B40c.80 of the client - yours could be different. If your client supports this you may want to try - just to make sure you are connecting to the correct company.

Yes (Supplier = String(32, " ")) sets supplier to 32 spaces. The field needs to be large enough to accept the data. The olesql_getstring actually parses the string into the field you select. So be 100% sure you query is working and should contain data. See the attached jpg. I put it in debug mode so you could see what should happen.

This is what I'm getting different from yours. I am still playing with it. Hopefully I'll figure it out. :(

Can you run exactly what I did? Not sure if tiitm001 is the same between our systems.

At what point does the temp_string get filled with the data? I noticed yours has USD but the value opf my temp_string is:
olesql_getstring("whinr140.loca"," ")

Your query id is a longer number. Mine increases by one each time I try a query. It is 1 the first time, then 2, then 3, and so on. Is it supposed to be a longer number like 10526 or something. Maybe that is the problem somehow.

Is there a way get read any and all fields which have been retrieved with *? I am obviously making a connection and the table is correct becuase the query_id is not 0. I checked with the databawse people and they say the fields they provided me are correct. I'm tearing my hair out and I know it's probobly something simple too.

grrrr

temp_string gets filled right after this statement temp_string = BaanObj.FunctionCall.

No clue about that query_id - I just ran it again and it came up with the same query id. Ran it a third time to check - the query id is always the same.

DO not make the mistake of thinking it is simple. As mentioned above(and one reason I no longer use this method) is that the client, the porting set and the tools versions can all effect trying to get data from Baan. One of the reasons I am running out of ideas.

I know this tread is old but incase someone like me who are new in Baan Automation and using BaanV or BaanLN client.
The secret is:

' Baan4
strlen = Len(BaanObj.FunctionCall)
tstr = BaanObj.FunctionCall

' Baan5
'strlen = Len(BaanObj.ReturnCall)
'tstr = BaanObj.ReturnCall

Hope it will help others.
Nath

I know this tread is old, but just in case if someone like who is new in Baan Automation and using Baan5 or BaanLN client. The secret is:

For Baan5:
temp_string = BaanObj.ReturnCall

For Baan 4:
temp_string = BaanObj.FunctionCall

hope it will help others
nath