Help Looping SQL retrieve and filling data.

[MikeWaller]

Hello All,

I've got two functions(actually a lot more than that, but if I can get help figuring these out I think I can handle the rest),one to retrieve data from an SQL database and one to fill the data. Originally, I had it using a static range for date and times and then filling to a static cell. I think I may have the loop for the retrieve, but I can not, for the life of me, figure out how to loop the fill part.
Here is the function for retrieving so far:

 Private Function GetDataPT() As Boolean
   
   On Error GoTo ERROR_TRAP
   
   Dim sStartCSN     As String
   Dim sEndCSN       As String
   Dim dtStartDate   As String
   Dim dtEndDate     As String
   Dim sSQL          As String
   Dim ProcStateUserID As String
   Dim LastCol       As Integer
   Dim FirstCol      As Integer
   Dim ws As Worksheet
   
   
   For FirstCol = 1 To LastCol
   LastCol = Worksheets("Settings").Cells(19, Columns.Count).End(xlToLeft).Column
   If LastCol < 2 Then LastCol = 2
   
   Set ws = ThisWorkbook.Worksheets(8)
   
   'Display Status
   ws.Cells(3, 6) = "Getting Data"
   
   GetDataPT = False

   'Create Query
   sSQL = "SELECT CAST(CSN as varchar(25)) as CSN, CAST(VIN as varchar(25)) as VIN, ContID, ProcDefName, ProcStateStart, ProcStateEnd, ProcGrpName, ProcStateUserID, ProcStateTranType, ProcStateComp"
   sSQL = sSQL & " FROM vblReportIMProcStateWithContAttrib"

'Time ranges are A19-B19,B19-C19,C19-D19 etc.
'   Start Time is in A19 - V19 on Run Worksheet
   dtStartDate = Worksheets("Settings").Cells(19, LastCol - 1)
'   End Time is in B19 - W19 on Run Worksheet
   dtEndDate = Worksheets("Settings").Cells(19, LastCol)
   
   
   If dtStartDate <> #12:00:00 AM# Then
      If dtEndDate <> #12:00:00 AM# Then 'apply both
         sSQL = sSQL & " WHERE ProcStateEnd >= '" & dtStartDate & "' AND ProcStateEnd <= '" & dtEndDate & "'"
      Else 'apply just start
         sSQL = sSQL & " WHERE ProcStateEnd = '" & dtStartDate & "'"
      End If
    
       sSQL = sSQL & " AND ProcGrpName in ('PowerTrain')"
      sSQL = sSQL & " AND ProcStateComp = 'YES'"
      sSQL = sSQL & " AND ProcStateTranType = 'Completed'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMRSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV2'"
      sSQL = sSQL & " AND ProcDefName <> 'RSSSV'"
      sSQL = sSQL & " AND ProcDefName = 'PT002L'"
      sSQL = sSQL & " ORDER BY CSN"
      
      Set moRSWips = New Recordset
      moRSWips.Open sSQL, moDBConn
      Set moRSWips.ActiveConnection = Nothing
      GetDataPT = True
   Else
      MsgBox "No Start Date Entered", vbInformation, "GetDataPT"
   End If
 
Next FirstCol
Exit_Function:
   Exit Function
ERROR_TRAP:
   Select Case Err.Number
   Case 0
   Case Else
      DisplayError msModName & "GetDataPT", Err.Number, True, Err.Description
      Err.Clear
      Resume Exit_Function
   End Select
   
End Function

And here is the fill function. nCount is the data I am trying to fill into ranges C10:N10 and P10:AA10 on a sheet called "MON PROD" based on the time ranges from the previous code. So far I have tried multiple things and can only get the value for the last time range into C10 on "MON PROD".

 Private Sub FillSheetPT()
   
   On Error GoTo ERROR_TRAP
   
   Dim ws As Worksheet
   Dim nRow As Integer     'Column
   Dim nCol As Integer     'Row
   Dim sCSN As String      'CSN
   Dim sVIN As String      'VIN
   Dim sStation As String
   Dim sProcessGroup As String
   Dim sCSNold As String
   Dim sCSNnew As String
   Dim sCSNInput As String
   Dim sWIP As String
   Dim sUserID As String
   Dim nCount As Integer   'Unique CSN Count
   Dim nLoop As Integer
   Dim ShiftStart As Date
   Dim sDateEnd As String
   Dim sStartTime As String
   Dim nRowStart As Integer
   Dim nColStart As Integer
   Dim ProcStateUserID As String
   
   Dim bDateDisplayed As Boolean
   Set ws = ThisWorkbook.Worksheets(9)
   
   'Deactivate Automatic Worksheet Recalculation
   Application.Calculation = xlCalculationManual
   
   nRowStart = 2
   nColStart = 3
   
   'Display Status
   Me.Cells(3, 6) = "Initialize Sheet"
   ShiftStart = Worksheets("Run Report").Cells(4, 2)
   
   sProcessGroup = moRSWips!ProcGrpName
   
   'Get Distinct Column Count
   nCount = 0
   While Not moRSWips.EOF
