How to set the correct SQL syntax to use WHERE?

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
Hello everybody in forum,

I've found the example code of how to use SQL in VBA in here query-table-with-excel-as-data-source.html (See Code below)

All the code works fine, but my issue is how to set the correct query to
print print Field2, Field3 where Field1 = 2008.

If I use below line works but prints all lines:
Code:
SQL = "SELECT [Name],[LastName] FROM [Employees$A2:P6]"

If I use below line, I get Run Time Error saying something like: "Some values haven't been established for some required parameters"
Code:
SQL = "SELECT [Name],[LastName] FROM [Employees$A2:D6] WHERE [Year]=2008"

If I use below line, I get Run Time Error too with the same error.
Code:
SQL = "SELECT [F2],[F3] FROM [Employees$A2:D6] WHERE [F1]=2008"

How would be the correct syntax in order to be able to use WHERE?

Code:
Sub Excel_QueryTable()
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim SourceFile As String
Dim qt As QueryTable

SourceFile = Application.ThisWorkbook.FullName
                     
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & SourceFile & ";" & _
                  "Extended Properties=Excel 12.0;" & _
                  "Persist Security Info=False"
                  
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

[COLOR=#0000cd]SQL = "SELECT [Name],[LastName] FROM [Employees$A6:P250]"[/COLOR] [COLOR=#008000]'Here is the line where I need help[/COLOR]

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets("Dest").QueryTables.Add(Connection:=oRS, _
Destination:=Range("B2"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

The sample database in sheet Employees is:
YearNameLastNameDepartment
2003MaryJ.Sales
2007JohnK.Adminstration
2008CarlM.accounting
2010AnnP.Security
2008PeterT.Restaurant

<tbody>
</tbody>

Thanks for any help.
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,367
Members
430,424
Latest member
ALHK022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top