Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 29th May 2002, 18:22
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Question on integrating Excel with BaaN.

Hi,
I am trying to connect to baan from Excel and extract some data back to Excel. There is lot of information already on this forum regarding this and it is very useful. Based on the information I read, I wrote this piece of code in excel (VB) and when I executed it, for some reason it is not able to create the object “Baan4.Application”. The program keeps going to CannotCreateBaan: routine and give me the error message “Unable to start BaaN”.

Here is the script:
Code:
Dim BaanObj As Object

Private Sub CommandButton1_Click()
Call open_baan
End Sub

Sub open_baan()
    On Error GoTo CannotCreateBaan
        
    Set BaanObj = CreateObject("Baan4.Application")
    BaanObj.Timeout = 3600
    On Error GoTo BaanAutomationError
    Exit Sub
    
CannotCreateBaan:
    Cells(2, 3) = "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

We are using BaaN IV on Unix with BW client. Does the above mentioned script only works if you are running baan on windows?

Any feedback is greatly appreciated.

Thanks,
NS

Last edited by ~Vamsi : 9th June 2002 at 01:46.
Reply With Quote
  #2  
Old 29th May 2002, 18:37
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Windows Client

Are you trying this on a Windows Client? It sounds like it, but I am not sure. Run regedit and look for HKEY_CLASSES_ROOT\CLSID\{00000000-4000-11ce-AAA8-00608C50C597}\LocalServer32 = C:\BAAN\bin\bw.exe /Automation. This is a copy from a line of the bw.reg file on my PC, your client should have a copy of it in the baan directory. If the entry is not found then you will need to run the bw.reg file on your PC to create the entries. I had the same problem when I first tried using OLE.

Mark
Reply With Quote
  #3  
Old 29th May 2002, 18:49
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Mark,
I checked the registry and there was no entry as you have mentioned. So, I ran the bw.reg file on my PC and it made an entry in the registry. When I re-ran my code after this, I got the same error message (“Unable to start Baan”) along with a BW error message.

Here is the BW error message:
"1: Error in CreateInstance(IUnkown*, REFID, LPVOID*). SendOleEvent timed out."

Any ideas.

Thanks for your help.
NS
Reply With Quote
  #4  
Old 29th May 2002, 19:33
gfasbender gfasbender is offline
Senior Member
 
Join Date: Aug 2001
Location: Seattle, WA USA
Posts: 172
gfasbender is on a distinguished road
Baan: Baan IV, Baan5, A&D extension - DB: Oracle, MSSQL, Informix - OS: Unix, NT
NS, you're running Baan IV not Baan 5 correct? Baan 5 uses object "Baan.Application" not "Baan4.Application".

You might want to also insure that you have the most current objects in Baan solution 13742.
__________________
Gordon Fasbender
GW Consulting
Reply With Quote
  #5  
Old 29th May 2002, 20:43
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Hi,
I was able to connect to Baan throug Excel but as soon as the program executes this "BaanObj.ParseExecFunction", I get an error message saying "Connection with server lost" and the application locks up.

Any suggestions?

Thanks,
NS
Reply With Quote
  #6  
Old 29th May 2002, 21:27
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
New Objects

Did you download the Baan solution Gordon mentioned? It seems to me this fixed this problem before for another user on the board. You might also want to post the code that will help us see if everything looks okay.

Good Luck!

Mark
Reply With Quote
  #7  
Old 29th May 2002, 21:49
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Mark/Gordon,
I am trying to download the patch rightnow but I am not sure how far I get with this since we don't have the BaaN support.

Here is the complete scirpt.
Code:
Dim BaanObj As Object
Dim B_function As String
Dim Query As String

Sub GetBaanUsers()
    If BaanObj Is Nothing Then
            ConnectBaan
        If BaanObj Is Nothing Then
            End
        End If
        End If
    ThisWorkbook.Sheets("Sheet1").Activate
    Query = "select ttaad200.user from ttaad200 where  ttaad200._compnr = 000"
    B_function = "olesql_parse(" & Query & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    query_id = Val(BaanObj.returnvalue)
End Sub

Sub ConnectBaan()
    Set BaanObj = CreateObject("Baan4.Application")
    BaanObj.Timeout = 2000000
    On Error GoTo CannotCreateBaan
    Exit Sub
    
CannotCreateBaan:
    Cells(2, 3) = "Can not connect to Baan"
    End
End Sub

