VBA SQL Reading data from a column until empty cell.

[dcg38524]

Hello,

Need your help on a VBA code modification.

We have an existing excel SQL query that works, but there are group names hard-coded which I wanted to revisit. As you know this works great if nothing changes but can be very awkward and inconvenient when changes are made frequently.

Just was a little curious on how hard it would be to change the code, I searched the web and couldn't find any clear solutions, so I am reaching out for help.

If all possible I would like to change the current code to read these group names from the existing workbook

• Tab called "valid names"
• Starting from column "A2"
• Until it reaches an empty cell.

Our code currently looks like below: 'Please Don't laugh,'
SELECT "ASSIGNED_TO_GROUP_, ASSIGNED_TO_INDIVIDUAL_, TICKET_ID_ , etc...."
FROM WAREHOUSE_MANAGEMENT
WHERE ASSIGNED_TO_INDIVIDUAL_ <> 'John'
AND ASSIGNED_TO_GROUP_ IN ( 'GROUP1' , 'GROUP2', 'GROUP3', 'GROUP4', 'GROUP5')
ORDER BY
TICKET_ID_

 

[Jerry Sullivan]

Hi Don,

One way to manage the reference to the data source range more dynamically without needing to edit your VBA code or query is to use a Named Range with WorkBook scope to define your table(s).

For SQL queries that reference External Workbooks, the Named Range should reference a Static Range Address (not an Excel Table, or Dynamic Named Range).

Since it would be a pain to continually update the Static Range reference manually, you can use a VBA macro to automatically update the Named Range each time you Save the Workbook.

Here's one example that assumes the dataset is in a contiguous range beginning at Cell A2.

Paste this into the ThisWorkbook Code Module....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
   Cancel As Boolean)
'--updates static named range(s) for use as tables in SQL queries
 With ThisWorkbook
   .Names.Add Name:="tblWarehouseManagement", _
      RefersTo:=Sheets("valid names").Range("A2").CurrentRegion
 End With
End Sub

 

[dcg38524]

Thanks Jerry,

Question, with your suggestion would I need write access to the DB? If so, our group only has read access to the Oracle DB.

I wasn't sure how to incorporate a single valuable to substitute "GROUP_NAME" into the proposed code below i.e. SQL statement " AND ASSIGNED_TO_GROUP_ IN ( 'GROUP1' , 'GROUP2', 'GROUP3', 'GROUP4', 'GROUP5')"

Dim n As Integer
Dim rgStartCell As Range
Dim Query2 As String
Dim query As String

Set rgStartCell:=Sheets("valid names").Range("A2").

SELECT "ASSIGNED_TO_GROUP_, ASSIGNED_TO_INDIVIDUAL_, TICKET_ID_ , etc...."
FROM WAREHOUSE_MANAGEMENT
WHERE ASSIGNED_TO_INDIVIDUAL_ <> 'John'

For n = 2 To rgStartCell.CurrentRegion.Rows.Count
AND [I][B]ASSIGNED_TO_GROUP_[/B][/I]  IN ('" & rgStartCell(n) & "')
    Query2 = query & "'" & rgStartCell(n) & "'"
Next n

 

[Norie]

How exactly are you running this query?

Do you at any point have the SQL statement in a string?

By the way, if you want to concatenate all the values in a column you can use this.

arrGroups = Range("A2", Range("A" & Rows.Count).End(xlUp))

arrGroups = Application.Transpose(arrGroups)

strGroups = "'" & Join(arrGroups, "','") & "'"

If column A contained Group1, Group2, Group3, ..., Group10 this code would produce this string,

'Group1','Group2','Group3','Group4','Group5','Group6','Group7','Group8','Group9','Group10'

 

[dcg38524]

Hi Norie,

We query a read only Oracle DB for key content information in a record that has several fields we used to gauge performance improvement.

The code you presented is exactly what I am looking for,
Question:
how would I include excel workbook tab "valid names"?
How would the "AND ASSIGNED_TO_GROUP_ IN" SQL statement look?

arrGroups = Sheets("[B]valid names[/B]", "A2", Range("A" & Rows.Count).End(xlUp)))  

