Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Connecting to SQL Server Database - Need your Help
I am trying to use OLEDB provider for SQL Server to connect, but its failing. Is it possible to connect SQL Server like this..? But when I tried with ODBC, its working. Look into this code: try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); // Create a QueryTable that starts at cell A1. m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objQryTables = m_objSheet.QueryTables; m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provi der=SQLOLEDB;Data Source=xxxxxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees"); m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.Refresh(false); // Save the workbook and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book7.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } catch(Exception ee) { throw ee; } Any help greatly appreciated. -- Thanks, Rajesh in Christ. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Connecting to SQL Server Database - Need your Help
Rajesh,
Is this Net? If so I don't have that to test, but I know that SQL Server has an OLEDB data provider, so it does work. What error do you get? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rajesh G" wrote in message ... I am trying to use OLEDB provider for SQL Server to connect, but its failing. Is it possible to connect SQL Server like this..? But when I tried with ODBC, its working. Look into this code: try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); // Create a QueryTable that starts at cell A1. m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objQryTables = m_objSheet.QueryTables; m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provi der=SQLOLEDB;Data Source=xxxxxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees"); m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; m_objQryTable.Refresh(false); // Save the workbook and quit Excel. m_objBook.SaveAs(m_strSampleFolder + "Book7.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); } catch(Exception ee) { throw ee; } Any help greatly appreciated. -- Thanks, Rajesh in Christ. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Connecting to SQL Server Database - Need your Help
Bob
Its in .Net All I am struggling with is this line m_objQryTable = (Excel._QueryTable)m_objQryTables.Add("OLEDB;Provi der=SQLOLEDB;Data Source=xxx;Initial catalog=Northwind;user id=sa;password=;", m_objRange, "Select * From Employees") Using QueryTable Object, I want to embed the DataSource and Query into the Excel Sheet I would like to use OLEDB provider, that is the difference. With ODBC its working fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically connecting to a remote database | Excel Discussion (Misc queries) | |||
connecting to back end server | Excel Discussion (Misc queries) | |||
Retrieving data from database (MS Sql Server) to Excel | Excel Discussion (Misc queries) | |||
SQL Server UPDATE Database from Excel Table | Links and Linking in Excel | |||
i want to connect excel with sql server 2000 as database with macr | Excel Discussion (Misc queries) |