none
How to find out Excel Sheet External Data Connections using Microsoft.Office.Interop.Excel in C#? RRS feed

  • Question

  • I'm trying to automate the process of refreshing data from external source like SQL Server in Excel sheet by using Office.Interop.Excel. I know how to refresh the excel sheet by using RefreshAll() method. But i would like to know is there anyway to find out excel sheets data Connection's last refresh date so that i can confirm the refresh was successfully done by comparing the last refresh date of data connection before and after the refresh. 
    • Moved by Kristin Xie Friday, September 5, 2014 7:51 AM Excel develop related
    Thursday, September 4, 2014 4:03 AM

Answers

  • Hi Mukkala,

    >> so that i can confirm the refresh was successfully done

    To confirm whether a refresh operation is succeed. You could use QueryTable.AfterRefresh Event (Excel).

    Here is an example for your reference:

    Dim WithEvents DataList As QueryTable
    
    Private Sub Workbook_Open()
        Set DataList = Sheet1.ListObjects(1).QueryTable
    End Sub
    
    
    
    Private Sub DataList_AfterRefresh(ByVal Success As Boolean)
     If Success Then
        MsgBox "Query completed successfully"
     Else
        MsgBox "Query failed or was cancelled"
     End If
    End Sub
    

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 11, 2014 2:24 AM
    Moderator

All replies

  • Hi,

    can you check this forum, there was a solution which  might be helpful for you as well.

    Hope this helps ! Cheers !


    Ram.

    Thursday, September 4, 2014 5:13 AM
  • Hi @Ramachandran thanks for the reply. But you have suggested VBA approach but here I need to code it in C# so that's why i'm asking whetehr there are any objects in C# to accomplish this.
    Thursday, September 4, 2014 6:01 AM
  • Hi Mukkala,

    If users refresh the data manually, Excel doesn't provide such property to find the last refresh data.

    What I thought is that you can log the refresh data into a local file such as notepad, Access or others if you refresh the data by code.

    After that, you can compare the last refresh date of data connection before and after the refresh.

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 8, 2014 2:34 AM
    Moderator
  • Hi Mukkala,

    >> so that i can confirm the refresh was successfully done

    To confirm whether a refresh operation is succeed. You could use QueryTable.AfterRefresh Event (Excel).

    Here is an example for your reference:

    Dim WithEvents DataList As QueryTable
    
    Private Sub Workbook_Open()
        Set DataList = Sheet1.ListObjects(1).QueryTable
    End Sub
    
    
    
    Private Sub DataList_AfterRefresh(ByVal Success As Boolean)
     If Success Then
        MsgBox "Query completed successfully"
     Else
        MsgBox "Query failed or was cancelled"
     End If
    End Sub
    

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 11, 2014 2:24 AM
    Moderator