Export data to existing EXCEL file from SQL Server table

Question

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\contact.xls;', 
'SELECT * FROM [$Sheet1]') 
select * from Persons

This code not working..It is giving the error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned 
message "The Microsoft Jet database engine could not find the object '$Sheet1'.  
Make sure the object exists and that you spell its name and the path 
name correctly.".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" 
for linked server "(null)".

Please help

If i remove the '$' then also giving the same error..Please help anyone — michelle
– michelle, Commented Aug 2, 2011 at 11:32

Kieren Johnstone · Accepted Answer · 2011-08-02 11:27:43Z

3

It turns out that The Microsoft Jet database engine could not find the object '$Sheet1', and so you should Make sure the object exists and that you spell its name and the path name correctly.

answered Aug 2, 2011 at 11:27

Kieren Johnstone

42.1k16 gold badges100 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

michelle Over a year ago

No I have checked the object and sheet name

Kieren Johnstone Over a year ago

Is there a sheet called $Sheet1?

Jacob · Accepted Answer · 2011-08-02 11:39:24Z

0

Excel sheet names

Identify the name of the sheet you are trying to "connect" to. Have a look at the picture above. The standard sheets are called "Sheet1" to "Sheet3". Your workbook under D:\contact.xls probably has custom names. Use the name you find there and add a $ to the end of it.

'SELECT * FROM [Sheet1$]'

answered Aug 2, 2011 at 11:39

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

5 Comments

michelle Over a year ago

Hi cularis..how will I find the custom names?can u please elaborate

Jacob Over a year ago

There is even a picture in my answer! Just open contact.xls with excel and look at the buttom left corner.

Jacob Over a year ago

Did you try putting the dollar at the end of Sheet1 and removing it from the front?

michelle Over a year ago

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\contact.xls;', 'SELECT * FROM [Sheet1$]') select * from Persons

Kieren Johnstone Over a year ago

Michelle: Click Start, Run. Enter d:\contact.xls, press Enter. Confirm there is a sheet called exactly Sheet1. Try the code above. Verify that the error message says EXACTLY the same as in your original question? As a developer you'll learn quickly that 'still not working' is THE most useless piece of information you can ever possibly conceive of giving.

Collectives™ on Stack Overflow

Export data to existing EXCEL file from SQL Server table

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related