Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1 2 
SQLXL Requirements (Read 12059 times)
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
SQLXL Requirements
14.06.10 at 09:43:26
 
Hello,
 
I have created a spreadsheet using SQLXL and have also written some VBA of my own to help achieve what I want. I'm also using Oracle Objects for OLE (OO4O) to connect to my Database
 
The spreadsheet works fine on my PC and also a few other peoples PCs but I'm having  problems running the spreadsheet on other PCs.
 
The error I'm getting is 'Run-time error 91 Object Variable or with block variable not set'.
 
I have checked the Requirements for running SQLXL http://www.oraxcel.com/projects/sqlxl/help/trouble_shooting/required_environment .htm
 
I think I have the full Oracle Client 8I installed and have Oracle Objects for OLE (OO4O) installed, but how can I check that these are being used by my spreadsheet or if they are configured correctly how SQLXL?
 
Is there something else I'm missing? I can only think it is something to do with the setup of the PCs or the code would not be running on some PCs and not on others.
 
Any help would be appreciated, I have had this problem for a while and can't understand why it works on some PCs and not on others????
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #1 - 14.06.10 at 11:20:34
 
Hi,
 
First it is good to hear that your solution works ok on some of the PC's. Bugs in your code we can therefore rule out.
 
On the PC's where your code produces an error, it is likely that SQL*XL is not installed or that SQL*XL has problems accessing its dlls or accessing e.g. OO4O. Error 91 errors are often due to components not being available or that cannot be started.
 
To investigate what the problem is I would like to ask you to give the full error message. If the error occurs in SQL*XL you will be asked to send in the error report. Please do that so I can see where any problem in SQL*XL may originate. If the error occurs in your code, please show me the code where it fails. Please also send the:
Err.number, Err.description, Err.source.
 
Shall I move this topic to the VBA and macro forum for you?
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #2 - 14.06.10 at 12:34:43
 
Thanks for getting back to me.  
 
You can move it into the VBA and Macro forum if you think it would be best to put it there but it seems like a setup/installation problem, as the same code works on some PCs and not others.
 
The only message I get it the 'Run-time error 91 Object Variable or with block variable not set'.  
 
How can I check on a PC if what I have installed is what is needed for accessing an oracle Database with OO4O?
 
The line highlighted in bold in where the debugger stops with the 'Run-time error 91 Object Variable or with block variable not set' error. but like I said this code works fine on a number of PCs but doesn't on a number of others, please help.
 
SQLXL.Sql.setText "select lock_record from sw_fac_table where v_class= :vc;"
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    With Targets(litExcel)
         .AutoFilter = False
         .AutoFit = False
         .Headings = False
         .Sort = False
         .StartFromCell = "$AI$1"
         .Transpose = False
         .SQLInNote = False
    End With
    SQLXL.Database.Parameters.BindVariables("vc").Value = Worksheets(1).Cells(3, 1).Value
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litTypeNumber
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litParamIn
    With SQLXL.Sql.Statements(1)
         .ShowParametersDlg = False
         .ShowResultsetDlg = False
         .Execute
    End With
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #3 - 14.06.10 at 13:56:25
 
Can we try to see whether there is a problem with the value in cell(1,3)?
 
If you can program a message box before the line where you put the value to the parameter:
msgbox Worksheets(1).Cells(3, 1).Value  
 
I am curious to hear what happens on the machines that give the error.
 
Another thing you can check is whether the bind variable is in the collection. Put a breakpoint on this line:
SQLXL.Database.Parameters.BindVariables("vc").value = ...
 
When the execution stops there add the watch: SQLXL.Database.Parameters.BindVariables("vc")
 
Is the vc parameter in the collection?
 
I think you can also do SQLXL.Database.Parameters.BindVariables.count
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #4 - 14.06.10 at 14:23:28
 
It is the same spreadsheet and data that is running on the different PCs, so yes the value in cell(1,3) is exactly the same.
 
That is the most curious thing about this problem. I have been looking at this problem for a long time and running exactly the same code and data on the different PCs. It works fine on some and gives the 'Run-time error 91 Object Variable or with block variable not set' on others.  
 
This leads me to conclude that it must only be something to do with the setup of each indivdual machine. I don't know if its something to do with the oracle home, or something to with Oracle Client, or something to with OO4O, or something do with the install of SQLXL etc.
 
Please help.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #5 - 14.06.10 at 15:08:57
 
Hi,
 
You use worksheets(1) will be dependent on which workbook is the active workbook. That is why I asked you to print the value of the cell as a debug statement.
 
On the line you indicate you have already established the connection. If it error 91 on that line the interaction with SQL*XL does not work. It could be that the parameter vc is not in the list. Have you attempted to debug the line to see whether the parameter is in the list?
 
Is it the same version of SQL*XL on all the PC's?
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #6 - 14.06.10 at 15:38:17
 
Yes I activate the worksheet in my code - here is a fuller listing of it.
 
   '*******Check connection status*******
    If SQLXL.Database.Connected = True Then
        SQLXL.Database.Disconnect
    End If
    
    '*******Create Connection*******
    '**Change with regards to environment - so correct database is accessed**
    SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litOO4O
    SQLXL.Database.Connect "sw_prod", "sw_prod", "prod_db"
    
    '*******Activate Worksheet*******
    Worksheets(1).Activate
        
    SQLXL.Sql.setText "select lock_record from sw_factor_table where vessel_class= :vc;"
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    With Targets(litExcel)
         .AutoFilter = False
         .AutoFit = False
         .Headings = False
         .Sort = False
         .StartFromCell = "$AI$1"
         .Transpose = False
         .SQLInNote = False
    End With
    SQLXL.Database.Parameters.BindVariables("vc").Value = Worksheets(1).Cells(3, 1).Value 'The debugger highlights this line after displaying the Run-time Error 91  
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litTypeNumber
    SQLXL.Database.Parameters.BindVariables("vc").Mode = litParamIn
    With SQLXL.Sql.Statements(1)
         .ShowParametersDlg = False
         .ShowResultsetDlg = False
         .Execute
    End With
 
