Hi,

Every day I have to perfrom many automatic tasks, some of them involve downloading tables in excel and notepad format from BaaN, to monitor sales, orders etc... I am developing an Application that manage to perform most of those automatic tasks, however, when it comes to program the tasks that involve BaaN I am extremelly lost.

I was thinking on using some kind of macro in VB, like the ones I have found in the BaaN Client's folder called Samples. However I haven't been able to make the sample macros work (they get stack in the initial senstece Set Baan4Obj = CreateObject("Baan4.Application") reporting that ther program is unable to create a BaaN object.

I was looking for a simple tutorial to tell BaaN to download certain information in a Notepad or excel format without using the GUI.

Thank you in advance.

If u do not want to try 3rd party solutions , u can use baan exchange / easy sqls and report conversion to ascii files for getting files in xl / notepad . If u can code in baan writing wrappers around this functionalities , the soluton could be very much useful.

Hi,

Every day I have to perfrom many automatic tasks, some of them involve downloading tables in excel and notepad format from BaaN, to monitor sales, orders etc... I am developing an Application that manage to perform most of those automatic tasks, however, when it comes to program the tasks that involve BaaN I am extremelly lost.

I was thinking on using some kind of macro in VB, like the ones I have found in the BaaN Client's folder called Samples. However I haven't been able to make the sample macros work (they get stack in the initial senstece Set Baan4Obj = CreateObject("Baan4.Application") reporting that ther program is unable to create a BaaN object.

I was looking for a simple tutorial to tell BaaN to download certain information in a Notepad or excel format without using the GUI.

Thank you in advance.

This works but it is slow - or at least it is for me. On previous versions of the baan client that was the default. On the current version of the baan client there is a tab called automation. So for you default .bwc file check out what is in that tab - that is what you should put in createobject. Each .bwc can have a different automation connection so that you can call the one you want.

Ok, I have looked for the .bwc file (since that may solve the problem with the excel macro, which would let me use the sample macro to learn how baan VB work). I found the file but inside there were many fields but none of them was automation. Here is what it said:

[ApplicationServer]
bse=/baan/bse
bshell=bshell
command=
location=
connection=
[Remote]
hostname=somedomain
username=s444444
password=
logindialog=NO
serveros=
protocol=EXEC
portnumber=512
domain=
password64=
[NLS]
locale=ISO
RightToLeft=False
[Font]
fontName80=Fixedsys
fontWidth80=0
fontHeight80=-12
fontWeight80=normal
fontPitch80=fixed
fontItalic80=NO
fontName132=Fixedsys
fontWidth132=0
fontHeight132=-12
fontWeight132=normal
fontPitch132=fixed
fontItalic132=NO

Did I miss anything?

Maybe if I can fix what ever errors the sample macro gives me (since the macro is supposed to be 100% correct, that shouldn't take long) you can also address me to a tutorial/manual where I can learn how to refer to the data (Using VB) I would like to download. (I basically want to program what I can do manually with the GUI displaying thing in NOTEPAD).

By the way thank you for the rapid response.

There is not a tutorial that I am aware of. I learned most of this just playing with it. You can also search the AFS/DDC/OLE forum for additional hints - where this thread probably belongs. Let me edit your post first.

What client on you using? Mine includes this:

[Automation]
className=Baan4.Application.101
classId={41C2BC02-0991-431E-8053-CB0F46EFE0E3}
multiInstance=NO

You can see this in the bw configuration tab.

Sorry if this is a stupid question but I can't find the config tab. Where is it?

Is there any other way to find out how to call a BaaN object. Attach is all the screens I see when I execute the bw.exe in case it helps.

Thanks,

Sorry if this is a stupid question but I can't find the config tab. Where is it?

Is there any other way to find out how to call a BaaN object. Attach is all the screens I see when I execute the bw.exe in case it helps.

Thanks,

Hello,

see attachment !

Regards

The client automation tab is in the .bwc configuration. So when logging into baan before typing in password, hit the configure button on the bwclient. That will bring up the screen bdittmar has posted. Of course that assumes you are running a current client, some of the older clients did not have this option. You should also have this automation entry somewhere in the registry.

Also to get the entry into the registry just re-save the .bwc file. Also I noticed that the default configuration we use has Baan4.Automation.Default.

Then the problem is simple, My client does not have this capability since that tab does not exist, I got to that wind and everything looked ok except for the fact that the Automation tab was missing.

The solution I guess is probably to look for another client and install it my self.

You guys know any that for sure has that capability?

I haven't said it lately but thanks a lot for your help.

The version we are using is B40c.87. Something else to look for is called bw.reg(or maybe bwreg.bat something like that) it used to create the registry entries. Also use regedit and look for Baan4.application that needs to exist.

I post this little piece of code in VB .NET:


Imports System
Imports System.IO
Imports System.Text

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
startproc()
End Sub

Private Function Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim openFileDialog1 As New OpenFileDialog()
' ------ Put the right directory here:
openFileDialog1.InitialDirectory = "C:\Programmi\Baan\Baan IV\lib\user"
openFileDialog1.Filter = "BW Configuration File (*.bwc)|*.bwc"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
BAANFile.Text = openFileDialog1.FileName
Return openFileDialog1.FileName
Else
Return ""
End If

End Function

Private Sub searchtxt(ByRef obj As System.Windows.Forms.TextBox, ByVal strs As String, ByVal otxt As String)
Dim j
j = otxt.Contains(strs)
If j Then
obj.Text = otxt.Substring(Len(strs) + 1)
End If
End Sub


Private Sub getOLEname(ByVal filename As String)
Dim myStream As System.IO.Stream = Nothing
Dim text As String()
Dim i, j As Integer

Try
myStream = File.OpenRead(filename)
If (myStream IsNot Nothing) Then
text = File.ReadAllLines(filename)
For i = 0 To text.Length - 1
searchtxt(BAANBSE, "bse", CStr(text(i)))
searchtxt(BAANCls, "className", CStr(text(i)))
searchtxt(BAANHst, "hostname", CStr(text(i)))
Next
BAANTmp.Text = BAANBSE.Text & "\tmp"
End If
Catch Ex As Exception
MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
Finally
If (myStream IsNot Nothing) Then
myStream.Close()
End If
End Try
End Sub

Private Sub BaanConfig_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BAANFile.TextChanged
getOLEname(BAANFile.Text)
End Sub
End Class

Module main
Dim BAANObj As Object
Dim BAANFun As String


Sub startproc()

On Error GoTo BaanCannotCreate
BAANObj = CreateObject(Form1.BAANCls.Text)
BAANObj.Timeout = 10
On Error GoTo BaanAutomationError
' ----- Your code here
GoTo BaanEnd
BaanCannotCreate:
MsgBox("Unable to create baan object")
Exit Sub
BaanAutomationError:
MsgBox("Baan IV automation error: " & BAANObj.Error)
MsgBox("Return value function: " & BAANFun & " " & BAANObj.returnvalue)
BaanEnd:
BAANObj.Quit()
BAANObj = Nothing
Exit Sub
End Sub


In this way you can choose between all your BW configuration files interactively, the software uses the BAANCls.text variable it founds on the config file.

I had already double-clicked the bw.reg (and that was suppose to create what ever register I needed).

I searched with regedit and found BaaN4.Application (As you can see in the attachement). I don't know what to do now.

I have tried to execute one of the sample macros again in case something that I've changed had solved the problem, but instead I have gotten the exact same result ("Unable to start BaaN" giving the error when calling the constructor to create the Baan object).

I have attached the snapshot of the regedit result and below I've pasted the code of one of the sample macros I'am trying to run. Again just in case it can help.

/////////////////CODE///////////////////////////

Dim BaanObj As Object
Dim B_function As String
Dim B_function2 As String
Dim Query As String
Dim user As String
Dim temp_string As String
Dim query_id As Long
Dim RetVal As Long

Sub GetBaanUsers()
' This example will fill the spreadsheet with the names of BAAN IV users,
' with the criterium: user name > "tools"
' This macro parses the following string:
' "select ttaad200.user from ttaad200 where ttaad200.user > "tools""
' 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")
ThisWorkbook.Sheets("Users").Activate
BaanObj.Timeout = 10
On Error GoTo BaanAutomationError

' form the string to Visual Basic format. Mind how quotes (Chr(34)) are put in the string
Query = "select ttaad200.user from ttaad200 where ttaad200._compnr=000 and ttaad200.user > " & Chr(34) & Chr(34) & "tools" & Chr(34) & Chr(34)

B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
' execute the function olesql_parse("select ttaad200.user from ttaad200 where ttaad200.user > "tools"")
' from DLL ottdllsql_query
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
' 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
temp_string = "ttaad200.user"
user = String(10, " ")
' 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) & user & Chr(34) & ")"
BaanObj.ParseExecFunction "ottdllsql_query", B_function2

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

Row = 1
Column = 6
If user = "" Then MsgBox ("No users found")
While (user <> "")
' fill spreadsheet
Worksheets("Users").Cells(Row, Column) = user
Row = Row + 1
If Row > 10 Then
Row = 1
Column = Column + 1
End If

' Read next query result. If this function fails, the ReturnValue is not equal to zero
BaanObj.ParseExecFunction "ottdllsql_query", B_function
If BaanObj.ReturnValue = 0 Then
' retrieve query result and store it in the second argument of the function olesql_getstring
BaanObj.ParseExecFunction "ottdllsql_query", B_function2
temp_string = BaanObj.FunctionCall
' the second argument (user name) can be retrieved from position 35 of the FunctionCall
user = Mid(temp_string, 35, 10)
user = Trim(user) ' removes spaces from text string
ElseIf BaanObj.ReturnValue = 110 Then ' end of set
user = ""
Else
GoTo BaanAutomationError
End If
Wend

' 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

' 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

It seems that the ClassID is "Baan4 Application" instead of "Baan4.Application".
Try substitute the string in the VB program in CreateObject.

Yeah, I tried that as soon as I saw it, but still the same result. Is it possible that I don't have the dll needed or any other stuff needed. What files/resources would I need in order for the macro to be able to create the BaaNobj?

Why don't you reinstall a recent version of BW?

Yeah, I'll do that, that's a great idea.

Where and which I can find one that will 100% have the Automation capabilities. I am going to search baanboard and as my colleages.

Ok I found one that will have the automation.