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).
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
using System; using System.IO; using Excel = Microsoft.Office.Interop.Excel; class LooperMain { static void Main() { string strPath = Path.GetFullPath(Directory.GetCurrentDirectory()); string[] strFiles = Directory.GetFiles(strPath); Excel.Application excel = null; bool bMakeOnce = true; string strReportName = "Report.xlsx"; int intFirstLine = 1; int intLastColumn = 5; int lastRow; int lastRowReport; int intTotalRows; Excel.Workbook wkbReport = null; string strWkbReportPath; excel = new Excel.Application(); excel.Visible = true; foreach (string strFile in strFiles) { if (strFile.Contains(strReportName)) { Console.WriteLine(strReportName + " is deleted."); File.Delete(strFile); } } foreach (string strFile in strFiles) { if ((strFile.Contains(strReportName)) || !(strFile.Contains("xls"))) { continue; } Console.WriteLine(strFile); Excel.Workbook wkb = null; Excel.Worksheet sheet = null; Excel.Worksheet sheetReport = null; Excel.Range rngLastReport = null; Excel.Range rngToCopy = null; wkb = Open(excel, strFile); System.Threading.Thread.Sleep(3000); if (bMakeOnce) { bMakeOnce = false; strWkbReportPath = wkb.Path + "\\" + strReportName; wkb.SaveAs(strWkbReportPath); wkb.Close(); wkbReport = Open(excel, strWkbReportPath); } else { sheetReport = wkbReport.Worksheets[1]; sheet = wkb.Worksheets[1]; intTotalRows = sheet.Rows.Count; lastRow = sheet.Cells[intTotalRows, 1].End(Excel.XlDirection.xlUp).Row; lastRowReport = sheetReport.Cells[intTotalRows, 1].End(Excel.XlDirection.xlUp).Row; rngToCopy = sheet.Range[sheet.Cells[intFirstLine, 1], sheet.Cells[lastRow, intLastColumn]]; int size = rngToCopy.Rows.Count; rngLastReport = sheetReport.Range[sheetReport.Cells[lastRowReport + 1, 1], sheetReport.Cells[lastRowReport + 1 + size, intLastColumn]]; rngToCopy.Copy(rngLastReport); wkb.Close(false); } } wkbReport.Close(true); excel.Quit(); Console.WriteLine("Finished!"); } public static Excel.Workbook Open(Excel.Application excelInstance, string fileName, bool readOnly = false, bool editable = true, bool updateLinks = true) { Excel.Workbook book = excelInstance.Workbooks.Open( fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return book; } } |
Here is the code in GitHub.