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:
If I use below line, I get Run Time Error saying something like: "Some values haven't been established for some required parameters"
If I use below line, I get Run Time Error too with the same error.
How would be the correct syntax in order to be able to use WHERE?
The sample database in sheet Employees is:
[TABLE="width: 372"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Name[/TD]
[TD]LastName[/TD]
[TD]Department[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]Mary[/TD]
[TD]J.[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]John[/TD]
[TD]K.[/TD]
[TD]Adminstration[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]Carl[/TD]
[TD]M.[/TD]
[TD]accounting[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]Ann[/TD]
[TD]P.[/TD]
[TD]Security[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]Peter[/TD]
[TD]T.[/TD]
[TD]Restaurant[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for any help.
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:
[TABLE="width: 372"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Name[/TD]
[TD]LastName[/TD]
[TD]Department[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]Mary[/TD]
[TD]J.[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]John[/TD]
[TD]K.[/TD]
[TD]Adminstration[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]Carl[/TD]
[TD]M.[/TD]
[TD]accounting[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]Ann[/TD]
[TD]P.[/TD]
[TD]Security[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]Peter[/TD]
[TD]T.[/TD]
[TD]Restaurant[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for any help.
Last edited: