title: Excel VBA connect to remote Oracle DB with InstantClient
body: <p><br>
  I am trying to use Excel (mainly 2003, for more user compatibility) to connect to a remote Oracle DB. I would like to run a .sql script and return the dataset to a worksheet.<br>
  I am on a Windows 7 64bit machine. I do not know the specs of the Oracle DB server.<br>
  I would like to keep this as lightweight as possible (no extra file installations on client machines, use shared network locations for required files as much as possible)<br><br></p>
  
  <hr>
  
  <hr>
  
  <h2>So Far:</h2>
  
  <p>I downloaded and "installed" the InstantClient from Oracle (versions 12.1 and 11.2 for both 32bit and 64bit) onto a remote network location. <br>
  I tried connecting to the Oracle DB using SQL Plus and it worked fine (I tried several of the installed InstantClient versions to see if there would be any compatibility issues). <br>
  As a test: using SQL Plus and the Shell function in VBA, I was able to successfully spool the data into a separate excel file.<br>
  <br></p>
  
  <hr>
  
  <p>I tried several different connection string formats using various drivers/providers:<br></p>
  
  <ul>
  <li>Driver={Oracle in instantclient_11_2}</li>
  <li>Driver={Microsoft ODBC for Oracle}</li>
  <li>Provider=MSDAORA</li>
  <li>Provider=MSDAORA.1</li>
  <li>Provider=OraOLEDB.Oracle
  <br></li>
  </ul>
  
  <hr>
  
  <p>Errors I Received:<br></p>
  
  <pre><code>"Run-time error '-2147467259 (80004005)':
  [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
  The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation..."
  </code></pre>
  
  <p><br></p>
  
  <pre><code>"Run-time error '-2147467259 (80004005)':
  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
  </code></pre>
  
  <p><br></p>
  
  <pre><code>"Run-time error '3706':
  Provider cannot be found. It may not be properly installed"
  </code></pre>
  
  <p>And a few other similar errors.
  <br><br></p>
  
  <hr>
  
  <p><br>
  I have added the network location containing the instantclient files to my PATH environmental variable. Not sure what other environmental variables I require or even if my current one is correct.</p>
  
  <p>Do I need:<br>
  TNS_ADMIN?
  ORACLE_HOME? 
  <br><br></p>
  
  <hr>
  
  <hr>
  
  <h2>Question:</h2>
  
  <ul>
  <li><p>How do I connect to the <em>remote Oracle DB</em> with VBA, using the <em>instantclient</em> files that are located in a <em>network (share) location</em>?<br></p>
  
  <ul>
  <li><p>What is the correct <em>full</em> connection string? (I used the EZConnect format with SQLPlus; are the actual connection details the same? and for clarification, could someone post an example of how the EZConnect format converts to the other format(s)?) <br></p>
  
  <pre><code>My EZConnect Format: username/password@myserver.some.thing.com/mydb
  </code></pre></li>
  <li><p>What "provider" or "driver" <em>should</em> I use for this purpose and are there any significant differences?<br></p></li>
  <li>What environmental variables do I <strong>require</strong> to make this work?</li>
  </ul></li>
  </ul>
  
  <p>I've found a lot of questions that are similar or related, but none that directly answered my question or helped me enough to completely solve it.</p>