and the details:

first, you gotta have the ACE drivers installed, look at this post FIRST:

http://www.sqlservercentral.com/Forums/FindPost1407497.aspx

then here's a mockup of the code: there's a couple of issues...I'm ASSUMING every sheet is just named the defaultSheet1$, and have the same structure/number of columns, and no headers/comments before the data starts so tehy can be treated like a table in a linked server.

--a table to loop thru filenames drop table ALLFILENAMES

CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))

--some variables

declare @filename varchar(255),

@path varchar(255),

@sql varchar(8000),

@cmd varchar(1000)

--get the list of files to process:

--#########################################

SET @path = 'C:\DB\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB2\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB3\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB4\'

SET @cmd = 'dir ' + @path + '*.txt /b'

INSERT INTO ALLFILENAMES(WHICHFILE)

EXEC Master..xp_cmdShell @cmd

UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

--#########################################

--cursor loop

declare c1 cursor for SELECT WHICHPATH + WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.xls' or WHICHFILE like '%.xlsx'

open c1

fetch next from c1 into @path

While @@fetch_status <> -1

begin

--===== Drop the text server

EXEC dbo.sp_DropServer 'MyExcelACE', 'DropLogins'

--===== create the Linked Server for the current file

EXEC sp_addlinkedserver @server = 'MyExcelACE',

@srvproduct ='',

@provider = 'Microsoft.ACE.OLEDB.12.0',

@datasrc = @path,

@location = NULL,

@provstr = 'Excel 12.0'

EXEC dbo.sp_AddLinkedSrvLogin 'MyExcelACE', FALSE, NULL, Admin, NULL

EXEC sp_tables_ex 'MyExcelACE'

--INSERT INTO MyTable(ColumnList)

--SELECT ColumnList FROM MyExcelACE...Sheet1$

fetch next from c1 into @path

end

close c1

deallocate c1