Odd behaviour with INSERT INTO ... SELECT with Excel 2007 SQL

Now then. I have this VBScript script

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=""" _
                        & inputfile & """;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

ado.CursorLocation = 3
ado.open

ado.Execute "INSERT INTO [recalculated] ([TIME PERIOD]) " & _
            "SELECT Format(CDate(Mid([TIME PERIOD], 8, 13)), 'mm/dd/yyyy') " & _
            "AS [TIME PERIOD] FROM [input]"
ado.close

The tables in the workbook are named ranges in their own worksheets

input:

recalculated (ie a blank table with the same column name):

The script runs without raising an error however the data is inserted into the input table instead of recalculated:

(I note that this is actually different behaviour to what I was seeing earlier - I added the TOP 3 to the SQL query for this question because originally the table was overwritten and not appended to)

But if I change the expression to something else, eg

ado.Execute "INSERT INTO [recalculated] ([TIME PERIOD]) " & _
            " SELECT TOP 3 'cheese' AS [TIME PERIOD] FROM [input]"

it works fine, recalculated is populated and input is left as is.

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?

I've just found that this works as expected if the workbook is already open in Excel when the script is run

When I run the code it works as I would expect i.e. the inserts go into the recalculated table.

Here's similar script that recreates your scenario but using a new workbook created from scratch:

' Const inputfile = "New Microsoft Office Excel Worksheet (2).xlsx"
Const inputfile_with_path = "C:\New Microsoft Office Excel Worksheet (2).xlsx"

Dim ado: Set ado = CreateObject("ADODB.Connection")
ado.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" _
                        & inputfile_with_path & """;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

ado.CursorLocation = 3
ado.Open

ado.Execute "CREATE TABLE [input] ([TIME PERIOD] VARCHAR(255));"
ado.Execute "INSERT INTO [input] VALUES ('WEEK OF JUN 21, 2010 (B)');"
ado.Execute "INSERT INTO [input] VALUES ('WEEK OF JUN 07, 2010 (B)');"
ado.Execute "INSERT INTO [input] VALUES ('WEEK OF JUN 21, 2010 (B)');"
ado.Execute "INSERT INTO [input] VALUES ('WEEK OF JAN 04, 2010 (B)');"
ado.Execute "INSERT INTO [input] VALUES ('WEEK OF JAN 04, 2010 (B)');"

ado.Execute "CREATE TABLE [recalculated] ([TIME PERIOD] VARCHAR(255));"

ado.Execute "INSERT INTO [recalculated] ([TIME PERIOD]) " & _
            "SELECT Format(CDate(Mid([TIME PERIOD], 8, 13)), 'mm/dd/yyyy') " & _
            "AS [TIME PERIOD] FROM [input]"

ado.Close