How to access the current ActiveWorkBook instance when the VSTO addin is invoked in Excel ?

Question

Hi

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))
            {
                Connection.Open();
                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 ?

Answer 1

Hi Krishna

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