for each loop on recordset, perform new strsql query

[batador]

Forgive my ignorance right off the bat, I am a fabricobbler. I am trying to modify an existing query. My thought process is that I can let this strsql query run, populate the results to excel, then query a specific range in the query for the date range, run a for each loop on the range to query a sql database, compare the date ranges and pull in a shift name. I can get the macro all the way to the point of looking at the specified range, debug that range to the immediate window, but I can't get it to populate the querytable to the specified range. It acts like it doesn't even try to look at the sql table I am telling it to look at. I will attempt to paste the code below. Also, this code is written after the fact, and placed in the original macro as a call method. I am wide open to any and all suggestions as to how to make this better. I have perused the forum and google for far too long, I am sure this question has been posed before and I apologize. There are also snippets of different methods I have tried interlaced all throughout the code, and for that I apologize.



Sub GetShift()




Dim strsql As String
Dim rg1 As Range
Dim i As Long
Dim j As Variant
Dim coll As New Collection
Dim ary As Variant






Sheets("Data").Select
'rg1 = Range("C2:C10000")
Range("C2", Range("C" & Rows.Count).End(xlUp)).Select
ary = Selection



'Set rg = Sheets("Data").Range("C2", Range("C" & Rows.Count).End(xlUp)).Select
'coll.Add rg1
ary = selection


For Each j In ary

Debug.Print j

strsql = "SELECT Hist_Shift_Code"
strsql = strsql & " From ""1802_SHIFT"""
strsql = strsql & " where ""Hist_Shift_Start"" >= '" & ary & "' " 'I have tried umpteen different names here for the date ranges. this was a last attempt, handful of noodles at the wall
strsql = strsql & " and ""Hist_Shift_Start"" < '" & ary & "'"


server = "ODBC;DSN=*****" 'I commented out the actual server name for security reasons


Next


On Error GoTo ErrorHandler

With ActiveSheet.QueryTables.Add(server, _
Destination:=Range("A1"))
.Sql = (strsql)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True


End With



' End If

' Next I

ErrorHandler: ' Error-handling routine.




End Sub

[ranman256]

you dont loop thru recordset, you run a query (update or other)

[Welshgasman]

This appears to be Excel VBA (in an Access forum? )
How about Debug.Print strSQL ?, after all, that is the pertinent code, surely?
I would also expect you to use J, as that is the each cell in the selected range?

[batador]

This appears to be Excel VBA (in an Access forum? )
How about Debug.Print strSQL ?, after all, that is the pertinent code, surely?
I would also expect you to use J, as that is the each cell in the selected range?

You are correct, this is excel vba. I apologize for posting here, didn't pay attention and thought I was in the excel forum I was looking at. I have multiple memberships with different sites. I will post in the correct forum. Appreciate both of y'all's replies.

[Micron]

Seems like you need this site
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

One thing that doesn't look right to me is that Ranges (at least as you are coding them) are objects and you shouldn't just pass a range to a variant. It might figure out that it's supposed to be an object, but maybe not. Using SET to allocate the range to an object variable would be better. Also, IMO you should get the count of populated rows and use a counter like 1 (or something else if you have row headers) to a variable that equals that row count and loop that way, not For Each object (range or cell) in an object (range). More like
For i = 2 To lngRowCount
Range("A" & i)... now do something with/to the range.

Please use code tags (see # button on forum toolbar) when posting more that a couple of lines of code, and maintain indenting.