From the Create New Data Source dialog box, for a new OLE-DB connection, select Create a new data source (using OLE-DB), and then click Next. In the Data Provider field, select Microsoft Excel, and then click Next. Click the Browse button, and then locate and select the database file. The ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows: Visual Basic Standard EXE Project Files. Cannot initialize the data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)'. So close the file and try the query again. OLE DB Driver Not Installed The OPENROWSET function uses OLE DB, so it needs a driver for your data source, in this case for Excel. As I told you earlier, there is no way to open password protected Excel files and create Excel queries using OleDb. You can use COM components to achieve similar query functions, but it is not as good as the SQL statement query in OleDb. So if you want to use a password, it is recommended that you use a database to accomplish this task. Contact Us for RPA Tool Project Support Email: [email protected] can be used and re-used in a wide variety of automated tasks. These bots allow automatio.
Why use Excel as a Database?
Most developers know how to use back ends like Microsoft SQL server, Oracle, Microsoft Access or IBM DB2 as database back ends for their applications. Microsoft Excel in the other hand is able to perform many of the services these back ends provide.
The unique things about Microsoft Excel if contrasted with these back ends is it's simplicity which directly results in zero DDL effort along with many other advantages. Microsoft Excel is increasingly used as a database replacement as you can have your tables designed and created in zero time and you can start immediately enter data into them, a feature that is hard to obtain in all of the back ends mentioned above. We observe an increasing demand from several application producers (specially small and medium applications) to have Microsoft Excel as their database back end. This way they will have all the analysis / data storage / calculations / formatting free from excel and in the same time they can access every thing in a form of a database table from the applications they build so that they have everything with an affordable cost. This is all not to mention that the availability of Microsoft Excel is much higher if compared to any other database back end. This tutorial will teach you how to use Microsoft Excel table sheets from .NET applications as if they are database tables residing on a database server for example.
Using MS Excel with ADO.NET
.NET Framework supports using Excel as a backend database for .Net applications. You can accomplish this task by using ADO.NET the same way you use it when you need to manipulate other types of backend databases. With ADO.Net you can retrieve data from a Microsoft Excel workbook, modify or add data in an existing workbook, or create a new workbook and work sheets.
In the following sections of our tutorial we will explain - step by step - how to manipulate Excel sheets by giving a real working example.
Different SQL Query with MS Excel
Using ADO.NET with Excel sheets is somehow differing from using it with the other database types in the following issues:
Automatic Code Generation: You have to do the entire job yourself, no automatic data adapters, commands, and datasets generation. You will write all the code yourself.
Connection String: The connection string will have the following syntax:
The 'HDR' parameter tells if columns in the accessed worksheet have headers or not. Yes for have, No for have not.
Data Types: There is no direct way to specify the data types for columns in Excel tables. The OLE DB scans a number of columns (you can set it ) to guess the data type for the field.
Table Names: To reference a table in an Excel workbook you have to use one of the following syntaxes:
Oledb Excel Imex
Note that the dollar sign is an indication that the sheet exists. If you are creating a new sheet or table, do not use it.
Manipulate Excel Workbook With .NET Application
Create new Excel Workbook and Datasheet
To create a new Excel datasheet use the 'Create Table' SQL command. If the workbook specified in the connection string dose not exists, it will be created too. If it exists then the command will create a datasheet with the specified name.
Open up your MS Visual Studio 2005 and create a new VB.NET application. Add some button controls to 'Form1' design surface as shown in the following figure.
In the most beginning of 'Form1.vb' code file import the following namespace.
In this example we will create a separate function to build the required connection string as follows:
This function takes a string represents an Excel workbook file path name like 'd:yBook1.xls' as the data source and returns the connection string that represents that data source.
Now double click the'Create New Datasheet' button to activate its click event handler. Write the following lines of code.
As you see in the above code, to create a new workbook or datasheet we have to define an OleDB connection object and an OleDB command object. Then we set the connection string by using the 'CreateConnString' described in the previous section. Set the command connection property to the used connection object. Set the command text property to the SQL create table command as shown in the above code. This command will create a new workbook named 'Book1.xls' and a new worksheet named 'MySheet' that have two columns 'F1' and 'F2' both of the same data type which is 'Char(255)'.
Press F5 to run the application, and click the 'Create New Data Sheet' button. Now browse to the 'D:K' folder and find the new created 'Book1.xls'.
Insert New Records
To insert new records into an existing data sheet use the 'INSERT INTO' SQL command. In our application double click the 'Insert New Record' button to activate its click event handler. Write the following lines of code:
Run the application and click the 'Insert New Record' button. Browse to the 'D:kBook1.xls' and open it. You will find the following:
Figure 3 - New Record is inserted into the MySheet
The record is added to 'MySheet'.
Retrieve Existing Records
To retrieve existing records in an existing excel workbook use one of the two famous ADO.NET techniques. OleDB Data Reader, or OleDB Data Adapters and Data Sets. We will use the first technique in our program sample with a pre created Excel workbook named 'MyBook.xls' as shown in the following figure.
Double click the 'Retrieve Records' button to activate its click event handler. Type the following lines of code:
In the above code we retrieve all the records in the 'Products' datasheet located in the 'MyBook.xls' workbook. Run the application, click the 'Retrieve Record' button, and note the result.
You can use Data Adapter and Data set objects to accomplish the same task, but you need to build all the commands needed in the Data Adapter Fill and Update operations yourself.
Update Existing Record
To update an existing record we will make use of the 'Update ' SQL command. Double click the 'Update Existing Record' button to activate its event handler and type the following lines of code:
In the above code we locate the record where 'Prod = 'Prod1' and change its name to 'ABC' in the 'Products' worksheet of the 'MyBooks.xls' workbook. Run the application and click the 'Update Existing Record' button. Browse to 'MyBook.xls' and open it. You will find the following:
The First record was updated.
Limitations of Using Excel as a Database System
The following is some limitations when using ADO.NET with Excel:
- You can not delete a record or an entire datasheet using ADO.NET.
- You can not insert formulas in cells using ADO.NET.
Excel Oledb Connection String
To download the complete working example, just click here.
For further information
Microsoft Oledb Provider For Excel
Refer to the online copy of Microsoft Developers Network at http://msdn.microsoft.com or use your own local copy of MSDN.