none
How to Bind Project Server Project names & ProjectUIDs to Excel Drop down control RRS feed

  • Question

  • Hi,

    Would like to develop an excel based EPM report to show case all the specific project related information like (Schedule,Late tasks,Milestones,Deliverables,Upcoming deliverables,EVM,Issues,Risks.. etc..)

    The above connections shold be updated dynamically upon selecting a project name from the drop down control.

    Does any one know how to bind Project name & Project UID to the Excel combobox control usign VB macros?

    Display name should be visible where as ProjectUID should be a value it should be hidden from the Combo box dropdown.



    • Edited by Rajkumar Allepu Tuesday, November 21, 2017 5:03 AM Spelling correction
    Tuesday, November 21, 2017 4:37 AM

Answers

  • Here is the code, have written vb code to bind the Project name & Project UId to the excel Combobox

    Sub BindProjectNamesToDropDown()

    Dim ADOCn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim gstrConnString As String
    Dim i As Integer
    Dim sheetName As String

    sheetName = ThisWorkbook.Sheets(1).Name

    gstrConnString = "Provider=SQLOLEDB;Data Source=server name;Initial Catalog=db name;User ID=; Password=;"

    Set ADOCn = New ADODB.Connection
    ADOCn.ConnectionString = gstrConnString
    ADOCn.Open gstrConnString

    Dim sSQL As String
     sSQL = "SELECT [projectName],[ProjectUID] FROM <table name> order by projectName"
    Set adoRS = New ADODB.Recordset
    adoRS.Open sSQL, ADOCn

    ThisWorkbook.Worksheets(sheetName).ComboBox1.Clear

    adoRS.MoveFirst

    i = 0

    With ThisWorkbook.Worksheets(sheetName).ComboBox1
        .Clear
    Do
        .AddItem
             .List(i, 0) = adoRS![projectName]
             .List(i, 1) = adoRS![ProjectUID]
             i = i + 1
             adoRS.MoveNext
         Loop Until adoRS.EOF
    End With

    ThisWorkbook.Worksheets(sheetName).ComboBox1.List(0) = "-Select a Project-"
    ThisWorkbook.Worksheets(sheetName).ComboBox1.ListIndex = 0

    adoRS.Close
    Set adoRS = Nothing
    ADOCn.Cl
    ose
    Set ADOCn = Nothing

    End Sub

    • Proposed as answer by Agasthya.S.P Tuesday, November 21, 2017 4:58 AM
    • Marked as answer by Rajkumar Allepu Thursday, December 7, 2017 8:55 AM
    Tuesday, November 21, 2017 4:43 AM
  • Here is the code to update the Dataset connection & Refresh all the Pivot tables/charts upon selecting the project name from the drop down.

    Sub UpdateEVMData()

    Dim ProjectID As String
    Dim ProjectUID As String
    Dim sqlQuery As String
    Dim sheetName As String

    sheetName = ThisWorkbook.Sheets(1).Name


    If (ThisWorkbook.Worksheets(sheetName).ComboBox1.Text = "-Select a Project-") Then
        MsgBox ("Please select a valid Project")

    Else
        ProjectUID = ThisWorkbook.Worksheets(sheetName).ComboBox1.Value
        'Remove the curly braces ({}) from the ProjectUID
        ProjectID = Mid(ProjectUID, 2, Len(ProjectUID) - 2)
        
        sqlQuery = "<SQL Query>'" & ProjectID & "'"
       
        
        
        'Debug.Print ProjectID
        
        '===========Update the connection ====================
        
        With ActiveWorkbook.Connections("<Connection Name>").OLEDBConnection
        .CommandText = sqlQuery 
        .Refresh
        End With
        
     
        
        'Refresh all the connections
        ActiveWorkbook.RefreshAll
        
        'Refresh all the Pivot tables across the workbook
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
                pt.RefreshTable
                pt.Update
            Next pt
        Next ws
    End If

    End Sub

    • Proposed as answer by Agasthya.S.P Tuesday, November 21, 2017 4:58 AM
    • Marked as answer by Rajkumar Allepu Thursday, December 7, 2017 8:55 AM
    Tuesday, November 21, 2017 4:51 AM

