Export SQL data update to Excel

[ropewrench]

I am trying to send the information from a query on SQL server table to update an existing Excel spreadsheet. (Not append to it. My initial INSERT works fine but I really want to have it update instead. DTS is not an option at this time. Attached is the code. Any help would be greatly appriciated.

-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC dbo.sp_addlinkedserver 'Excel1',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\testing.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin Excel1,FALSE,NULL,NULL,Null

GO
declare @tsqlStmt varchar(500)

-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server

set @tsqlStmt = 'UPDATE Excel1...[Sheet1$]' + 'SELECT Warehouse,VENDOR,PRODUCT_CODE, PROD_DESC, LOCATION_CODE,LOT_CODE, NS_STATUS From [priyadev].dbo.RinNSStatus
Where [priyadev].dbo.RinNSStatus.Warehouse_ID = ?????'

print @tsqlStmt

-- execute dynamically the TSQL statement
exec (@tsqlStmt)

-- drop the linked server
EXEC sp_dropserver 'Excel1', 'droplogins'



respectfully,
ropewrench

 

[LNBruno]

Hi, ropewrench.

I'm struggling with the same problem -- and your post helped a lot!

This isn't a "hi-tek" answer, but could you simply delete the spreadsheet before executing the procedure?

In my case, I'm using Excel templates (charts), so I'm going down the development path (VB) to try to update specific sheets/cells.

I'm hoping you found a better solution ???

< M!ke >

 

[ropewrench]

LNBruno,
That is exactly what I did. The SP deletes the Excel file, then using an Excel template which is copied to a new name replaces the deleted one and this is then attached for sending. It works pretty good.

respectfully,
ropewrench