lundi 20 août 2012 11:11
I am developing a VSTO Excel Addin using C#.net .I want to enter data in excel invoked by Visual studio and read the same using OLEDB from this WorkBook where the VSTO addin is invoked in Excel .
I tried the below code but it doesnt work.It works if i give a path of anexcel file from desktop.
wb = Globals.ThisAddIn.Application.ActiveWorkbook;
string stCurWBPath = Globals.ThisAddIn.Application.StartupPath + "\\" + wb.Name + ".xlsx";
string strQuery = "select * from [Sheet1$]";
string strProvider = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + stCurWBPath + ";Mode=ReadWrite" + @";Extended Properties=""Excel 8.0;HDR=Yes""";
using ( Connection = new OleDbConnection(strProvider))
using (OleDbCommand objCmd = new OleDbCommand(strQuery, Connection))
objCmd.CommandType = CommandType.Text;
objCmd.ExecuteNonQuery(); //line 12
it fails at the above statement (line 12).Says cannot access the object Sheet1$
any suggestions ?
- Modifié Krrrishna lundi 20 août 2012 11:20
Toutes les réponses
lundi 20 août 2012 12:04Modérateur
The problem is that you're mixing up the "interop" workbook with the workbook as a data source. You shouldn't try to set up an OLEDB connection to an open workbook - the workbook is "file locked".
You need to either save the file path and name, close the workbook, then use the OLEDB connection OR you need to retrieve the data in a different manner. For example, if you use a ListObject you can create a DataBinding to it in order to write and read the data.
Cindy Meister, VSTO/Word MVP
- Marqué comme réponse Quist ZhangMicrosoft Contingent Staff, Moderator vendredi 24 août 2012 09:08