I will get an Excel 2003 file everyday via email. This file has 5 columns. The first for columns have static headers and text in them. The 5th column is the date of the report and below that is a list of values in the same column, followed by an empty row, and then the sum of all the values in the column.
I would like to clean it up in the following manner:
Directly import the first 4 columns, without changing it. The date, which is the header of the 5th column, should be extracted and should form the entire 5th column in my table in the database. The values in the 5th column should form the 6th column in the database and the sum of the values should be ignored. Also, the number of rows is variable and so how to make the code relative? This is a file to which I will not have direct access to, nor can the format be changed.
How can I achieve this using VB.Net? I read up a few articles and they all said that using VB is better than C# for this purpose. I'm a complete noob to Excel automation and really need some help. Thanks!