Hi,

I need to query data from a reference table.

I have a reference sheet and users need to input certain
information, and to query certain data from this
reference sheet. The function is like a SQL. My question
is how to write a query in Excel VBA? Can I define this
reference sheet as a table and query it in VBA code?

Thanks

Ming

Hello, yes you can, here's a pertinent MS KB:

http://support.microsoft.com:80/supp.../q257/8/19.asp

And one recent example I cooked up:

http://mrexcel.com/board2/viewtopic....50393&start=31

Regards,
Nate Oliver

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!

Hello again Ming,

Instead of referring to columns (e.g., [A], [H], etc...) Fill row 1 with headers, and refer to those headers. E.g., [h] becomes the text in h1, e.g., [Header 8]. ADO can then interpret your data sheet as a table with row 1 identifying the fields.

Sql errors will error out on the open. Hope this helps.

Regards,
Nate Oliver

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!