Hi,

I'm learning how to do AFS from an Excel VBA macro, using the Baan samples, and many of the posts in this form, especially http://www.baanboard.com/baanboard/showthread.php?t=13049&highlight=functioncall and http://www.baanboard.com/baanboard/showthread.php?s=&threadid=774&highlight=excel.

As I understand it the functioncall property of the Baan OLE object should return the results of the Baan function. But when I run it, it returns just the statement I passed to the function. I think I'm missing something.

For example, I'm writing some code to add a line to a sales order. If the Baan DLL function is successful, it should return the new position number. This works if there is valid data in Excel. But now I'm trying to trap various errors. For example, if the item number is wrong, it should return the "item not found" error.

Here is my code:

Baan DLL Function:


function extern domain tcmcs.st30 add_line( domain tcorno i.orno,
domain tcitem i.item,
long i.oqua,
string err.msg(100))

{long ret

string rstr(30), value(10)

domain tcqsl1 o.qty


o.qty = i.oqua


stpapi.put.field("tdsls4101m000", "tdsls040.orno", str$(i.orno))

stpapi.find("tdsls4101m000")

stpapi.handle.subproc("tdsls4101m000", "tdsls4102s000", "add")

stpapi.continue.process("tdsls4101m000", err.msg)

stpapi.put.field("tdsls4102s000", "tdsls041.item", i.item)

stpapi.put.field("tdsls4102s000", "tdsls041.oqua", str$(o.qty))

ret = stpapi.insert("tdsls4102s000", 1, err.msg)

if ret = 0 then

rstr = err.msg

stpapi.recover("tdsls4102s000", err.msg)

else

stpapi.get.field("tdsls4102s000", "tdsls041.pono", value)

rstr=value

endif

stpapi.end.session("tdsls4102s000")

stpapi.end.session("tdsls4101m000")

return(rstr)
}


Here is the VBA code that calls the function (somewhat condensed for this example):


strDLL = "omySalesFunction"
lngSO = 123456
strItem = "BadItemCode"
lngQty = 1
strFunc = "add_line(" & lngSO & _
", " & Chr(34) & strItem & Chr(34) & ", " & _
lngQty & ", " & Chr(34) & Space(100) & Chr(34) & ")"

Set objBaan = CreateObject("Baan4.Application")
objBaan.ParseExecFunction strDLL, strFunc
varRetVal = objBaan.returnvalue
varErrVal = objBaan.Error
debug.print objBaan.functioncall


Sorry, this code is a bit rough still, and shows some trial and error.

When I run this, the functioncall is the same as the string I sent in ParseExecFunction, ie "add_line(123456, "BadItemCode", 1, "[100 spaces]").
I thought from all the examples I've seen that the last position of the functioncall would have contained the error code, instead of just 100 spaces, but it does not change.

The Baan DLL function right now returns "Item Not Found" in this case, instead of the position number. I want my VBA code to recongnize that it got an error back instead valid data. I know that I could just test for a long here, but I am trying to write generic code (a VBA class module actually) that will work in a variety of situations. The "Item Not Found" error does not generate anything reconginzed by objBaan.Error.

Is what I'm trying to do possible? Am I missing something?

Thanks,
Rob

I am not an expert and only played with this one time. What I did was add the errmsg as a parameter on the DLL. Like this:

function extern domain tcbool sfcdll9475.process.records(
domain tuole.data comp,
domain tccwoc main.f,
domain tccwoc main.t,
domain tccwoc sub.f,
domain tccwoc sub.t,
domain tcyrno year.f,
domain tcyrno year.t,
domain tcweek date.f,
domain tcweek date.t,
domain tiqbm2 effi.rate,
domain tisfc.dayp salary.perc,
string input.file(1024),
string report.file.name(1024),
ref domain tuole.data commmsg)

THen in the excel VB code I did this:

dllfunction = "sfcdll9475.process.records" & "("
dllfunction = dllfunction & comp$ ' Company
dllfunction = dllfunction & main_f$ ' main from
dllfunction = dllfunction & Main_t$ ' main to
dllfunction = dllfunction & sub_f$ ' sub from
dllfunction = dllfunction & sub_t$ ' sub to
dllfunction = dllfunction & year_f ' year from
dllfunction = dllfunction & year_t ' year to
dllfunction = dllfunction & date_f ' date from
dllfunction = dllfunction & date_t ' date to
dllfunction = dllfunction & efficiency ' Efficiency
dllfunction = dllfunction & salary_perc ' Salary Perc.
msg = "Start" + String(130, " ")
dllfunction = dllfunction & file1$ ' Input File
dllfunction = dllfunction & file2$ ' Report File
dllfunction = dllfunction & Chr(34) & msg & Chr(34) ' Report File
dllfunction = dllfunction & ")"
' Call SendtoBAAN(dllname, dllfunction)
Call SendtoBAAN
strlen = Len(BaanObj.functioncall)
compos = InStr(1, BaanObj.functioncall, "commmsg:", 1)
msg = Mid(BaanObj.functioncall, compos + 8, strlen - 3)
msg = RTrim(Mid(msg, 1, Len(msg) - 2))
If (msg <> "Done") Then
MsgBox msg
End If

I do not know if this is best. All I know is at the time I tested it, the code worked. Never did use this.

Thanks. It was the "ref" I was missing. It works great now.