Dailycode.info

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)
        {
            //Doupload
            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 = "";
 
            try
            {
                Exlcon.Open();
            }
            catch
            {
                //ShowAlertMessage("Please select valid excel file.");
                return;
            }
            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)
                {
                    //dosomething
                }
            }
        }

 

 

 


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:
 
xlApp.Worksheets.get_Item(index).Select();
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!!!!
xlApp.Worksheets.get_Item(index).Select();
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!