'      sProcessGroup = moRSWips!ProcGrpName & ""
      If sCSN <> moRSWips!CSN Then
         sCSN = moRSWips!CSN
          
            If (moRSWips!ProcGrpName = "CradleBatchAssy") Or (moRSWips!ProcGrpName = "PowerTrainSTSV") Or (moRSWips!ProcGrpName = "StaBarAssyBatch") Then
               sCSN = moRSWips!VIN & ""
            End If
         
         'Increment Column Counter
         nCount = nCount + 1
      End If
      'Get Next Record
      moRSWips.MoveNext
   Wend
   If Not moRSWips.BOF Then
      moRSWips.MoveFirst
   End If
   
   'Clear Old Data
   ClearWorksheetPT
   'Clear First Column
   ws.Range("B2:C2") = ""
   ws.Range("A2:C2") = ""
   
   'Fill Data
   If nCount > 0 Then
   
      'Display Status
      Me.Cells(3, 6) = "Create Grid"
   
      'Display Status
      Me.Cells(3, 6) = "Fill Data"
      
      nRow = nRowStart - 1
      While Not moRSWips.EOF
         sCSN = moRSWips!CSN & ""
         
            If (moRSWips!ProcGrpName = "CradleBatchAssy") Or (moRSWips!ProcGrpName = "PowerTrainSTSV") Or (moRSWips!ProcGrpName = "StaBarAssyBatch") Then
               sCSN = moRSWips!VIN & ""
            End If
        
         sUserID = moRSWips!ProcStateUserID & ""
         sWIP = moRSWips!ContID & ""
         sStartTime = moRSWips!ProcStateStart & ""
         sCSNnew = sCSN & sWIP & sStation & sUserID
         While Not (sCSNold = sCSNnew)
            If Not (sCSNold = sCSNnew) Then
               'First Record with NEW CSN
               
               'Increment Row Counter
               nRow = nRow + 1
               nCol = nCol + 1
               
               'Display CSN
               ws.Cells(nRow, 1) = sCSN
               ws.Cells(nRow, 2) = sStartTime
               'ws.Cells(nRow, 3) = nCount
               Worksheets("MON PROD").Cells(10, 3) = nCount
               
                   If Not bDateDisplayed Then
                  'Display First CSN ProcStateEnd
                  ws.Cells(6, 2) = sDateEnd
                  bDateDisplayed = True
               End If
            
            End If
            
            'Make OLD Copies
            sCSNold = sCSN & sVIN & sWIP & sStation & sUserID
         
         Wend
         
         'Get Next Record
         moRSWips.MoveNext
      Wend
      
   'Display Status
   Me.Cells(3, 6) = "Format Data"
        
   'ReActivate Automatic Worksheet Recalculation
   Application.Calculation = xlCalculationAutomatic
      
Format_Cells:
   
      'Display Status
      Me.Cells(3, 6) = "Sheet Filled"
      Me.Cells(4, 6) = Now
   End If
EXIT_SUB:
   Exit Sub
ERROR_TRAP:
   Select Case Err.Number
   Case 0
   Case Else
      DisplayError msModName & "FillSheetPT", Err.Number, True, Err.Description
      Err.Clear
      Resume EXIT_SUB
   End Select
   
End Sub

 

[ChrisM]

Wow, that's some complicated code you have there! Couple things:

I don't see how your GetData function is running when it starts like this-

For FirstCol = 1 To LastCol
LastCol = Worksheets("Settings").Cells(19, Columns.Count).End(xlToLeft).Column

You assign a value to LastCol AFTER you've used it in the For-Next. Shouldn't that go before the loop? Otherwise you are looping from 1 to zero.

I'm not able to invest enough time to completely go through your Fill function, but I am wondering...why are you having to loop through the recordset twice? And why are you testing for various column values? You should set the exact columns and filters you want in the sql statement, then I don't see why you wouldn't just output the entire recordset to the destination cell using CopyFromRecordset.