Yes it is the same version of SQLXL on all PCs - SQLXL 4.3.15
Back to top
 
« Last Edit: 14.06.10 at 15:47:34 by jahaan »  
  IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #7 - 15.06.10 at 13:56:13
 
Can anyone give me any pointers with the above please, as am running out of answers.  
 
Please help.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #8 - 15.06.10 at 15:31:45
 
I am thinking that perhaps the parameter was not created correctly. It would be nice if you could confirm that. Some SQL*XL versions have suffered a bug in that area int he past.
 
Please if you can display the SQLXL.Database.Parameters.BindVariables("vc").count it would already give an indication. If you can inspect the collection by adding it as a watch. I wonder whether SQL*XL failed to create the vc parameter.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #9 - 30.06.10 at 13:00:27
 
I have attached a screen shot that shows that the "vc" Bind Variable has been created correctly and populated. I created a variable (checkBindVar) for this exercise which is assigned the value in "vc" Bind Variable.
 
The screen shot shows that when I step through in debug and hover over the variable (checkBindVar) which gets assigned the value in the Bind Variable you can see that it is populated.
 
This brings me back to conclude that the code is working (the same code and data works a number of PCs), and that the problem is with the setup of the PCs on which the same code and data is not working.
 
This is why I would appreciate any advice on how exactly the PCs should be set up to run SQLXL.
 
AS mentioned previously....
 
I think I have the full Oracle Client 8I installed and have Oracle Objects for OLE (OO4O) installed, but how can I check that these are being used by my spreadsheet or if they are configured correctly how SQLXL?  
 
Is there something wrong with the Oracle Home not being set correctly?
Back to top
 

examplescreenshot.jpg
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #10 - 30.06.10 at 13:41:52
 
Hi,
 
But you have tested this on the computer that worked. What happens on the computer that does not work correctly?
 
It would be interesting to see what happens to this code on those PC's. Add this code before your assignment of the sheet value to the parameter.
 
msgbox worksheets(1).cells(3,1).value  
msgbox sqlxl.database.parameters.bindvariables("vc").value
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #11 - 01.07.10 at 14:47:52
 
Yes, that is what happened on a PC which runs the code without error.
 
However....... this morning I tried perform the same process on a PC I know isn't running the code without error.....
 
I started debugging and stepping through my code just like in my previous post but when I landed on the line which assigns a Value from Worksheets(1).Cells(3,1).value to the bind variable
(sqlxl.database.Parameters.bindvariables("vc").Value = Worksheets(1).Cells(3, 1).Value)
 
It threw up the Run-time error '91': Object Variable or with block variable not set just like before. Any Ideas why?
Back to top
 

examplescreenshot2.jpg
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #12 - 02.07.10 at 08:47:39
 
It is very likely that worksheets(1) is pointing to a sheet that you do not expect. Please take my advise and add the following debugging code so you know from which workbook it is picked up. But first I will explain once more why this could be a problem.
 
Worksheets(1) points to the first worksheet in the active workbook. However this workbook may not be the one you expect. Addin's (like SQL*XL but also the standard Excel addin's) are also in the list of workbooks (but they are not displayed as such) in addition to the other workbooks (xls files) that are open. Accidentally another workbook may be the last Excel handled and it may be the active one.
 
So before the assignment of the value to the parameter it is a good idea to ensure that you use it from the correct workbook. I don't know whether it is possible to qualify the call with an explicit workbook reference like this: workbooks("myworkbook.xls").worksheets(1).... but that would certainly be the safest. You can also use the object ThisWorkbook which always points to the xls or xla file from which you run the current macro. ThisWorkbook.Worksheets(1)... may be useful in that instance.
 
To know for sure from which file (xls or xla) you are taking the value insert this line before your assignment of the parameter:
msgbox "workbook=" & worksheets(1).parent.name
msgbox "worksheet=" & worksheets(1).name
msgbox "value=" & worksheets(1).cells(3,1).value
 
This will display the name of the workbook, the worksheet and the value of the cell before using it to fill the parameter.  
 
Run your macro on the PC's on which you suspect the problems and see what happens.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
jahaan
YaBB Newbies
*


I Love SQL*XL

Posts: 10
Re: SQLXL Requirements
Reply #13 - 06.07.10 at 14:21:57
 
I have tried adding the code as you suggested. Here is what happened when I ran it on a PC I knew it wasn't working on....
 
The First messagebox showed the workbook name eg: weightloader.xls.
The Second messagebox showed the worksheet name eg: worksheet1.
The Third messagebox showed the value in cell 3,1 eg: AIP.  
 
So it is referencing the correct workbook, worksheet and cell.
 
Once it got down to the line assigning the bind variable to the setText method SQLXL.Sql.setText "select lock_record from sw_fac_table where v_class= :vc; it threw up the 'Run-time error 91 Object Variable or with block variable not set' error as before. Any ideas why this is still happening?
 
Any help will be appreciated.
 
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLXL Requirements
Reply #14 - 06.07.10 at 15:58:02
 
Ok, so that proves that the worksheet value can actually be retrieved.  
 
Is it a coincidence you have an error on a different line now? Previously you reported the error was on the statement where the worksheet value was assigned to the parameter. Now you report that the error is on the setText line? That should be earlier in your code.
 
Is there a possibility that there are different versions of SQL*XL on the PC's. In the past there were versions in which parameter handling was buggy. Perhaps an idea to upgrade the PC's to the latest edition?
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1 2