Short solution for short problems

Unprotected Cells on a Protected worksheet

I was looking for a good way to set some cells editable in Excel in a worksheet that is completely protected. 

I call a function at the end of the action that protects the entire sheet:

Public Sub ProtectSheet(sWorkSheet As String)

 Worksheets(sWorkSheet).Protect Password:=XXxxXXxx, AllowFiltering:=True

End Sub

Now to prevent some cells to be protected, you just need to set the cells to Locked = False before you protect the worksheet.

'Make sure this cell doesn't get protected when you protect the sheet.

 Cells(iCnt, 3).Locked = False

How to read an excel 2007 or 2010 file with the OleDbConnection.

How to read an excel 2007 or 2010 file with the OleDbConnection. My mistake was that I started out from a older connection string:


sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties=Excel 8.0;";



I got 2 different errors:

1 Could not find installable ISAM.

2 External table is not in the expected format.

It could only be fixed off course by using the correct connection string. So instead of using the Jet.OLEDB I used the ACE.OLEDB (Microsoft Access Database Engine 2010)


sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadPath + ";Extended Properties=Excel 12.0";



Then here is the fill code to read en Excel file into a DataSet


private void Upload(string uploadPath)
            string sConnectionString;
            //sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties=Excel 8.0;";
            sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadPath + ";Extended Properties=Excel 12.0";
            OleDbConnection Exlcon = newOleDbConnection(sConnectionString);
            string sDate = "";
            string sRowsNtInserted = "";
            int iRows = 0;
            DateTime date1;
            string sMonthName = "";
                //ShowAlertMessage("Please select valid excel file.");
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", Exlcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            //Read the dataset line by line and process the tasks, see if they already exist.
            if (objDataset1 != null)
                foreach (DataRow dr in objDataset1.Tables[0].Rows)




Inserting functions from .net into Excel. (VLOOKUP) "Exception from HRESULT: 0x800A03EC"

I got a small task to write a program that takes single language excel sheets and makes them multilanguage.
To accomplisch this we added a translation worksheet that will be filled with all translations of text in de excel worksheets for 4 different languages.
Then the text in the excel will be replaced with a VLOOKUP function that looks up the string in the Translation worksheet.
The program was ready in half a day, but then Excel strarted giving me a hard time.
If I tried to insert the VLOOKUP functino from code, it kept giving me an error. Inserting strings or even a SUM function was no problem, but the VLOOKUP wouldn't work. The functino looks like this: =VLOOKUP("Bath / Object";TRANSLATIONS;SPRACHE;FALSE).
This code was trying to insert the code in the cell where the text Bath / Object was:
string value = String.Format(@"=VLOOKUP(""{0}"";TRANSLATIONS;SPRACHE;FALSE)", str);
rangeSet.FormulaR1C1 = value;
First I tried all kinds of things to replace the quotes, but it didn't help. I tried setting the Value or Value2, Formula etc but it kept ginving me the error. Finally after a long search I recorded a macro in Excel and edited this macro and swaw that the Macro was using comma's (,) instead of colons (;).
So I changed the code looking like this and it works!!!!
string value = String.Format(@"=VLOOKUP(""{0}"",TRANSLATIONS,SPRACHE,FALSE)", str);
rangeSet.FormulaR1C1 = value;
The Nice error I got was: Exception from HRESULT: 0x800A03EC. Try to figure this out!