An excel file has columns such as : Security_ID, Security_Name, Bid_Price, Ask_Price If possible please let me know how to correct this query to update the spreadsheet from sql server 2005 data. Thanks UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 'Data Source=C:WorkData.xls; extended Properties=Excel 8.0')...AllBonds$ set Bid_Price = Ask_Price = where Security_ID = The result is coming from this sql: select sec.Security_ID--, sec.Security_Name, p.Bid_Price, p.Ask_Price from tblPrices p right join tblSecurities sec on sec.Security_ID = p.Security_ID where convert(varchar(11), Price_Quote_Date) = dateadd(d, -4, convert(varchar(11), getdate()))
Is that a question? I doubt that you can use UPDATE OPENDATASOURCE but perhaps it is perfectly valid syntax. If not, then create some VBA code to interface using the Excel libraries, and refer to that from your SQL 2005 project. The trick is to do this manually in Excel (connect to SQL Server, identify the Excel cells to be updated, and the new values from SQL) whilst recording your actions with the fabulous macro recorder in Excel. This recorder produces the full VBA syntax, which you can then include in your SQL 2005 object (although you'll need to initialize an Excel object, and fine-tune the code to work off that object).