How to update an open Excel in C# using OLEDB?

How to tell c# OleDb driver for excel to always read as text?

• I am using the Microsoft.Jet.OLEDB.4.0 provider to read from an excel doc into my visual studio c# application... I know that using the property IMEX=1 will cause the reader to always read "intermixed" types as text. However, it appears that if the first cell in the column is a number and later cells are text, even with this property all cells are not interpreted as text. So does anyone know if in this situation there is a way to tell the provider to read all cells no matter what as text?

• Answer:

  Have you tried using the leading single quote in all non-empty, non-formula cells in the worksheet? Or is that too inconvenient when working in Excel? I am not even sure what that does to the data when it is read into your c# application. But if that solves the problem, it would not be difficult to add the single quote to the beginning of every non-empty, non-formula cell in a worksheet. To save time you would want to use a range that includes all non-empty cells but not much more. Say that range was "A1:Z300", then Sub AddSingleQuote() Dim oCell As Object For Each oCell In Range("A1:Z3000").Cells If Len(oCell.Text) > 0 Then If Left$(oCell.Formula, 1) <> "=" Then oCell = "'" + oCell.Text End If End If Next End Sub would add a single quote to the front of every non-empty, non-formula cell. Multiple single quotes at the beginning of a cell are compressed into one by Excel, so a check for existing single quotes isn’t necessary. Look how a subroutine to remove the single quotes is almost identical the subroutine to add them. Sub RemoveSingleQuote() Dim oCell As Object For Each oCell In Range("A1:Z3000").Cells If Len(oCell.Text) > 0 Then If Left$(oCell.Formula, 1) <> "=" Then oCell = oCell.Text End If End If Next End Sub Using this subroutine would remove all leading single quotes in the stated range, even ones that were there before and you wanted to keep.