none
Excel 2010 - "always use connection file" doesn't appear to work RRS feed

  • Question

  • I have been struggling with this one for several days now, so I hope someone may have an idea.....

    I have a workbook with a number of query tables. It's going to be distributed to a large number of users, so I'm maintaining the connection information for each query in an .odc file so I can centrally manage password changes and changes to the SQL of the queries.

    I have selected the "always use connection file" option on each connection, so I was expecting that the connection string and command text in each of my connections would update if I edit them in the corresponding .odc file. But having changed the command text in one of the .odc files, the query is still using the locally-stored copy in the workbook rather than showing the updated SQL.

    If I go into Excel, open the connection definition and manually re-point the connection file to the .odc file then the query updates OK, but that isn't going to work as an approach when I've rolled this out to my users. I've tried adding some code to run through the query tables and reset the connection file each time the workbook's opened, but this doesn't cause the command text to update (unlike browsing manually....).

    Microsoft's documentation pretty clearly states "Always use connection file    Select this check box to ensure that the most up-to-date version of the connection file is always used whenever the data is displayed or refreshed." but that simply doesn't seem to be the case for me. Grateful for any suggestions as to why (or failing that, any suggestions as to how I can programmatically reproduce the effect of browsing to the .odc file).

    Thanks.

    Thursday, July 21, 2016 7:12 AM

Answers

  • Hi Deepak,

    Frankly, I just wanted a solution either way! I believe this is something that ought to be working via the UI, but as it clearly wasn't I was keen to see if there was a way to code a solution.

    I have now done this myself - happy to add the code if anyone might find it useful, but it wasn't anything very clever: I simply read the .odc file in line by line and looked for the ConnectionString and CommandText tags, then extracted their contents and updated the query table properties accordingly. It now works, but it doesn't seem to me like an approach anyone ought to have to take.

    I'm happy to close the thread on here, but I would like to leave the one on excel IT Pro open as it seems to me the UI isn't doing what it should.

    Thanks for your assistance - much appreciated.

    Ian

    Tuesday, July 26, 2016 10:27 AM

