VB.net Excel

I have been trying to convert an Excel macro to VB.net but I'm having problems with the above commands. XlUp, XlDown, XltoRight, and XltoLeft. They all work in the macro but they all throw an error in VB.net. Is there a reference I'm not importing? I have the following Imports:

Imports Microsoft.Office.Interop.Excel Imports Excel = Microsoft.Office.Interop.Excel

If it is a missing Import is there a way to find out which Import is missing by the error?

Thanks all.

It might be helpful if you could post the VBA code where you are using these items.

That would be ok except the VBA macro code is working. The VB.net is the one that isn't working.

VBA macro code (works fine):
LastRow = Range("A1").End(xlDown).Row
LastCol = Range("A1:X1").End(xlToRight).Column

VB.net code (doesn't work throws error):
LastRow = xlWs.Cells(xlWs.Rows.Count, 1).End(xlDown).Row
LastCol = xlWs.Cells(1, xlWs.Columns.Count).End(xlToRight).Column

Quote:

---

Originally Posted by JimReid

...They all work in the macro but they all throw an error in VB.net....

---

By throw an error you mean that the code breaks when it runs or it doesn't compile?

Possibly useful link:

https://www.vbforums.com/showthread....ast-Row-in-VBA

You could create your own constants that store the values used in VBA:

https://learn.microsoft.com/en-us/of...el.xldirection

see if UsedRange works for you

Code:

---

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\TestFolder\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Sheet1"), Worksheet)

            Dim ColRange = xlSt.UsedRange.Columns.Count 'count the used Columns     
            Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows

            'show UsedRanges
            MessageBox.Show(CStr(ColRange))
            MessageBox.Show(CStr(rowRange))
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

---

It doesn't compile
Message says: "xlDown is not declared, it may be inaccessible due to its protection level"
same message for xltoRight

NuGet package SpreadsheetLight makes this task easy.

Code:

---

Public Shared Function GetLastRowColumn(ByVal fileName As String, ByVal sheetName As String) As (EndRowIndex As Integer, EndColumnIndex As Integer)
        Using document As New SLDocument(fileName, sheetName)
                Return (document.GetWorksheetStatistics().EndRowIndex, document.GetWorksheetStatistics().EndColumnIndex)
        End Using
End Function

---

My issue isn't with VBA but with VB.net not being able to use xlUp or xlDown or xltoRight or xltoLeft. They all work with VBA but not with VB.net. I'm thinking that I have a reference missing or something similar but not sure what.

It doesn't compile.

I scanned our source code for xlDown and saw it used like this:

xlWorkbook.Sheets(1).Rows(intRow1 & ":" & intRow1).Insert Shift:=xlDown

rkbook.Sheets(1).Range("J1", xlWorkbook.Sheets(1).Range("J1").End(xlDown)).Columns.NumberFormat = "@"

Maybe it will point you somewhere.

Quote:

---

Originally Posted by kareninstructor

NuGet package SpreadsheetLight makes this task easy.

Code:

---

Public Shared Function GetLastRowColumn(ByVal fileName As String, ByVal sheetName As String) As (EndRowIndex As Integer, EndColumnIndex As Integer)
        Using document As New SLDocument(fileName, sheetName)
                Return (document.GetWorksheetStatistics().EndRowIndex, document.GetWorksheetStatistics().EndColumnIndex)
        End Using
End Function

---

---

Don't want to get involved with 3rd party packages. Should be able to do this with just VB.net.

use .Range rather than .Cells

Code:

---

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("E:\TestFolder\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Sheet1"), Worksheet)
            Dim LastRow As Integer, LastCol As Integer

            With xlSt
                LastCol = .Range("A1:X1").End(XlDirection.xlToRight).Column
                LastRow = .Range("A" & .Rows.Count).End(XlDirection.xlUp).Row
            End With

            MessageBox.Show(CStr(LastCol))
            MessageBox.Show(CStr(LastRow))

'Edit: need to close the Excel file

            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing


        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

---

Quote:

---

Originally Posted by JimReid

Don't want to get involved with 3rd party packages. Should be able to do this with just VB.net.

---

You can and I gave you a link in post #7 to where you can find the appropriate values, and from there you can create your own Constant variables in code.

Quote:

---

Originally Posted by OptionBase1

You can and I gave you a link in post #7 to where you can find the appropriate values, and from there you can create your own Constant variables in code.

---

Someone else gave me this and it works fine.
LastCol = .Range("A1:X1").End(XlDirection.xlToRight).Column
LastRow = .Range("A" & .Rows.Count).End(XlDirection.xlUp).Row

I must have had it coded wrong. Thanks anyway.

Good catch, I didn't catch that it is an Enum so the XLDirection prefix is necessary. :thumb:

Did you try something like this construct?

Dim range As Excel.Range = excelWorksheet.UsedRange

' Example usage of XlDirection constants
Dim lastRow As Integer = range.Cells(range.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
Dim lastColumn As Integer = range.Cells(1, range.Columns.Count).End(Excel.XlDirection.xlToLeft).Column

' Your code logic here