What you're doing just seems overly complicated, but maybe you need to do it that way?

 

[MikeWaller]

Wow, that's some complicated code you have there! Couple things:

I don't see how your GetData function is running when it starts like this-

For FirstCol = 1 To LastCol
LastCol = Worksheets("Settings").Cells(19, Columns.Count).End(xlToLeft).Column

You assign a value to LastCol AFTER you've used it in the For-Next. Shouldn't that go before the loop? Otherwise you are looping from 1 to zero.

I'm not able to invest enough time to completely go through your Fill function, but I am wondering...why are you having to loop through the recordset twice? And why are you testing for various column values? You should set the exact columns and filters you want in the sql statement, then I don't see why you wouldn't just output the entire recordset to the destination cell using CopyFromRecordset.

What you're doing just seems overly complicated, but maybe you need to do it that way?

Thanks for the quick reply! It probably is overly complicated. I'm not too experienced in SQL - Excel relationships. I'm trying to set up an automated production report to where they just have to put in the date and it fills the number of units produced per hour for that day. The problem is that If I define LastCol before the loop, I get a type mismatch error and I can't find out where the error is coming from. That probably comes from my inexperience. I just figured I had it in the wrong spot. I guess my first step should be to learn more about the SQL query for dates/times and see if I can just do it from there. I'll look it into it and post back once I figure it out. Thanks again, Chris!

 

[ChrisM]

It doesn't matter where you place a line in VBA, it will calculate the same way every time (unless it contains a variable that is changed by preceding lines of code). So if you were getting an error before and you aren't now, you actually fixed it by some other method then.

