How to get last row using Excel DNA and C#

[ankit mishra]

Hello All,

I have a VBA code that I am moving to C#. For one of my scenario I have to identify last row number for column E , so that I can run loop for each row and read data in my C#.

Following was my working code in VBA: - 

Dim mainWorkBook As Workbook

Set mainWorkBook = ActiveWorkbook

Dim rowCountSheet As Double

rowCountSheet = mainWorkBook.Sheets("Sheet7").Range("E1048576").End(xlUp).Row

For i = 12 To 12 + rowCount

 'Read cells from each row and create a XML string

Next

My code in C#

int RowCount=0;

            dynamic Excel = ExcelDnaUtil.Application;

            try

            {

                RowCount = Excel.Worksheets["Reference"].Range("E1048576").End.Row;

                MessageBox.Show("Row count is: " + RowCount);

            }

            finally {

                MessageBox.Show("Row count is: " + RowCount);

            }    

I also tried these combination but it is not giving me desired result

Excel.Worksheets["Reference"].Range("E1048576").End(xlUp).Row   //This gives error for xlUp

Excel.Worksheets["Reference"].Range("E1048576").RowLast  // This gives 0

Please let me know how I can do so, or if you can guide me to some reference material for same.

Regards,

Ankit

[ankit mishra]

Got it working. I Referred https://groups.google.com/forum/#!searchin/exceldna/count$20row$20C$23|sort:relevance/exceldna/gxV4xGuVPFs/VwabMF6hLE8J

This is my C# code. (need to use Microsoft.Office.Interop.Excel)

            int LastRowCount=0;

            dynamic Excel = ExcelDnaUtil.Application;

            dynamic sheet2 = Excel.ActiveWorkbook.Worksheets("Sheet2");

         

            try

            {

                LastRowCount = sheet2.Cells(sheet2.Rows.Count, 5).End(XlDirection.xlUp).Row;

                                

                MessageBox.Show("Row count is: " + LastRowCount);

            }

            finally {

                MessageBox.Show("Finally " + LastRowCount);

            } 

Regards,

Ankit

[Govert van Drimmelen]

Instead of using the "dynamic" types, you can also add a reference to the COM interop assemblies (easiest is to install the ExcelDna.Interop package from NuGet). Then your code might look like this:

using System;

using MessageBox = System.Windows.Forms.MessageBox;

using Microsoft.Office.Interop.Excel;

using ExcelDna.Integration;

namespace ExcelDnaCsMacro

{

    public class Macros

    {

        [ExcelCommand(MenuName = "MacroTest", MenuText = "Get Last Row")]

        public static void GetLastRow()

        {

            Application Application = (Application)ExcelDnaUtil.Application;

            MessageBox.Show("Getting last row...");

            try

            {

                Worksheet refWs = Application.Worksheets["Reference"] as Worksheet;

                int rowCount = refWs.Range["E1048576"].End[XlDirection.xlUp].Row;

                MessageBox.Show("Row count is: " + rowCount);

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error: " + ex);

            }

        }

    }

}

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ankit mishra [ankitmi...@gmail.com]
Sent: 05 May 2017 10:58 AM
To: Excel-DNA
Subject: [ExcelDna] Re: How to get last row using Excel DNA and C#

Got it working. I Referred https://groups.google.com/forum/#!searchin/exceldna/count$20row$20C$23|sort:relevance/exceldna/gxV4xGuVPFs/VwabMF6hLE8J

This is my C# code. (need to use Microsoft.Office.Interop.Excel)

            int LastRowCount=0;

            dynamic Excel = ExcelDnaUtil.Application;

            dynamic sheet2 = Excel.ActiveWorkbook.Worksheets("Sheet2");

         

            try

            {

                LastRowCount = sheet2.Cells(sheet2.Rows.Count, 5).End(XlDirection.xlUp).Row;

                                

                MessageBox.Show("Row count is: " + LastRowCount + " and Column count is " + LastColumnCount);

            }

            finally {

                MessageBox.Show("Finally " + LastRowCount);

            } 

Regards,

Ankit

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[ankit mishra]

Thanks Govert for reply. I used your code and its working perfectly.

[Pratham Patel]

Hello Govert,

I tried your code to get the last row used, I was facing numerous errors which I couldn't figure out why. Is it possible to build a function to get the last row used in a particular column( which is taken as an input/ parameter from the user)

These were the errors that I faced :

DnaCompilation [Error] There was an error in loading the add-in LastRow (C:\Users\prpatel\source\repos\New folder\LastRow.xll):

DnaCompilation [Error] There were errors when compiling project: 

DnaCompilation [Error]     c:\Users\prpatel\AppData\Local\Temp\4h5hgjmz.0.cs(3,17) : error CS0234: The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

DnaCompilation [Error]     c:\Users\prpatel\AppData\Local\Temp\4h5hgjmz.0.cs(6,16) : error CS0234: The type or namespace name 'Interop' does not exist in the namespace 'ExcelDna' (are you missing an assembly reference?)

DnaCompilation [Error]     c:\Users\prpatel\AppData\Local\Temp\4h5hgjmz.0.cs(2,35) : error CS0234: The type or namespace name 'Forms' does not exist in the namespace 'System.Windows' (are you missing an assembly reference?)

Thank You

Pratham

[Daboho]

Good