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
Parameters and runtime error 91 (Read 3525 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Parameters and runtime error 91
29.01.12 at 14:56:55
 
Parameters and runtime error 91
 
Quote:
Receive runtime error 91, Object variable or With block variable not set when executing this code

With SQLXL.database.Parameters.Bindvariables("FY")
.Value = Worksheets("Sheet1").Cells(1, 1).Value
.Mode = litTypeVarChar2
.Mode = litParamIn
End With

Query worked perfectly until upgrading to SQLXL5. Nothing else has changed. Code still works perfectly on other PCs still using SQLXL4.

 
The Parameters collection have been moved to the statement and they are not a member of the database class anymore.
 
Please review the following example where I have recorded the running of the following statement: select:myvar
 
I hope it is self explanatory.
 
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/01/2012 by Gerrit-Jan
'

'
  SQLXL.Sql.setText "select :myvar"
  Set SQLXL.Sql.Statements(1).Target = SQLXL.Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With SQLXL.Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "$C$8"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  SQLXL.Sql.Statements(1).BindVariables.Add "myvar"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(1).BindVariables("myvar")
    .DataType = litTypeOther
    .Mode = litParamInOut
    .Value = "44"
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub
 


 
Back to top
 
 

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