title: Excel - Refresh and Validate Data From SQL (VBA)
body: <p>I'm using office 2013 and SQLServer 2008 R2. I have an Excel VBA proc that inserts a list of commissions for sales into a database. One of the columns is SalesRepID, which clearly has to be valid. When it was written, we had a fairly static sales team, but we've since acquired two other companies who do less specialized work, so there's a quite a bit of change in the sales force now. </p>
  
  <p>The current methods has a list of sales repID's and names in sorted named range, which is then used as validation criteria: the user is given a list of sales reps in drop down list in Excel and when they select one, it uses VLookup to insert the SalesRepID in a hidden cell. This is then used in the insert operation.</p>
  
  <p>What I want to do now is give the users a refresh button (and ideally run the refresh on open). Some of the billers work at home at the weekend, so this needs to be able to work offline (obviously, if a new rep is added and they haven't refreshed they won't see the new rep).</p>
  
  <p>I don't want to have to define anything like ODBC sources on a user's physical machine</p>
  
  <p>I'm trying to figure out the way to solve this. One way is to add a refresh button that will execute a query, insert the current list of sales reps and update the range (or use a dynamic range) to be the current no of records returned. (I'm assuming I can update a named range's address, or that a dynamic range will update if the data is inserted from a SQL query)</p>
  
  <p>For example:</p>
  
  <p>Initial Range</p>
  
  <pre><code>1 Tom
  2 Dick
  3 Harry
  </code></pre>
  
  <p>SalesReps range = A1:B3</p>
  
  <p>User Hits Refresh: Deletes old ranges contents. insert new list</p>
  
  <p>New Range</p>
  
  <pre><code>1 Tom
  2 Dick
  3 Harry
  4 Fred
  </code></pre>
  
  <p>SalesReps range = A1:B4</p>
  
  <p>Can anyone think of a better way to this?</p>
  
  <p>Regards</p>
  
  <p>Mark</p>