Importing Excel data into SQL Server 2005

I have been at a total loss trying to import Excel data into a SQL database. When I first started this project, my task was to use the existing data in the Cost Recovery database to build and develop a working model. I had absolutely no luck getting the data into tables. The method to which I finally resorted was importing the data into Microsoft Access tables; afterwhich, I used the following SQL statement to create a table and import the data:

SELECT * INTO mySQLTable FROM myLinkedServer...myAccessTableName

This just an example where mySQLTable is the name of the table created in SQL, myLinkedServer is a linked server (I have a posting on how to do this), and myAccessTableName is the name of the table I created in Access. This all works, but is tedious.

What I found out when importing data directly from Excel is closing the Excel spreadsheet when trying to import. Otherwise, I kept getting this error:


-- Msg 7399, Level 16, State 1, Line 22
-- The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
-- Msg 7303, Level 16, State 1, Line 22
-- Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I googled and re-googled looking for an answer. I tried suggestion after suggestion with no luck. It was when I closed the workbook, did I have success. The workbook can NOT be open while trying to import.
The two following statements work, but the first uses a Linked Server and is simpler for me:


SELECT * INTO mySQLTable FROM myLinkedServer...[myExcelWorksheet$]

Again, mySQLTable is the name of the SQL table I want to create and into which import data, myLinkedServer is the name of the Linked Server accessing the Excel workbook, and myExcelWorksheet is the name of the worksheet in the workbook. Include the brackets [ ] and the $ in the worksheet name.

The second method is as follows:


SELECT * INTO mySQLTable FROM openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=myFullFilePath\myExcelWorkbook.xls;HDR=YES','SELECT * FROM [myExcelWorksheet$]')

Names are the same as above. myFullFilePath is the complete and full path of where the workbook is located. myExcelWorkbook.xls is the filename of the workbook itself.