none
Naming Your Connection RRS feed

  • Question

  • I am using C# to push an access query to Excel.  My syntax works great, but I would now like to name the connection so when a workbook has multiple connections I know which one I should select to modify which specified query.  For example, in Excel 2007 when I click the Data Tab and Click Connections - each query that is imported is just named Connection, Connection1, Connection2, Connection3 etc etc  Which piece of my code below names the connection or what should I add so that I am able to name the connection? --- If further code is needed please let me know.

    Excel.QueryTable qt = oWS.QueryTables.Add(@"ODBC;DSN=MS Access Database;DBQ=" + database + @";DefaultDir="
    + database + @";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;", oWS.get_Range("A2", "A100"),
    "SELECT * FROM " + queryName);
    xlRange.Font.Bold = true;
    qt.EnableRefresh = true;
    qt.Name = "Imported_Query";
    qt.FieldNames = false;
    qt.RowNumbers = false;
    qt.FillAdjacentFormulas = false;
    qt.PreserveFormatting = true;
    qt.RefreshOnFileOpen = false;
    qt.BackgroundQuery = true;
    qt.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
    qt.SavePassword = true;
    qt.SaveData = true;
    qt.AdjustColumnWidth = false;
    qt.RefreshPeriod = 0;
    qt.PreserveColumnInfo = true;
    qt.Refresh(true);

    Monday, December 8, 2014 2:29 PM

Answers

  • Hi IndigoMontoya,

    Based on the description, you want to name the connection which added when you importe data from external data.

    As faras I research, I didn't find a direct way to achieve the goal, recording macros also failed.

    As a workaround, we can save all the connection name to an array. After we added the new connection, then we can get the new connection via compare the connection name. At last, we can rename the name of specific connection via code below:

    With ActiveWorkbook.Connections("newConnection")
            .Name = "newConnectionName"
    End With

    Regards & Fei


    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.

    • Marked as answer by IndigoMontoya Tuesday, December 9, 2014 1:43 PM
    Tuesday, December 9, 2014 9:29 AM
    Moderator