C# – Loop through Excel files and get data from them

C# and .Net were actually thought as the technology that was going to exchange VBA and make it useless. Well, it did not happen because of many reasons, mainly because plenty of companies still use macros and noone has the time to rewrite them into C# or VB.Net. Still, the legends say that anything doable with VBA can be rewritten with C# (while the other way around it is unfortunately not true).cs

The point in C# is that when it uses the Excel object, it gets access the same resources as VBA. Thus, it is not becoming faster nor slower, it is simply using the same. And still, I have decided to show how a simple VBA task, like looping through files and getting some data can be programmed in C#, using the Excel object.

Thus, first we have to add this Excel object. Like this:xl1

Just writing the “Excel” word in the search box of the references in C# will display us what we want. Then we should click “OK”. With the Excel object, we can do almost anything we can do with VBA. Thus, in my case, my idea was to put a few Excel file in the directory of the exe and to open them one by one. The first one was going to be SavedAs “Report.xlsx” and the others were going to be saved in it. I am interesting only in the data in the first five columns, starting from the first cell and ending in the last used cell of the first column. My idea is to make the code better, and probably even make some kind of interface, when I have time and I feel like it.

Pretty much this is the code:

Here is the code in GitHub.

About

A VBA Developer.

Tagged with: ,