title: Odd behaviour with INSERT INTO ... SELECT with Excel 2007 SQL
body: <p>Now then. I have this VBScript script</p>
  
  <pre><code>Const inputfile = "New Microsoft Office Excel Worksheet (2).xlsx"
  
  dim ado: set ado = CreateObject("ADODB.Connection")
  ado.connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" _
                          &amp; inputfile &amp; """;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  
  ado.CursorLocation = 3
  ado.open
  
  ado.Execute "INSERT INTO [recalculated] ([TIME PERIOD]) " &amp; _
              "SELECT Format(CDate(Mid([TIME PERIOD], 8, 13)), 'mm/dd/yyyy') " &amp; _
              "AS [TIME PERIOD] FROM [input]"
  ado.close
  </code></pre>
  
  <p>The tables in the workbook are named ranges in their own worksheets</p>
  
  <p><code>input</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/uPIB9.png" alt="input table"></p>
  
  <p><code>recalculated</code> (ie a blank table with the same column name):</p>
  
  <p><img src="https://i.stack.imgur.com/pLdZ7.png" alt="recalculated table"></p>
  
  <p>The script runs without raising an error however the data is inserted into the <code>input</code> table instead of <code>recalculated</code>:</p>
  
  <p><img src="https://i.stack.imgur.com/IXjtL.png" alt="resulting input table"></p>
  
  <p>(I note that this is actually different behaviour to what I was seeing earlier - I added the <code>TOP 3</code> to the SQL query for this question because originally the table was overwritten and not appended to)</p>
  
  <p>But if I change the expression to something else, eg </p>
  
  <pre><code>ado.Execute "INSERT INTO [recalculated] ([TIME PERIOD]) " &amp; _
              " SELECT TOP 3 'cheese' AS [TIME PERIOD] FROM [input]"
  </code></pre>
  
  <p>it works fine, <code>recalculated</code> is populated and <code>input</code> is left as is.</p>
  
  <p>Anyone know what's going on and how I can fix it? At the very least, can someone reproduce this as just to prove I'm not a lunatic?</p>
  
  <hr>
  
  <p>I've just found that this works as expected if the workbook is already open in Excel when the script is run</p>