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
Unable to assign variable to result SQLQueryValue (Read 2510 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Unable to assign variable to result SQLQueryValue
16.10.09 at 12:18:23
 
Unable to assign variable to result SQLQueryValue
 
Quote:
We are unable to assign a variable to the sqlqueryfunction result. The workaround is to use a cell formula and then read the cell. There is no information on how to assign the values from this function to a VBA type/object. Is it possible for you to provide an example?

 
You cannot use the SQLQueryValue function to do this. It is a cell function and it refers back to the cell or to the cells (if it was used as an array function) in which it was typed. A simple call like this will not work:
Code:
Dim v As Variant
v = SQLXL.SQLQueryValue("select * from emp")
 


Your call will result in:
#SQL*XL Universal error: unhandled error (please report): 424:Object required
 
To do what you require please see the code below.  
 

  • I started a new workbook.
  • I used the SQL*XL front-end to connect to my database (using ORAOLEDB)
  • Entered the VB environment (Alt-F11)
  • Selected the new project (Book2)
  • In tools-references I added a reference to SQL*XL
  • I added the following sub:
    Code:
    Public Sub test()
        Dim dyn As Object
    
        SQLXL.Sql.setText "select * from emp"
        SQLXL.Sql.Statements(1).Execute
        Set dyn = SQLXL.Sql.Statements(1).Dynaset
    
        dyn.MoveFirst
        While Not dyn.EOF
    	  MsgBox dyn.Fields(1).Value
    	  dyn.MoveNext
        Wend
    End Sub
     
    
    


 
This code assumes you have built a connection manually. This can also be automated of course.
Back to top
 
« Last Edit: 16.10.09 at 13:07:08 by Gerrit-Jan Linker »  

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