Update Excel from SQL server

Question

Post reply

Update Excel from SQL server

Fab_Innocenti

Old Hand

Points: 359
More actions
September 26, 2003 at 3:36 am

#160778

Hello!
I need to update an excel sheet with sql query result.
Thanks!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Can you be more specific.

Do you want Excel to update the worksheet or for sql to create the worksheet?

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Ooops!

Just noticed this is in the DTS section and therefore assume you want to DTS to Excel.

You can either DTS direct to Excel but there are data/null problems or you could DTS to a csv file and open it with Excel.

Edited by - davidburrows on 09/26/2003 07:46:43 AM

Far away is close at hand in the images of elsewhere.
Anon.

Fab_Innocenti Old Hand Points: 359 More actions · Answer 3

hello! I have a sheet excel and I have to update particular cells of the sheet with the result of a sql query. I have found some example but I would want to index the cells that I want to update for example

Update Prova...Foglio set $A$1 = 'pippo'

Thanks!!!

quote:
Can you be more specific.
Do you want Excel to update the worksheet or for sql to create the worksheet?

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Based upon your example you are using Linked Server to access Excel. When doing this sql does not know it is Excel (this is handled by the driver) and will treat each worksheet as a table with first row as column names. You can update worksheets by using

update linkname...sheetname set columnname='data' where condition

SQL will not recognise $A$1 as this is Excel.

The only way I can see to enable you to update the spreadsheet is to write a macro in the workbook that gets the results of the query and updates cells. Or create an ActiveX script to use Excel's objects.

Edited by - davidburrows on 09/29/2003 02:31:11 AM

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Fab_Innocenti,

hello! I have a sheet excel and I have to update particular cells of the sheet with the result of a sql query. I have found some example but I would want to index the cells that I want to update for example

Update Prova...Foglio set $A$1 = 'pippo'

Why do you need to update Excel from SQL Server and not just go the other way and use Microsoft Query to query SQL Server?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gljjr SSChampion Points: 10080 More actions · Answer 6

You can use OpenDataSource to update an XLS file provided the XLS worksheet is shared and you have "Field Names" at the top of every column.

 

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'

    ,'Data Source="d:\junk\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0'

    )...Sheet1$(Field1,Field2)

SELECT 'who','doneit'



SELECT * FROM  OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'

    ,'Data Source="d:\junk\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0'

    )...Sheet1$

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Edited by - gljjr on 10/01/2003 4:12:02 PM

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Gary Billins SSC-Addicted Points: 454 More actions · Answer 7

I get this message when I try the openDataSource command

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.

OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'].

I created an excel worksheet and placed

the words Field1 and Field2 in row 1

columns 1 and 2 respectively.

Thanks

prakash shinde Old Hand Points: 312 More actions · Answer 8

I am getting error when trying to import data from excel to sql server OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table