Excel, VBA: Parametrized query

Question

I have an excel vba function that pulls data from sql, the code is as below:

  Function GetDCF(vari As String)

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

  Dim cn As ADODB.Connection
     Set cn = New ADODB.Connection

       Dim strConn As String
          Dim sql As String


   strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=xxxxx;INITIAL CATALOG=xxxxx;INTEGRATED SECURITY=sspi;"

    cn.Open strConn


       sql = "select * from table1 where COMPANY = '" & Range("A2").Value & "' "

      rs.Open sql, cn, adOpenDynamic, adLockOptimistic

 GetDCF = rs.Fields(vari) / 100

cn.Close

Set rs = Nothing
Set cn = Nothing

End Function

Now everything works fine except when I have a company such as Mike's dog, the apostrophe prevent the sql query to work well. I guess I will switch to parametrized query. But I'm not sure how to do it in this case?

Thanks for any advice!

Answer 1

As I mentioned in comments, you could escape single quote in your sql query using Replace:

sql = "select * from table1 where COMPANY = '" & Replace(Range("A2").Value, "'", "''") & "' "