Looks like you are missing a space in your SQL e.g.

'PURCHASE/REFINANCE'" & _
" and [e] < 1000000"

--

Ming Shao wrote in message ...
Dear Nate Oliver

Thanks for your code. But when I run my own code, I got the error
message as follows

.Open , , 3, 3
"On value given for one or more required parameters"

Attached is my code. What is wrong?

Thanks

Ming

Sub GetMaxLTV()

Dim cn As Object, rs As Object
Dim clcMde As Long
Dim StdMx As Object

clcMde = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Sheets("StdM").[a2:n81].ClearContents

Set cn = CreateObject("ADODB.Connection")
Set StdMx = Worksheets("stdM").Range("A1:N81")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" 'Create
DB connection

Set rs = CreateObject("ADODB.Recordset")

With rs
Set .ActiveConnection = cn
.Source = "Select [H]" & _
"From [StdM$A1:H81] where [a] = 1 " & _
"and [b] = 'FULL' and [c]= 'Second Home' and [d] =
'PURCHASE/REFINANCE'" & _
"and [e] < 1000000"
.Open , , 3, 3
Sheets("Pricing").[a1].CopyFromRecordset rs
.Close
End With

cn.Close
Set rs = Nothing: Set cn = Nothing

Application.Calculation = clcMde
Application.ScreenUpdating = True

End Sub





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!