All replies

  • Hi Ian Lafford,

    you had mentioned that ,"Microsoft's documentation pretty clearly states "Always use connection file    Select this check box to ensure that the most up-to-date version of the connection file is always used whenever the data is displayed or refreshed." but that simply doesn't seem to be the case for me. "

    here you had mentioned that you are referring the Microsoft documentation but you did not mention which document you are referring.

    so here I assume that you are referring the documentation mention below.

    Connection properties

    in which following details are mentioned

    did you read the "Important" Note?

    From your description its looks like file is not available or accessible and that's why this issue occurs.

    so here I first recommend you to confirm it that file is always available there and accessible.

    if it is available and accessible then please confirm it by sharing screenshots or information related to it with us so that we can try to help you further.

    you had asked , "any suggestions as to how I can programmatically reproduce the effect of browsing to the .odc file)."

    please visit the link below will give you information regarding all the objects supported by Query Table.

    QueryTable Object (Excel)

    Regards

    Deepak


    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.

    Friday, July 22, 2016 1:11 AM
    Moderator
  • Hi Deepak,

    Thanks a lot for your response. That is indeed the documentation I was referring to, and yes, I did check that the .odc file was accessible - as I said, if I browse to it manually from the connection properties dialog, the command text updates correctly, so that would suggest to me that the file is both accessible and valid.

    I have attached the text of the .odc file below (minus the connection string) in case that helps.

    And thanks for the link to the query table documentation: that is the object I've been trying to manipulate, so I'm glad I was on the right lines. I have been setting the .SourceConnectionFile property of my query table to refer to my .odc file: this successfully updates the connection file info appearing in the connection properties dialog, but as I said, it doesn't then update the command text. I had thought of trying to set the command text property directly, but I was hoping there was a better method than having to write code to open the connection file and parse it to extract the command text.

    Thanks again for your help.

    Ian

    <html xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns="http://www.w3.org/TR/REC-html40">
    <head>
    <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
    <meta name=ProgId content=ODC.Table>
    <meta name=SourceType content=ODBC>
    <title>Fac data</title>
    <xml id=docprops><o:DocumentProperties
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns="http://www.w3.org/TR/REC-html40">
      <o:Name>Fac data</o:Name>
     </o:DocumentProperties>
    </xml><xml id=msodc><odc:OfficeDataConnection
      xmlns:odc="urn:schemas-microsoft-com:office:odc"
      xmlns="http://www.w3.org/TR/REC-html40">
      <odc:Connection odc:Type="ODBC">
       <odc:ConnectionString>#############################################################################REMOVED################################################################################</odc:ConnectionString>
       <odc:Parameter>
        <odc:Name>Parameter1</odc:Name>
        <odc:DataType>0</odc:DataType>
       </odc:Parameter>
       <odc:Parameter>
        <odc:Name>Parameter2</odc:Name>
        <odc:DataType>0</odc:DataType>
       </odc:Parameter>
       <odc:CommandText>SELECT&#13;&#10; IFP_FINANCEPLAN_ID&#13;&#10;,IFP_BANK_OPERATION_ID&#13;&#10;,FAC_STAT_CODE&#13;&#10;,DECODE (FAC_OPCYCLE_CODE,&#13;&#10; 'S' , 'Signed', 'Not Signed') VC_FAC_OPCYCLE_CODE&#13;&#10;,IFP_PRODUCTTYP_ID&#13;&#10;,RTRIM(PTY_PRODUCTTYP_NAME)&#13;&#10;,FIT_FINANCINGTYP_ID&#13;&#10;,RTRIM(FIT_FINANCINGTYP_NAME)&#13;&#10;,trim(CCY.CCY_SWIFT_CODE) &quot;CCY_SWIFT_CODE&quot;&#13;&#10;,NVL(FAC_SYNDICATED_PCT/100000000 * IFP_AMT,0) &quot;SYNAMT&quot;&#13;&#10;,NVL(IFP_AMT/1000000,0)&#13;&#10;,SRT_SOVRGN_RISK_NAME&#13;&#10;,LFC_TENURE_PERIODS&#13;&#10;,NVL(TRA_MARGIN_PCT, LFC_MARGIN_PCT)/100&#13;&#10;,decode(FAC_REVOLVING_LOAN_IND,'Y','Yes','No') FAC_REVOLVING_LOAN_IND&#13;&#10;,APP.APS_APPROACH_SUBTYPE_NAME&#13;&#10;,LGD.lgd_rating/100&#13;&#10;,PDR.PDR_RATING_PD&#13;&#10;,PTY_FAC_SUBTYP_CODE&#13;&#10;,IFP_PLANNED_FIN_DATE&#13;&#10;FROM&#13;&#10; INVESTMENT_FINANCE_PLANS    IFP&#13;&#10;,FACILITIES               FAC&#13;&#10;,LOAN_FACILITIES            LFC&#13;&#10;,SOVEREIGN_RISK_TYP_CODES SRT&#13;&#10;,RISK_CATEGORIES&#13;&#10;,CURRENCIES               CCY&#13;&#10;,FINANCING_TYPES          FIT&#13;&#10;,PRODUCT_TYPES            PTY&#13;&#10;,LOAN_TRANCHES            TRA&#13;&#10;,LOANS                    LOA&#13;&#10;,APPROACH_SUBTYPES        APP&#13;&#10;,LGD_RATINGS              LGD&#13;&#10;,PD_RATINGS               PDR&#13;&#10;WHERE&#13;&#10;IFP_BANK_OPERATION_ID = ?&#13;&#10;AND   FAC_FINANCEPLAN_ID         = IFP_FINANCEPLAN_ID&#13;&#10;AND   FAC_FINANCEPLAN_ID         = TRA_FINANCEPLAN_ID  (+)&#13;&#10;AND   FAC_FINANCEPLAN_ID         = LFC_FINANCEPLAN_ID&#13;&#10;AND   TRA_OPR_ID                 = LOA_OPR_ID (+)&#13;&#10;AND   FAC_RISKCATEGORY_ID        = RKC_RISKCATEGORY_ID (+)&#13;&#10;AND   LFC_SOVRGN_RISKTYP_ID      = SRT_SOVRGN_RISKTYP_ID (+)&#13;&#10;AND   IFP_INTRNL_EXTRNL_CODE     = 'I'&#13;&#10;AND   IFP_CCY_ID                 = CCY.CCY_ID&#13;&#10;AND   IFP_PRODUCTTYP_ID          = PTY_PRODUCTTYP_ID&#13;&#10;AND   PTY_FINANCINGTYP_ID        = FIT_FINANCINGTYP_ID&#13;&#10;AND   IFP_APPROACH_SUBTYPE_ID    = APS_APPROACH_SUBTYPE_ID&#13;&#10;AND   PTY_FAC_SUBTYP_CODE        = 'LO' &#45;- Loans&#13;&#10;AND   FAC.fac_lgd_rating         =  LGD.lgd_id (+)&#13;&#10;AND   FAC_FINANCEPLAN_ID         = PDR.PDR_FAC_ID (+)&#13;&#10;AND   FAC_STAT_CODE              in ('A','H')&#13;&#10;&#13;&#10;UNION&#13;&#10;&#13;&#10;SELECT&#13;&#10; IFP_FINANCEPLAN_ID&#13;&#10;,IFP_BANK_OPERATION_ID&#13;&#10;,FAC_STAT_CODE&#13;&#10;,DECODE (FAC_OPCYCLE_CODE,&#13;&#10; 'S'    , 'Signed', 'Not Signed') VC_FAC_OPCYCLE_CODE&#13;&#10;,IFP_PRODUCTTYP_ID&#13;&#10;,RTRIM(PTY_PRODUCTTYP_NAME)&#13;&#10;,FIT_FINANCINGTYP_ID&#13;&#10;,RTRIM(FIT_FINANCINGTYP_NAME)&#13;&#10;,trim(CURR.CCY_SWIFT_CODE)&#13;&#10;,NVL(FAC_SYNDICATED_PCT/100000000 * IFP_AMT,0)&#13;&#10;,NVL(IFP_AMT,0)/1000000&#13;&#10;,TO_CHAR(NULL)&#13;&#10;,TO_NUMBER(NULL)&#13;&#10;,TO_NUMBER(NULL)&#13;&#10;,TO_CHAR(NULL)&#13;&#10;,APP.APS_APPROACH_SUBTYPE_NAME&#13;&#10;,LGD.lgd_rating/100&#13;&#10;,PDR.PDR_RATING_PD&#13;&#10;,PTY_FAC_SUBTYP_CODE&#13;&#10;,IFP_PLANNED_FIN_DATE&#13;&#10;FROM&#13;&#10; INVESTMENT_FINANCE_PLANS&#13;&#10;,FACILITIES&#13;&#10;,EQUITY_FACILITIES&#13;&#10;,RISK_CATEGORIES&#13;&#10;,CURRENCIES CURR&#13;&#10;,FINANCING_TYPES&#13;&#10;,PRODUCT_TYPES&#13;&#10;,APPROACH_SUBTYPES APP&#13;&#10;,LGD_RATINGS              LGD&#13;&#10;,PD_RATINGS               PDR&#13;&#10;&#13;&#10;WHERE&#13;&#10;IFP_BANK_OPERATION_ID = ?&#13;&#10;AND   FAC_FINANCEPLAN_ID         = IFP_FINANCEPLAN_ID&#13;&#10;AND   FAC_FINANCEPLAN_ID         = EFC_FINANCEPLAN_ID&#13;&#10;AND   FAC_RISKCATEGORY_ID        = RKC_RISKCATEGORY_ID (+)&#13;&#10;AND   IFP_INTRNL_EXTRNL_CODE     = 'I'&#13;&#10;AND   IFP_CCY_ID                 = CURR.CCY_ID&#13;&#10;AND   IFP_PRODUCTTYP_ID          = PTY_PRODUCTTYP_ID&#13;&#10;AND   PTY_FINANCINGTYP_ID        = FIT_FINANCINGTYP_ID&#13;&#10;AND   IFP_APPROACH_SUBTYPE_ID    = APS_APPROACH_SUBTYPE_ID&#13;&#10;AND   PTY_FAC_SUBTYP_CODE       = 'EQ' &#45;- Equities&#13;&#10;AND   facilities.fac_lgd_rating   =  LGD.lgd_id (+)&#13;&#10;AND   FAC_FINANCEPLAN_ID         = PDR.PDR_FAC_ID (+)&#13;&#10;AND   FAC_STAT_CODE              in ('A','H')&#13;&#10;order by pty_fac_subtyp_code desc, FAC_REVOLVING_LOAN_IND desc, IFP_FINANCEPLAN_ID</odc:CommandText>
       <odc:CredentialsMethod>None</odc:CredentialsMethod>
       <odc:AlwaysUseConnectionFile/>
      </odc:Connection>
     </odc:OfficeDataConnection>
    </xml>
    <style>
    <!--
        .ODCDataSource
        {
        behavior: url(dataconn.htc);
        }
    -->
    </style>
     
    </head>
    <body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
    <table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
      <tr>
        <td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
          &nbsp;
        </td>
         <td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
          &nbsp;
        </td>
      </tr>
      <tr>
        <td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
          &nbsp;
        </td>
      </tr>
      <tr>
        <td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
          <div id='pt' style='height: 100%' class='ODCDataSource'></div>
        </td>
      </tr>
    </table>
     
    <script language='javascript'>
    function init() {
      var sName, sDescription;
      var i, j;
     
      try {
        sName = unescape(location.href)
     
        i = sName.lastIndexOf(".")
        if (i>=0) { sName = sName.substring(1, i); }
     
        i = sName.lastIndexOf("/")
        if (i>=0) { sName = sName.substring(i+1, sName.length); }
        document.title = sName;
        document.getElementById("tdName").innerText = sName;
        sDescription = document.getElementById("docprops").innerHTML;
     
        i = sDescription.indexOf("escription>")
        if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
        if (i>=0 && j >= 0) {
          j = sDescription.lastIndexOf("</", j);
          if (j>=0) {
              sDescription = sDescription.substring(i+11, j);
            if (sDescription != "") {
                document.getElementById("tdDesc").style.fontSize="x-small";
              document.getElementById("tdDesc").innerHTML = sDescription;
              }
            }
          }
        }
      catch(e) {
        }
      }
    </script>
    </body>
     
    </html>

    Monday, July 25, 2016 9:25 AM
  • Hi Ian Lafford,

    can you tell us which approach you want to select to achieve this issue.

    because we can see that you are using both approach like with user interface and also with codding.

    because the supporting forum is different for both the approach.

    so it is better if you clear the requirement.

    on this forum we can try to give you suggestion by codding.

    if you want a suggestion for user interface "Excel it pro discussion forum" is correct forum.

    so if you want suggestion regarding user interface then please let us know so that I can move this thread to that forum and you can get suggestions related to your issue.

    Regards

    Deepak


    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.

    Tuesday, July 26, 2016 7:46 AM
    Moderator
  • Hi Deepak,

    Frankly, I just wanted a solution either way! I believe this is something that ought to be working via the UI, but as it clearly wasn't I was keen to see if there was a way to code a solution.

    I have now done this myself - happy to add the code if anyone might find it useful, but it wasn't anything very clever: I simply read the .odc file in line by line and looked for the ConnectionString and CommandText tags, then extracted their contents and updated the query table properties accordingly. It now works, but it doesn't seem to me like an approach anyone ought to have to take.

    I'm happy to close the thread on here, but I would like to leave the one on excel IT Pro open as it seems to me the UI isn't doing what it should.

    Thanks for your assistance - much appreciated.

    Ian

    Tuesday, July 26, 2016 10:27 AM