Using VB .Net to parse an excel file


I am current writting a VB .Net app that needs to do the following:

open an existing excel workbook and select a worksheet
search the cells in one column for a string
continue searching the cells in the same column for another string
copy the cells between both strings and push them into a db

I've having difficulty finguring out how to search the cells into order to create the range object, w/o creating the range object to begin wth.

I haven't worked with the Excel object model before so any assistance would be appreciated
Who is Participating?
mmarinovConnect With a Mentor Commented:
it is not needed to use Excel object - you can work with excel through oledb provider as a database then you can use for example oledbdataadapter.fill(dataset1) and you can going though columns and rows of the tables in filled dataset

this code will do ... all you have to do is change sheet name and the function'll return DataSet which you can easily manage

private DataSet TranslatExcelToDataset(string filePath)
      string ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"{0}\"; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"", filePath);
      OleDbConnection Connection = new OleDbConnection(ConnectionString);
      OleDbDataAdapter Adapter = new OleDbDataAdapter();
      OleDbCommand Command = new OleDbCommand("SELECT * FROM [Sheet1$]", Connection);
      DataSet DS = new DataSet("Table");
      Adapter.SelectCommand = Command;
      return DS;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.