Your sql query looks fine. You select some fields, limit it by date/time and a couple other filters. Perfectly normal (though you can write it more quickly using the NOT IN operator, as in: ProcDefName NOT IN ("SMRSV", "SMSSV", "etc")

Where I'm confused is why you need to loop and get what you call the "distinct column" count. What's going on there?

Your recordset is returned as a table of rows and columns. Why don't you just output that to the worksheet at the specified cell? I'm sure the logic is in there if I stared at your code long enough, but just explain it in english for me.

 

[mesosoup]

It doesn't matter where you place a line in VBA, it will calculate the same way every time (unless it contains a variable that is changed by preceding lines of code). So if you were getting an error before and you aren't now, you actually fixed it by some other method then.

Your sql query looks fine. You select some fields, limit it by date/time and a couple other filters. Perfectly normal (though you can write it more quickly using the NOT IN operator, as in: ProcDefName NOT IN ("SMRSV", "SMSSV", "etc")

Where I'm confused is why you need to loop and get what you call the "distinct column" count. What's going on there?

Your recordset is returned as a table of rows and columns. Why don't you just output that to the worksheet at the specified cell? I'm sure the logic is in there if I stared at your code long enough, but just explain it in english for me.

One thing i would like to add is you should avoid inline SQL. If you can write your SQL in stored procedures and call those via your excel vba. You can lock down the stored proc and avoid security head aches such as SQL injection.

 

[MikeWaller]

The distinct column count is how I am getting my total amount produced for the hour. This is the reason for the looping. Again, this is from my inexperience. I am sure there is a way to get the total from SQL, but I'm just not sure how to do it. Thanks for the suggestion, meso. Once I can get the query locked down with Chris's advice I'm sure this will make everything a lot easier. I think a lot of the problem is that instead of coding from scratch, I am editing a workbook from another project.

 

[MikeWaller]

Ok, so I figured out that I need to use the COUNT function to get the total, but I can not figure out the syntax. Here is what I have now:

sSQL = "SELECT CAST(CSN as varchar(25)) as CSN, CAST(VIN as varchar(25)) as VIN, ContID, ProcDefName, ProcStateStart, ProcStateEnd, ProcGrpName, ProcStateUserID, ProcStateTranType, ProcStateComp"
   sSQL = " COUNT(CSN) as CSNTotal"
   sSQL = sSQL & " FROM vblReportIMProcStateWithContAttrib"
  
'   Start CSN is on Settings Worksheet
   dtStartDate = Worksheets("Settings").Cells(19, LastCol - 1)
'   End CSN is  on Settings Worksheet
   dtEndDate = Worksheets("Settings").Cells(19, LastCol)
   
   
   If dtStartDate <> #12:00:00 AM# Then
      If dtEndDate <> #12:00:00 AM# Then 'apply both
         sSQL = sSQL & " WHERE ProcStateEnd >= '" & dtStartDate & "' AND ProcStateEnd <= '" & dtEndDate & "'"
      Else 'apply just start
         sSQL = sSQL & " WHERE ProcStateEnd = '" & dtStartDate & "'"
      End If
    
       sSQL = sSQL & " AND ProcGrpName in ('PowerTrain')"
      sSQL = sSQL & " AND ProcStateComp = 'YES'"
      sSQL = sSQL & " AND ProcStateTranType = 'Completed'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMRSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV2'"
      sSQL = sSQL & " AND ProcDefName <> 'RSSSV'"
      sSQL = sSQL & " AND ProcDefName = 'PT002L'"
      sSQL = sSQL & "GROUP BY CSN, VIN, ContID, ProcDefName, ProcStateStart, ProcStateEnd, ProcGrpName, ProcStateUserID, ProcStateTranType, ProcStateComp"

I just need the total amount of CSNs for the time range. I feel like I'm making this harder than it should be again.

 

[MikeWaller]

Alright! Thanks to your help I have everything down to one sub at least. Now I just need to figure out how to loop it for each hour range, and then loop that for each day(MON PROD,TUE PROD, WED PROD etc.). You guys definitely put me in the right direction though. Thank you so much! It should have been so simple and I never would have looked for another way until you told me to do so. Here is what I ended up with so far.

Private Function GetDataPT() As Boolean
   
   On Error GoTo ERROR_TRAP
   
   Dim sStartCSN     As String
   Dim sEndCSN       As String
   Dim dtStartDate   As String
   Dim dtEndDate     As String
   Dim sSQL          As String
   Dim ProcStateUserID As String
   
   Dim ws As Worksheet
   
   Set ws = ThisWorkbook.Worksheets(8)
   
   'Display Status
   ws.Cells(3, 6) = "Getting Data"
   
   GetDataPT = False

   'Create Query
   sSQL = "SELECT CAST(CSN as varchar(25)) as CSN, CAST(VIN as varchar(25)) as VIN, ContID, ProcDefName, ProcStateStart, ProcStateEnd, ProcGrpName, ProcStateUserID, ProcStateTranType, ProcStateComp"
   sSQL = sSQL & " FROM vblReportIMProcStateWithContAttrib"
  
'   Start CSN
   dtStartDate = Worksheets("Settings").Cells(19, 1)
'   End CSN
   dtEndDate = Worksheets("Settings").Cells(19, 2)
   
   
   If dtStartDate <> #12:00:00 AM# Then
      If dtEndDate <> #12:00:00 AM# Then 'apply both
         sSQL = sSQL & " WHERE ProcStateEnd >= '" & dtStartDate & "' AND ProcStateEnd <= '" & dtEndDate & "'"
      Else 'apply just start
         sSQL = sSQL & " WHERE ProcStateEnd = '" & dtStartDate & "'"
      End If
    
       sSQL = sSQL & " AND ProcGrpName in ('PowerTrain')"
      sSQL = sSQL & " AND ProcStateComp = 'YES'"
      sSQL = sSQL & " AND ProcStateTranType = 'Completed'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMRSV'"
      sSQL = sSQL & " AND ProcDefName <> 'SMSSV2'"
      sSQL = sSQL & " AND ProcDefName <> 'RSSSV'"
      sSQL = sSQL & " AND ProcDefName = 'PT002L'"
      sSQL = sSQL & " ORDER BY CSN"
      
      
      Set moRSWips = New Recordset
      moRSWips.Open sSQL, moDBConn
      Set moRSWips.ActiveConnection = Nothing
      GetDataPT = True
   Else
      MsgBox "No Start Date Entered", vbInformation, "GetDataPT"
   End If
  
  Worksheets("MON PROD").Cells(10, 3) = moRSWips.RecordCount
Exit_Function:
   Exit Function
ERROR_TRAP:
   Select Case Err.Number
   Case 0
   Case Else
      DisplayError msModName & "GetDataRM", Err.Number, True, Err.Description
      Err.Clear
      Resume Exit_Function
   End Select
   
End Function

It's still not pretty, but it's much better than before.

 

[ChrisM]

What's the deal with looping by day and hour? Do you have one worksheet per day, etc? So you want to run the same query over and over with slightly different date/time parameters? Please explain what you are trying to do.

Off the top of my head, I would expect you to run only one query and output it to a data sheet. That query would have a date, day of week, and hour column. Then you could use those values to do vlookup type formulas and view the data in whatever format you want. But again I'm not sure what it is you are trying to do.