I want to query an excel sheet and treat it as a database. So far, I have been able to connect and query it just fine. However, there seems to be a problem that is hindering my progress - I need to iterate through the excel sheet and add all the numerical values for specific columns. So I wrote code to do this. It works for some rows, but for some rows it can't see any data (when there are actually numbers in this column).
So I experimented, and determined that the columns that the ADO recordset is able to capture have data at the very top rows, while the columns that appear to be 100% blank (they're not actually) don't. I added a number at the top for these columns manually, and it solved the problem. I however, need this to be an automatic process, so I wrote some code to place a random number at the top row in the fields that are blank - it doesen't work as the recordset shows that it is the only populated field. Further, the size of the excel sheet doubles - which I think is associated to the problem.
Does anyone know how to resolve this problem? Better yet, does anyone know of a way to automatically export the excel data into MS Access? Thanks!
Here is my code that I use to iterate through the database: