Sub CustomQuery()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strPath As String
Dim newStrSQL As String
Dim oldStrSQL As String
Dim strQryName As String
Dim myArr()
Dim objCell As Object
Dim lstRow As Long
lstRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim myArr(0 To lstRow - 2)
'lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim j As Integer
j = 0
For Each objCell In Range("A2:A" & lstRow)
myArr(j) = objCell.Value
j = j + 1
Next objCell
strPath = "C:\Users\New User\Desktop\Vlookup.mdb"
Dim i As Integer
newStrSQL = "SELECT Symbol, Prices FROM Table1" _
& " WHERE Table1.Symbol IN ("
For i = 0 To UBound(myArr)
newStrSQL = newStrSQL & "'" & myArr(i) & "', "
Next i
' trim off trailing comma and append closing paren
newStrSQL = Left(newStrSQL, Len(newStrSQL) - 2) & ")"
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & strPath
Set cmd = New ADODB.Command
'Set cmd = cat.Views(strQryName).Command
'oldStrSQL = cmd.CommandText
Debug.Print oldStrSQL
cmd.CommandText = newStrSQL
Debug.Print newStrSQL
'Set cat.Views(strQryName).Command = cmd
Set cmd = Nothing
Set cat = Nothing
End Sub
Basically, the code above works fine, but the results appear in the
Immediate Window. I’m trying to figure out how to get the results of
a query to my spreadsheet, starting in Cell B2, and going down. Now,
the results of the query are displayed in the Immediate Window, but I
actually need to write the records from the query to the sheet. Can
someone please tell me how to do that??!!!
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