Sub DisconnectBaan()
    If Not BaanObj Is Nothing Then
        BaanObj.Quit
        Set BaanObj = Nothing
    End If
End Sub
I also checked the log messages in baan (log.bshell) and it reports "Attribute of unkown type 355"

Appreciate all your help.

Thanks,
NS

Last edited by ~Vamsi : 9th June 2002 at 01:46.
Reply With Quote
Sponsored Links
  #8  
Old 29th May 2002, 23:30
alejandro's Avatar
alejandro alejandro is offline
Senior Member
 
Join Date: Feb 2002
Location: Madrid - Spain
Posts: 144
alejandro is on a distinguished road
Baan: IV c4 - DB: Oracle 8 - OS: Unix
Excel and BaaN OLE

When you run Excel have you a connection yet to BaaN?

If so, try to execute in BaaN this Object ottstpoledaemon. Then run your Excel code. To run it simply type ottstpoledaemon from menu option run program.

Also, which BW version are you using?
Reply With Quote
  #9  
Old 30th May 2002, 16:31
Al Smith Al Smith is offline
Member
 
Join Date: Mar 2002
Location: Marcellus, MI. USA
Posts: 52
Al Smith is on a distinguished road
Baan: Baan IVc4 - DB: Oracle - OS: Unix, NT, W2k
Hi,
You do have an error in your B_function string. The olesql_parse needs quotes ,chr(34), around the Query variable.

Code:
B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"

Al.
__________________
A computer is a tool, not a toy.

Last edited by ~Vamsi : 9th June 2002 at 01:47.
Reply With Quote
  #10  
Old 31st May 2002, 14:56
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Alejandro,
I tried your suggestion to run ottstpoledaemon and then try to run my code but, it would not work. We are using BW version B40c.61.

Al,
I did try your suggestion but got the same error message ("Connection with server lost").


What else could go wrong?

Thanks,
NS
Reply With Quote
  #11  
Old 31st May 2002, 15:36
Al Smith Al Smith is offline
Member
 
Join Date: Mar 2002
Location: Marcellus, MI. USA
Posts: 52
Al Smith is on a distinguished road
Baan: Baan IVc4 - DB: Oracle - OS: Unix, NT, W2k
Hi,
Do you know at what step the server connection is lost?

If not, try stepping through the code using F8.
After the Set BaanObj = CreateObject("Baan4.Application") is executed you should see the Option Dialog box at the bottom of your screen. Go into the shell and do a "PS". you should see the ttstpoledaem. Doing an "oic" should show the ottstpoledaemon object as loaded.
Continue to step through the code. After BaanObj.ParseExecFunction "ottdllsql_query", B_function is executed, again go into the Option Dialog shell and do a "oic". Now the ottdllsql_query object should be loaded.

Al.
__________________
A computer is a tool, not a toy.
Reply With Quote
  #12  
Old 31st May 2002, 19:15
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Al,
The connection with server is lost the movment it executes BaanObj.ParseExecFunction "ottdllsql_query", B_function

After connecting to baan i ran the "oic" commnad and it does shows ottstpoledaemon object as loaded.

I could not check for ottdllsql_query object since it breks out just after BaanObj.ParseExecFunction "ottdllsql_query", B_function.

You think I might have the old BaanObj objects? where are located?

Thanks for your help.
NS
Reply With Quote
  #13  
Old 31st May 2002, 19:57
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Well, this time it did not disconnect from baan but gave me this error message in log.bshell.

2002-05-31[13:21:30]:E:bsp: ******* S T A R T of Error message *******
2002-05-31[13:21:30]:E:bsp: Log message called from /port.6.1c.05.01/vobs/tt/mir/mir/main.c: #879 keyword: stop
2002-05-31[13:21:30]:E:bsp: Pid 1576 Uid 125 Euid 125 Gid 125 Egid 125
2002-05-31[13:21:30]:E:bsp: user_type S language 2 user_name bsp tty ote locale ISO88591/NULL
2002-05-31[13:21:30]:E:bsp: Errno 0 bdb_errno 213 (Transaction is started but not updated)
2002-05-31[13:21:30]:E:bsp: Log_mesg: bshell received SIGTERM
2002-05-31[13:21:30]:E:bsp: ******* E N D of Error message ******

Hope I am getting closer.

Thanks,
NS
Reply With Quote
  #14  
Old 31st May 2002, 21:40
alejandro's Avatar
alejandro alejandro is offline
Senior Member
 