arrGroups = Application.Transpose(arrGroups)  

strGroups = "'" & Join(arrGroups, "','") & "'"

SELECT "ASSIGNED_TO_GROUP_, ASSIGNED_TO_INDIVIDUAL_, TICKET_ID_ , etc...." 
FROM WAREHOUSE_MANAGEMENT WHERE ASSIGNED_TO_INDIVIDUAL_ <> 'John'
AND [I][B]ASSIGNED_TO_GROUP_[/B][/I]  IN ('" &arrGroups & "')
order by
TICKET_ID_

 

[Norie]

This is how you can include the sheet.

With Sheets("valid names")
    arrGroups = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

For the second question, can you post the actual code you are using?

 

[dcg38524]

Thanks Norie,

Code is listed below: Please don't laugh on badly it's structured

Sub Main()
    Dim sStartDate As String
    Dim sConnect As String
    Dim sEndDate As String
    Dim Ctr As Integer
    Dim recCat As New ADODB.Recordset
    Dim wksResults As Worksheet
    Dim fldCount As Integer
    Dim iCol As Integer
    Dim sSQL As String
    Dim arrGroups As String
    Dim strGroups As String
    
    With Sheets("valid RAs")
    arrGroups = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
    
    arrGroups = Application.Transpose(arrGroups)
    strGroups = "'" & Join(arrGroups, "','") & "'"
            
    'Query for the catalogue to be queried
    Unload InputForm
    Load InputForm
    'Set default values
    InputForm.txtStartDate.Value = Format(Now(), "DD-mmm-YYYY") & " 00:00:00"
    InputForm.txtEndDate.Value = Format(Now(), "DD-mmm-YYYY") & " 23:59:59"
    
    Do
        'note: userform activate event displays the drop down list
        'and sets the focus to the combobox
        InputForm.Show
        
        sStartDate = InputForm.txtStartDate.Value
        sEndDate = InputForm.txtEndDate.Value
        
        'check value of bOK that was set by the buttons on the form
        If Not bOK Then Exit Sub
        
        'if an item is selected, exit the loop
        If (Not sStartDate = "") And (Not sEndDate = "") Then Exit Do
        'if no item selected, display a message
        MsgBox "Please ensure both start and end date are selected."

    Loop
    
    'Query for the submitter
    sStartDate = InputForm.txtStartDate.Value
    sEndDate = InputForm.txtEndDate.Value
    Unload InputForm
   
    'Connect to Oracle
    sConnect = "Provider=MSDAORA.1;Password=" & DB_PW & ";User ID=" & DB_USERID & ";" & _
           "Data Source=" & DB_NAME & ";Persist Security Info=True"
    connCham.Open sConnect

