This article describes the way to read or write into the excel workbook(or a file, used interchangeably) pro-grammatically using C#.NET language and ACE Oledb data providers by Microsoft.
This covers the following topics:
- System Requirements
- Development Environment
- Versions of Excel files which can be read or written
- How to build a connection string?
- How to build a command string?
- Possible errors and exceptions
System Requirements
To read/write the excel worksheet using ACE oledb providers, MS office need not to be installed on a machine. An installable package containing ACE oledb providers can be installed from ACE OLEDB Installer Location
Go to this link to install the required version and also check the system requirements.
Note: You can install either 32 bits version or 64 bits version but not both. Also, if you have 64 bits office installed then you can’t install 32 bits ACE oledb and vice versa.
Check the requirements carefully on the page.
Development Environment
- ACE 12.0 oledb Data Providers dlls
- Development IDE – Visual Studio [or simple Notepad]
Versions of Excel files which can be read or written
This ACE 12.0 oledb data provider can carry out operations on all excel files till version 2010.
How to build a Connection String?
A typical example of connection string:
Provider=Microsoft.ACE.OLEDB.8.0;Data Source=;Extended Properties="Excel 8.0;"
Below is the description for each part:
- Provider : It is the main oledb provider that is used to open the excel sheet. This can be Microsoft.Jet.OLEDB.4.0 for Excel 97 onwards Excel file format or Microsoft.ACE.OLEDB.12.0. So far it is tested with upto Excel 2010 version.
- Data Source : It is the full path of the excel workbook. Replace with the full path of your existing excel workbook/ file.
- Extended Properties (Optional) : Extended properties can be applied to Excel workbooks which may change the overall activity of the excel workbook from your program. The most common ones are the following :
- HDR : It represents Header of the fields in the excel table. Default is YES. If you dont have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as f1,f2 etc.
- ReadOnly : You can also open excel workbook in readonly mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook
- FirstRowHasNames : It is the same as HDR, it is always set to 1 ( which means true) you can specify it as false if you dont have your header row. If HDR is YES provider disregards this property. You can change the default behaviour of your environment by changing the Registry Value- [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames] to 00 (which is false)
- MaxScanRows : Excel does not provide the detailed schema defination of the tables it finds. It need to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 – 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default. Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hope Microsoft fixes this issue to its later versions
- IMEX : (A Caution) As mentioned above excel have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur of you have mixed data in one column. Say you have data of both integer and text on a single column, in that case excel will choose its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
For e.g., In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
To work around this problem for data, set “IMEX=1” in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. You can change the enforcement of type by changing [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well
Thus if you look into the simple connectionstring with all of them, it will look like:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\testexcel.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""
or
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\testexcel.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""
How to build a command string?
If you have ever written a MS SQL query then you will not have difficulty in writing a command string. Here, command strings are written or defined in the same fashion as in MS SQL.
Think of each excel sheet as a MS SQL table from which data is to be fetched. Thus, complete excel file as a database.
Below image is the snapshot of a sample excel sheet which we will try to read or write into.
Read Command String
In order to read the excel sheet, “SELECT” command is used. Either you may want to read the complete excel sheet or you may be interested in reading just a block of data. Both scenarios are supported and this is what us different from defining the MS SQL “Select” query.
Range (block of data) is defined using A2:R5 format.
Don’t worry about ranges. I have described it in the below section.
- How to read complete excel sheet?
Below is the sample command string to read the complete data in an excel sheet:
Select * from [NameOfExcelSheet$]
Here, NameOfExcelSheet is the name of an excel sheet. Excel file can have multiple sheets. So, one has to specify the name of the sheet in order to fetch the data.
This command string reads the complete excel sheet.
Note: This may read empty rows, columns as well if there is any hidden data or there are empty rows in b/w the filled data. - How to specify range to read a block of data from excel sheet?
Below is the sample command string to read the complete data in an excel sheet:
Select * from [NameOfExcelSheet$A1:E5]
Here, NameOfExcelSheet is the name of the excel sheet. Excel file/workbook can have multiple sheets. So, one has to specify the name of the sheet in order to fetch the data.
This command string reads the complete excel sheet.
And, text after ‘$’ sin specifies the range of data to be read. Thus, as per the above command string, it will read the data from the excel cell A1 till E5. In other words, it will read 5 rows from row# ‘1’ to ‘5’ and 5 columns from column# ‘A’ to ‘E’. This can be beneficial in reading the data in chunks which can be easily achieved by dynamically specifying the range in the above format.
Another way to specify range is:
Select * from [NameOfExcelSheet$A1:E]
Here, as per the above command string, it will read all rows starting from row# ‘1’ and 5 columns from column# ‘A’ to ‘E’
Write Command String
This also corresponds to DML queries (INSERT,UPDATE and DELETE) in MS SQL.
You can write:
INSERT INTO [NameOfExcelSheet] VALUES('firsttextcol', 2, '4/11/2009');
[I assume First Column is char field, 2nd col is integer, 3rd is Date]
DELETE FROM [NameOfExcelSheet] Where secondintcol=2;
UPDATE [NameOfExcelSheet] SET secondintcol = 3 where firsttextcol = ‘firsttextcol’;
As in MS SQL, you can use [] (Square brackets) to allow spaces within column names and table names.
How to create an excel worksheet?
Simple. Use
Create table [NameOfExcelSheet] ()
For e.g. Create table [myTableName] (col1 int, col2 char(20))
How to Drop an excel workSheet?
To drop an excel worksheet, just use
Drop table [NameOfExcelSheet]
This will drop the worksheet.
Note:
- About Deleting Worksheet:
If this is the last worksheet, it will not delete the workbook file. You need to do it yourself. - About specifying ranges:
When you specify a worksheet as your source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range. Using MDAC you cannot add new rows beyond the defined limits of the range, otherwise you will receive Exception: “Cannot expand named range”
Possible errors and exceptions
- 1)”Microsoft.ACE.OLEDB.12.0-provider” is not registered
Reason:This is because of mismatch b/w compilation configuration in Visual Studio and the ACE version installed on the system.
Resolution:Make sure that both are same. Compile your app using the 64x bits if 64 bits ACE is installed or use 32 bits ACE.
SPONSORED
DRESS YOUR HAIR Like Never Before. Coming to YOU in United States By GKHAIR
Sharing ideas, Sharing experiences 
Just thought I’d say that this is a great article. Thanks for putting it together. I can’t imagine why anyone rated this as “Not helpful at all” as some folks did.
LikeLiked by 1 person
is there a way to ensure that the date is written in dd-Mmm-yyyy format
LikeLiked by 1 person
do you have a example of select with BETWEEN dates?
LikeLike
Hi!
Thank you so much for the helpful article.
Is there any way to choose what cell to start adding data from? I’m want to use the 3 top rows for different info, and then start adding data from the 4th row. But if i put anything into the 3 top rows, i get the “Unknown field”-error
Best regards,
Ulrik
LikeLike
Hi, thank you for your helpful article. It has helped me gain more understanding about commands. Unfortunately, I am not well versed with programming, and even worse, currently, I am the only one in our group who knows how use basic vba. Moving forward, I am tasked to make a Word Form that when filled out, the entry will also be automatically recorded in an excel spreadsheet. So I searched the net and I got this code (below), which I modified (or so I thought, but I actually don’t know what I’m doing) to suit our environment. I get an error message:
“Could not find installable ISAM”.
Before that, I got a ton of errors, some of which were:
“-2147217900: Syntax error in INSERT INTO STATEMENT”
“Run-Time error ‘3704’: Operation is not allowed when the object is closed”
“-217217843: Authentication Failed”
My system is:
Win 7
Excel 2007
Word 2007
No MS Access installed
No SQL installed
So, I’m not really sure if this code works at all. May I humbly ask for your help? If it’s ok with you? Thank you for taking time to look at this code below and helping me out:
Sub TransferToExcel()
‘Transfer a single record from the form fields to an Excel workbook.
Dim doc As Document
Dim strRequestNumber As String
Dim strRequestName As String
Dim strClientName As String
Dim strDetails As String
Dim strStatus As String
Dim strRemarks As String
Dim strVolunteerName As String
Dim strSQL As String
Dim cnn As ADODB.Connection
‘Dim strConn As String
‘Get data.
Set doc = ThisDocument
On Error GoTo ErrHandler
strRequestNumber = Chr(39) & doc.FormFields(“txtRequestNumber”).Result & Chr(39)
strRequestName = Chr(39) & doc.FormFields(“txtRequestName”).Result & Chr(39)
strClientName = Chr(39) & doc.FormFields(“txtClientName”).Result & Chr(39)
strDetails = Chr(39) & doc.FormFields(“txtDetails”).Result & Chr(39)
strStatus = Chr(39) & doc.FormFields(“txtStatus”).Result & Chr(39)
strRemarks = Chr(39) & doc.FormFields(“txtRemarks”).Result & Chr(39)
strVolunteerName = Chr(39) & doc.FormFields(“txtVolunteerName”).Result & Chr(39)
‘Define sql string used to insert each record in the destination workbook.
‘Don’t omit the $ in the sheet identifier.
strSQL = “INSERT INTO [Records$]” _
& ” (RequestNumber, RequestName, ClientName, Details, Status, Remarks, VolunteerName)” _
& ” VALUES (” ‘ & strRequestNumber & ‘”,”‘ & strRequestName ‘”,”‘ & strClientName ‘”,”‘ & strDetails ‘”,”‘ & strStatus ‘”,”‘ & strRemarks ‘”,”‘ & strVolunteerName ‘”,” & “)”
Debug.Print strSQL
‘Define connection string and open connection to destination workbook file.
Set cnn = New ADODB.Connection
With cnn
.Provider = “Microsoft.ACE.OLEDB.12.0”
.ConnectionString = “Data Source=C:\Users\abel\Desktop\Salonga Records\Salonga Records Database2.xlsx;Extended Properties=Excel 12.0 Macro; HDR=YES; IMEX=1”
cnn.Open
‘Transfer data.
.Execute strSQL
End With
Set doc = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & “: ” & Err.Description, _
vbOKOnly, “Error”
On Error GoTo 0
On Error Resume Next
cnn.Close
Set doc = Nothing
Set cnn = Nothing
End Sub
Best of Regards
LikeLike
Hi,
Problem is with a connection string value. Wrap ExtendedProperties=…. under double quotes. That is: “ExtendedProperties=….””
LikeLike
Hello Sir,
Thank you for your reply. I have updated the code to this (below) but still got the error “Could Not Find Installable ISAM”. I don’t have access or sql installed, I’m just using the vba in word and excel. I plan to make excel as my database.
Set cnn = New ADODB.Connection
With cnn
.Provider = “Microsoft.ACE.OLEDB.12.0”
.ConnectionString = “Data Source=C:\Users\abel\Desktop\Salonga Records\Salonga Records Database2.xlsx;” & _ (there is a break here, the two lines are not one anymore)
“Extended Properties=Excel 12.0 Macro; HDR=YES; IMEX=1″””
cnn.Open
‘Transfer data.
.Execute strSQL
End With
Set doc = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & “: ” & Err.Description, _
vbOKOnly, “Error”
On Error GoTo 0
On Error Resume Next
cnn.Close
Set doc = Nothing
Set cnn = Nothing
End Sub
When I run Debug mode after the error appears, the “cnn.Close” line is colored yellow. I don’t know why it is doing that. Thank you very much for your help.
Best Regards,
Abel
LikeLike
In Debug Mode, Can you extract the actual value of ConnectionString and paste it here. Also, try removing “Macro” word from the ConnectionString. If still problem doesn’t resolve then try removing both HDR and IMEX.
LikeLike