Now I'm just getting confused.

First of all I checked the authority on the directory containing the file and Everyone has Full Control so it can't be that.

I'm testing this on three machines one running VISTA native, another running VISTA under VMWARE and a SERVER 2003 machine (WHich has always worked). This morning the machine running VISTA native works fine. The VMWARE machine is still having the same issue.

I've run Process Monitor from sysinternals and have noticed that the following (quite possibly significant) registry key is missing from the failing machine but is present on the working one.

I exported it from the working machine added it to the failing machine and tried again after bouncing the SQL instance. Now I get

These keys are not present on the working machine. If I remove the HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0 keys from the working machine it fails. The working machine does have a bunch of other stuff that could be impacting these comparisons, like SQL2008 and SSIS where as the failing VMWARE based machine only have VISTA and SQL Server 2005. I've searched the working machine registry for "DynamicParameters" (the first of the missing values) but it's not there. I also (as previously stated) have XP and Server 2003 machines where this all works properly, I've searched these registries for "DynamicParameters", also not found.

So I added HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess to the registry as a DWORDs and set their values to 1. IT NOW WORKS.......Still getting NAME NOT FOUND on the other values but it does read the spreadsheet.

To summarise, using Microsoft.ACE.OLEDB.12.0 and openrowset on XP or server 2003 machines seems to work fine on Vista machines however it seems to be missing several registry keys (Which don't appear to be present on the working machines), if the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess keys are added it works.

While I'm rather pleased that I managed to get this mess working I'm concerned about releasing my applicaiton into production when I don't really understand what caused the problem.

Any suggestions anyone on why this might be the case?

I'm a local and domain administrator on all machines and in all cases SQL Server is running under the local system account.