sSQL = " SELECT "
sSQL = sSQL & " TICKET_ID_, PROBLEM_ID, SUBMITTED_BY, ASSIGNED_TO_GROUP_, ASSIGNED_TO_INDIVIDUAL_, TEF, "
sSQL = sSQL & "       DECODE (priority, "
sSQL = sSQL & "               0, 'Low', "
sSQL = sSQL & "               1, 'Medium', "
sSQL = sSQL & "               2, 'High', "
sSQL = sSQL & "               3, 'Urgent', "
sSQL = sSQL & "               4, 'Critical' "
sSQL = sSQL & "               ) ""Priority"", "
sSQL = sSQL & "       DECODE (case_type, "
sSQL = sSQL & "               0, 'Incident', "
sSQL = sSQL & "               1, 'Misc', "
sSQL = sSQL & "               2, 'Request' "
sSQL = sSQL & "               ) ""Case Type"", "
sSQL = sSQL & " SUB_CODE, CATEGORY, SOLUTION_TEXT,"
sSQL = sSQL & "       TO_DATE (TO_CHAR (  TO_DATE ('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') "
sSQL = sSQL & "                         + ((arrival_time) / (60 * 60 * 24)), "
sSQL = sSQL & "                         'MM/DD/YYYY HH24:MI:SS' "
sSQL = sSQL & "                        ), "
sSQL = sSQL & "                'MM/DD/YYYY HH24:MI:SS' "
sSQL = sSQL & "               ) ""Incident Start Time"", "
sSQL = sSQL & "       TO_DATE (TO_CHAR (  TO_DATE ('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') "
sSQL = sSQL & "                         + ((resolved_time) / (60 * 60 * 24)), "
sSQL = sSQL & "                         'MM/DD/YYYY HH24:MI:SS' "
sSQL = sSQL & "                        ), "
sSQL = sSQL & "                'MM/DD/YYYY HH24:MI:SS' "
sSQL = sSQL & "               ) ""Incident End Time"", "
sSQL = sSQL & " root_cause, hours_to_resolve, SUMMARY, KEYWORD"
sSQL = sSQL & " FROM INCIDENT_MANAGEMENT "
sSQL = sSQL & " WHERE"
sSQL = sSQL & "(ARRIVAL_TIME between (86400 * ( to_date('" & sStartDate & "', 'dd-mon-yyyy hh24:mi:ss') - to_date('01-jan-1970', 'dd-mon-yyyy')))  "
sSQL = sSQL & "                AND (86400 * ( to_date('" & sEndDate & "', 'dd-mon-yyyy hh24:mi:ss') - to_date('01-jan-1970', 'dd-mon-yyyy')))) "
sSQL = sSQL & " and ASSIGNED_TO_GROUP_ IN ('GROUP1' , 'GROUP2' , 'GROUP3' , 'GROUP4' , 'GROUP5' , 'GROUP6' ....... 'GROUP25' ) "
sSQL = sSQL & " order by "
sSQL = sSQL & "      TICKET_ID_ "
sSQL = sSQL & "     "

    recCat.CursorLocation = adUseClient
    recCat.Open sSQL, connCham, adOpenForwardOnly, adLockReadOnly

            
    ' Copy the recordset to the worksheet, starting in cell A4
    Sheets("Raw-Data").Cells(2, 1).CopyFromRecordset recCat
    recCat.Close
    Set recCat = Nothing
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A2").Select

 connCham.Close
End Sub

 

[Jerry Sullivan]

Thanks Jerry,

Question, with your suggestion would I need write access to the DB? If so, our group only has read access to the Oracle DB.

I wasn't sure how to incorporate a single valuable to substitute "GROUP_NAME" into the proposed code below i.e. SQL statement " AND ASSIGNED_TO_GROUP_ IN ( 'GROUP1' , 'GROUP2', 'GROUP3', 'GROUP4', 'GROUP5')"

Don, I completely misread your original question. I thought your actual database table was in the worksheet range beginning at A2.
From your dialog with Norie, it's clear the worksheet just holds the groups' field names.

In response to your last question, you could incorporate Norie's suggestions into your code like this...

Sub Test()
 
 Dim sSQL As String
 Dim sGroups As String
 Dim arrGroups As Variant
 
 With Sheets("valid RAs")
   arrGroups = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
 End With

 arrGroups = Application.Transpose(arrGroups)
 sGroups = "'" & Join(arrGroups, "','") & "'"

 '........replace this line in your existing query
 sSQL = sSQL & " and ASSIGNED_TO_GROUP_ IN (" & sGroups & ") "
 '........

'--display result
 MsgBox sSQL

End Sub

 

[dcg38524]

Jerry, Norie,

Your suggestions worked great, you both are amazing .

I can't thank you enough for being so patience with someone like myself with little SQL knowledge.

One last question, when I remove all but one group the code errors out with:

Run-time error '13'
Type mismatch

I any idea on why this is occurring?

Now the code works great for groups greater then one or any entry after "A2".

Thanks again,

Warm regards,
Don

 

[dcg38524]

I think I know why the query is errors out if only one entry exist in the "Valid RAs" tab.

sSQL = sSQL & " and ASSIGNED_TO_GROUP_ IN (" & sGroups & ") "

I think The IN statement used above is looking for more than one occurrence?
Not sure this is current but for only one entry in the "Valid RAs" tab the code would need to be:

sSQL = sSQL & " and ASSIGNED_TO_GROUP_ = (" & sGroups & ") "

Remember my SQL knowledge is very very limited, so my assumptions made above could be totally out of bounds and not related to the error.

-Don