Error creating linked server to Excel

[Andy]

Hi,
Please help, I'm getting desperate. Any ideas warmly welcomed!
I'm trying to read from a basic excel file (1000 or so rows from
column A) but am having problems. The code I am using is:

Declare @Return Int
SET NOCOUNT ON

Exec @Return= [master]..[sp_addlinkedSer ver] 'READ_XLS', 'EXCEL',
'Microsoft.Jet. OleDB.4.0' , 'e:\jsbackup\RA Codes.xls',
NULL, 'EXCEL 8.0'
print 'set up Return : ' + convert(varchar (10),@Return)
--NB E: is the drive as seen oon the server

EXEC sp_addlinkedsrv login
@rmtsrvname = 'READ_XLS',
@useself = 'true'
print 'login Return : ' + convert(varchar (10),@Return)


When I try to read from the (one) excel sheet in the file, via
Select * from [READ_XLS]...RACodes$
or to list what tables/sheets are available, via
exec sp_tables_ex 'READ_XLS'

I get the following error:

OLE DB provider 'Microsoft.Jet. OLEDB.4.0' reported an error.
Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet. OLEDB.4.0'
IDBInitialize:: Initialize returned 0x80040e4d: Authentication
failed.].

What am I missing?

*Many* thanks in advance.
Andy

[Gerrit-Jan Linker]

Re: Error creating linked server to Excel

A few things to check:

Do you have the .xls file already open?
Is e:\jsbackup\RAC odes.xls a server path?
I recall that you have to set named ranges for it to appear as tables
in Excel.

I don't know whether it helps but I have written an addin for Excel
that can push the data out from Excel (you are trying to pull it in
from the database). If you can reconsider your design and push it from
Excel you can perhaps use it.

SQL*XL is an addin for Excel that let you manage your database from
Excel. It is an end-user tool but it also lets you record all actions
as a macro (VBA) and therefore you can code/script applications with
it.

Have a look at SQL*XL at www.oraxcel.com

Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited

https://www.oraxcel.com