mercredi 25 février 2015

VBA, Excel ODBC driver - Type Mismatch error while using sql query with parameter from cell

I have problem with Type Mismatch error while I do a select with getting parameter from excels cell. I am using excel like a database. My code is:



Sub maxlg()

Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String



DBPath = ThisWorkbook.FullName

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
sSQLSting = "SELECT SUM(Sicherkoef*LG/KFM/'" & Range("U17") & "') AS MAXLG From [DATABASE$] WHERE Platzierung =('" & Range("S11") & "') AND RPJ = ('" & Range("U12") & "') "
mrs.Open sSQLSting, Conn
'=>Load the Data into an array
'ReturnArray = mrs.GetRows
''OR''
'=>Paste the data into a sheet
ActiveSheet.Range("V12").CopyFromRecordset mrs
'Close Recordset
mrs.Close


Conn.Close

End Sub


Problem is in getting parameter from here: RPJ = ('" & Range("U12") & "') " in the WHERE clause.


Aucun commentaire:

Enregistrer un commentaire