All replies

  • Here is the code, have written vb code to bind the Project name & Project UId to the excel Combobox

    Sub BindProjectNamesToDropDown()

    Dim ADOCn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim gstrConnString As String
    Dim i As Integer
    Dim sheetName As String

    sheetName = ThisWorkbook.Sheets(1).Name

    gstrConnString = "Provider=SQLOLEDB;Data Source=server name;Initial Catalog=db name;User ID=; Password=;"

    Set ADOCn = New ADODB.Connection
    ADOCn.ConnectionString = gstrConnString
    ADOCn.Open gstrConnString

    Dim sSQL As String
     sSQL = "SELECT [projectName],[ProjectUID] FROM <table name> order by projectName"
    Set adoRS = New ADODB.Recordset
    adoRS.Open sSQL, ADOCn

    ThisWorkbook.Worksheets(sheetName).ComboBox1.Clear

    adoRS.MoveFirst

    i = 0

    With ThisWorkbook.Worksheets(sheetName).ComboBox1
        .Clear
    Do
        .AddItem
             .List(i, 0) = adoRS![projectName]
             .List(i, 1) = adoRS![ProjectUID]
             i = i + 1
             adoRS.MoveNext
         Loop Until adoRS.EOF
    End With

    ThisWorkbook.Worksheets(sheetName).ComboBox1.List(0) = "-Select a Project-"
    ThisWorkbook.Worksheets(sheetName).ComboBox1.ListIndex = 0

    adoRS.Close
    Set adoRS = Nothing
    ADOCn.Cl
    ose
    Set ADOCn = Nothing

    End Sub

    • Proposed as answer by Agasthya.S.P Tuesday, November 21, 2017 4:58 AM
    • Marked as answer by Rajkumar Allepu Thursday, December 7, 2017 8:55 AM
    Tuesday, November 21, 2017 4:43 AM
  • Here is the code to update the Dataset connection & Refresh all the Pivot tables/charts upon selecting the project name from the drop down.

    Sub UpdateEVMData()

    Dim ProjectID As String
    Dim ProjectUID As String
    Dim sqlQuery As String
    Dim sheetName As String

    sheetName = ThisWorkbook.Sheets(1).Name


    If (ThisWorkbook.Worksheets(sheetName).ComboBox1.Text = "-Select a Project-") Then
        MsgBox ("Please select a valid Project")

    Else
        ProjectUID = ThisWorkbook.Worksheets(sheetName).ComboBox1.Value
        'Remove the curly braces ({}) from the ProjectUID
        ProjectID = Mid(ProjectUID, 2, Len(ProjectUID) - 2)
        
        sqlQuery = "<SQL Query>'" & ProjectID & "'"
       
        
        
        'Debug.Print ProjectID
        
        '===========Update the connection ====================
        
        With ActiveWorkbook.Connections("<Connection Name>").OLEDBConnection
        .CommandText = sqlQuery 
        .Refresh
        End With
        
     
        
        'Refresh all the connections
        ActiveWorkbook.RefreshAll
        
        'Refresh all the Pivot tables across the workbook
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
                pt.RefreshTable
                pt.Update
            Next pt
        Next ws
    End If

    End Sub

    • Proposed as answer by Agasthya.S.P Tuesday, November 21, 2017 4:58 AM
    • Marked as answer by Rajkumar Allepu Thursday, December 7, 2017 8:55 AM
    Tuesday, November 21, 2017 4:51 AM
  • Thanks for the post. This works like a magic spell :)

    Regards

    Agasthya


    agasthya

    Friday, December 8, 2017 4:28 AM