Join Date: Feb 2002
Location: Madrid - Spain
Posts: 144
alejandro is on a distinguished road
Baan: IV c4 - DB: Oracle 8 - OS: Unix
Code Incomplete?

Looking your previous post, and this last with log.bshell it seems that you are not completing your query. You have created a query_id, but you do not do anything later. This is true only if you posted complete code.

What I think you have to put before EndSub line in Sub GetBaanUsers is:

Code:
    query_id = Val(BaanObj.ReturnValue)
    B_function = "olesql_fetch(" & query_id & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    temp_string = "ttaad200.user"
    user = String(10, " ")
    B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & user & Chr(34) & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function2
    temp_string = BaanObj.FunctionCall
    user = Mid(temp_string, 35, 10)
    user = Trim(user)
    'Then here you have the first user. I finish here, test it please, later we will comment
    B_function = "olesql_break(" & query_id & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    B_function = "olesql_close(" & query_id & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    BaanObj.Quit
    Set BaanObj = Nothing


Also when querying tables try to use index. In this case ttaad200.user is the key field, but I have used this query object with complex select statements and is mandatory to write them well. So the string query should be changed to:

Query = "select ttaad200.user from ttaad200 where ttaad200._compnr = 000 order by ttaad200._index1"

Hope this helps.

Last edited by ~Vamsi : 9th June 2002 at 01:47.
Reply With Quote
  #15  
Old 31st May 2002, 21:54
r_nagu's Avatar
r_nagu r_nagu is offline
Senior Member
 
Join Date: Mar 2002
Location: ~
Posts: 231
r_nagu is on a distinguished road
Baan: BaaN IV - DB: Oracle 8 - OS: UNIX
Alejandro,
The reason I did not complete the code is beacuse I was getting disconnected from baan with error message "("Connection with server lost")" as soon as the step "BaanObj.ParseExecFunction "ottdllsql_query", B_function " is executed.
I did copied your code and added to my privouslly posted code but, i still get the same error message i.e., "("Connection with server lost")" .

Here is the complete code:
Code:
Dim BaanObj As Object
Dim B_function As String
Dim Query As String

Sub GetBaanUsers()
    If BaanObj Is Nothing Then
            ConnectBaan
            MsgBox ("Connected..")
    Else
        MsgBox ("Already connected")
    End If
    
    If BaanObj Is Nothing Then
            End
    End If

    ThisWorkbook.Sheets("Sheet1").Activate
    Query = "select ttaad200.user from ttaad200 where ttaad200._compnr = 000 order by ttaad200._index1"
    B_function = "olesql_parse(" & Query & ")"
    B_function = "olesql_parse(" & Chr(34) & Query & Chr(34) & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    query_id = Val(BaanObj.returnvalue)
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    temp_string = "ttaad200.user"
    user = String(10, " ")
    B_function2 = "olesql_getstring(" & Chr(34) & temp_string & Chr(34) & "," & Chr(34) & user & Chr(34) & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function2
    temp_string = BaanObj.FunctionCall
    user = Mid(temp_string, 35, 10)
    user = Trim(user)
    'Then here you have the first user. I finish here, test it please, later we will comment
    B_function = "olesql_break(" & query_id & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    B_function = "olesql_close(" & query_id & ")"
    BaanObj.ParseExecFunction "ottdllsql_query", B_function
    Call DisconnectBaan
End Sub

Sub ConnectBaan()
    Set BaanObj = CreateObject("Baan4.Application")
    BaanObj.Timeout = 2000000
    On Error GoTo CannotCreateBaan
    Exit Sub
    
CannotCreateBaan:
    Cells(2, 3) = "Can not connect to Baan"
    End
End Sub

Sub DisconnectBaan()
    If Not BaanObj Is Nothing Then
        BaanObj.Quit
        Set BaanObj = Nothing
    End If
End Sub

Thanks,
Nagesh

Last edited by ~Vamsi : 9th June 2002 at 01:48.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ervaren Baan specialist ICT Builder Jobs and Resumes 3 5th February 2005 06:37
Baanboard.com Community Bulletin 01/05/2002 patvdv About Baanboard.com 0 1st May 2002 15:04
Importing Excel Files To Baan Nazeem Finance, Invoicing and Integration 9 17th April 2002 16:42
BSP-US (LLC) added as a Baan Education Alliance svandenh General Discussion & Chat 1 20th August 2001 19:44


All times are GMT +2. The time now is 00:20.


©2001-2017 - Baanboard.com - Baanforums.com