• Home
 · 
• Miscellaneous
 · 
• Queries an SQL database using Excel and displays it in a spreadsheet

Queries an SQL database using Excel and displays it in a spreadsheet

Queries an SQL database using Excel and displays it in a spreadsheet

Rate Queries an SQL database using Excel and displays it in a spreadsheet

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    Dim oConn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim iField As Long
    Dim strSql As String
    Dim strValues As String
    Dim strClientCriteria As String
    Dim strAMCriteria As String
    
    Set oConn = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oConn.CursorLocation = adUseClient
    oConn.Open "Provider=SQLOLEDB;Data Source=stagdata2;Initial Catalog=SupportSite;User ID=sa;Password=whatver"
    
    'Retrieve additional criteria for the SQL for the CLIENT
    strClientCriteria = Worksheets("Graphs").Range("Client").Value
    If UCase(strClientCriteria) = "ALL" Then
        strClientCriteria = ""
    Else
        strClientCriteria = "AND a.client like '" & strClientCriteria & "' "
    End If
    
    'Retrieve additional criteria for the SQL for the ACCOUNT MANAGER
    strAMCriteria = Worksheets("Graphs").Range("Account_Manager").Value
    If UCase(strAMCriteria) = "ALL" Then
        strAMCriteria = ""
    Else
        strAMCriteria = "AND a.HistoryFullUserName like '" & strAMCriteria & "' "
    End If
    
    If UCase(strDataType) = "DATA" Then
        strSql = " SELECT a.projectname, c.projectstepname, b.changedate " _
                 & "FROM project a, Project_History b, projectsteps c " _
                 & "WHERE a.ProjectID = b.ProjectID " _
                 & "AND b.newvalue = c.projectstepid " _
                 & "AND b.Field = 'ProjectStatus' " _
                 & "AND c.projectstepname <> 'Canceled' " _
                 & "AND a.status <>'closed' " _
                 & strClientCriteria _
                 & strAMCriteria _
                 & "ORDER BY a.projectname, b.changedate "
    ElseIf UCase(strDataType) = "TOTAL_DOLLARS" Then
        strSql = "SELECT a.projectname, b.projectstepname, a.estdollar, a.actdollar " _
                & "FROM project a, projectsteps b " _
                & "WHERE a.projectstatus = b.projectstepid " _
                & "AND a.status <>'closed' " _
                & "AND a.type in ('Feature Request', 'Change Request', 'Maintenance Request', 'Implementation Request') " _
                & "AND a.projectstatus IS NOT NULL " _
                & "AND a.client NOT LIKE '%steton%' " _
                & strClientCriteria _
                & strAMCriteria _
                & "ORDER BY 1 "
    End If
    
     oRS.Open strSql, oConn
     
    'Load the Data into the spreadsheet
     If Not oRS.EOF Or oRS.BOF Then
         Range("A18").CopyFromRecordset oRS
     End If
     
     'Close everything
     oRS.Close
     Set oRS = Nothing
     oConn.Close
     Set oConn = Nothing

End Sub