none
C# VSTO 2012 - Importing data from external source RRS feed

  • Question

  • Hello everyone....

    Let me start by saying that I'm brand new to working VSTO, so if I'm missing something please be easy on me :)

    I'm trying to build an Excel 2010 AddIn in VSTO 2012 and am running into an issue. The add in will add a custom tab to the Excel Ribbon, where there are several buttons. When a user clicks a button, I want to execute a pre-defined query from a .odc file.

    Now, I can get the ribbon customized without any issues, and the .odc file is successfully connected to the workbook in the C# code (meaning I can see it in the Workbook Connections dialog of Excel). My problem is that I can't seem to find a way to import the data from that external data source. I've been searching online for a couple of days, and nothing seems to be working for me. Here's the code that I've currently got in the Ribbon button's Click event handler:

    private void query1_Click(object sender, RibbonControlEventArgs e)
    {
         //get the control context
        Excel.Window window = e.Control.Context;

        //get the active workbook and worksheet
        Excel.Workbook activeWorkbook = (Excel.Workbook)window.Application.ActiveWorkbook;
        Excel.Worksheet activeSheet = activeWorkbook.ActiveSheet;

        Excel.WorkbookConnection conn = activeWorkbook.Connections[1];
        conn.OLEDBConnection.BackgroundQuery = false;
        conn.Refresh();
        MessageBox.Show("DONE");
    }

    When this code executes, I get the MessageBox displayed, but no data is shown in the Worksheet. I know that the .odc file is fine, because I can manually go into the "existing connections" and the run the import, and it works fine.

    I've also tried adding a QueryTable, but had no luck getting that to refresh either. Additionally, by adding a QueryTable on top of the workbook connection, I get 2 connections displayed in the Workbook Connections dialog in Excel.

    I'm sure this must be something small that I'm missing...any thoughts?

    Wednesday, February 13, 2013 8:15 PM

Answers

  • Hello TrillJay,

    To get the Table formatting tab, you should use ListObjects. The following VBA code is generated by recording the Macro. You may convert this to C# and use it in your add-in

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
            "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Sample-Server;Use Procedure for Prepare=1;Au" _
            , _
            "to Translate=True;Packet Size=4096;Workstation ID=Sample-Server;Use Encryption for Data=False;Tag with column collation when pos" _
            , "sible=False;Initial Catalog=AdventureWorks2008R2"), Destination:=Range( _
            "$A$1")).QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array( _
            """AdventureWorks2008R2"".""HumanResources"".""Employee""")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = _
            "C:\Temp\Sample-Server AdventureWorks2008R2 Employee.odc"
            .ListObject.DisplayName = _
            "Table_Sample-Server_AdventureWorks2008R2_Employee"
            .Refresh BackgroundQuery:=False
        End With

    Let me know if this works for you.

    Thanks,

    Sreerenj G Nair

    Monday, February 25, 2013 9:47 PM

All replies

  • Hi TrilJay,

    Thanks for posting in the MSDN Forum.

    In order to populate the data to current worksheet you are able to use ListObject to approach it. Or fill every cell via code. And It's based on my experience that use ListObject is most easier way for it. Please take a look at : http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobjects.add(v=office.14).aspx . It might help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 14, 2013 8:11 AM
    Moderator
  • Tom - 

    Thanks for the information. Unfortunately, I wasn't able to get much help from the link you provided. 

    After some redesign, I've decided that I'm going to have several Document-Level projects, and each one will produce a different "report" with charts and graphs, etc. Then I'll have an Excel addin with some buttons, and each button will open the specified report workbook. I've gotten all of this working, mostly without issues. 

    Here's the document-level code that is pulling the data from the external data source:

    private void Sheet1_Startup(object sender, System.EventArgs e)
            {
                
                Excel.QueryTables allTables = this.QueryTables;
                Excel.QueryTable table = (Excel.QueryTable)allTables.Add(connectionStr, Range["A1"], "show_all_br");
                table.CommandType = Excel.XlCmdType.xlCmdTable;
                table.Refresh();
            }

    This works and the data is displayed in the worksheet. However, I don't get the table tools tab for Excel 2010, and the data has no styling/theme. Is that only available to a Listobject? How would I get this query working with a ListObject?

    Friday, February 15, 2013 7:37 PM
  • Hi Triluay,

    "show_all_br" is a stored procedure?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 18, 2013 6:41 AM
    Moderator
  • Yes. It's a stored TCL procedure that is executed when the OLE connection is made.
    Monday, February 18, 2013 1:59 PM
  • Hi TrilUay,

    I will involve some experts into your thread due to I can't reproduce your issue on my side via permission problem. Let's see whether they can help you out. There might be some time delay, appreciate for your patience.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 19, 2013 6:44 AM
    Moderator
  • Hello TrillJay,

    To get the Table formatting tab, you should use ListObjects. The following VBA code is generated by recording the Macro. You may convert this to C# and use it in your add-in

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
            "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Sample-Server;Use Procedure for Prepare=1;Au" _
            , _
            "to Translate=True;Packet Size=4096;Workstation ID=Sample-Server;Use Encryption for Data=False;Tag with column collation when pos" _
            , "sible=False;Initial Catalog=AdventureWorks2008R2"), Destination:=Range( _
            "$A$1")).QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array( _
            """AdventureWorks2008R2"".""HumanResources"".""Employee""")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = _
            "C:\Temp\Sample-Server AdventureWorks2008R2 Employee.odc"
            .ListObject.DisplayName = _
            "Table_Sample-Server_AdventureWorks2008R2_Employee"
            .Refresh BackgroundQuery:=False
        End With

    Let me know if this works for you.

    Thanks,

    Sreerenj G Nair

    Monday, February 25, 2013 9:47 PM
  • It's been a couple of weeks now and I'm not sure what I had tried before, but it was some variation of what you provided. After some trial and error, I've gotten the ListObject approach to work:

    Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
    Excel.Range rng = (Excel.Range)ws.Range["A1"];

    ws.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcExternal, connStr, System.Type.Missing, Excel.XlYesNoGuess.xlYes, rng, System.Type.Missing);
    Excel.QueryTable table = ws.ListObjects[1].QueryTable;
    table.CommandType = Excel.XlCmdType.xlCmdTable;
    table.CommandText = "show_all_br";
    table.SavePassword = true;
    table.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
    table.Refresh();

    Thanks for the help!

    Monday, March 4, 2013 2:49 PM