CODEWITHSUNDEEP
vbaexceloutlook
z1lent
z1lent

Reputation: 147

Outlook VBA find last Row in Excel Worksheet

I'm writing a function in Outlook VBA that involves reading content from an excel workbook.

The part I'm struggling with is finding the last row in a column (column A in this example). While the 1st line in the highlighted block correctly displays the content of A1 cell in given worksheet, the second line gives a Error "424" - object required.

Any suggestions into the problem would be greatly appreciated.

Public Function openExcel()



    Dim xlApp As Object
    Dim sourceWorkBook
    Dim sourceWorkSheet


    Dim cellVal As String
    Dim lastRow As Long


    Set xlApp = CreateObject("Excel.Application")

    With xlApp
        .Visible = True
        .EnableEvents = True
    End With

    Set sourceWorkBook = xlApp.Workbooks.Open("C:\SAMPLEPATH\Template.xlsx")
    Set sourceWorkSheet = sourceWorkBook.Worksheets("Sheet1")
    sourceWorkBook.Activate


    With Activesheet

        cellVal = sourceWorkSheet.Cells(1, 1)
        lastRow = sourceWorkSheet.Cells(.Rows.Count, "A").End(xlUp).Row

    End With



    sourceWorkBook.Save
    sourceWorkBook.Close
    xlApp.Quit



End Function

Upvotes: 1

Views: 1336

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If you want to have the ability to use Excel constants within your code, you will need to either

a) Include a reference to a Microsoft Excel Object Library, or

b) Create your own constant, e.g.

End(-4162)

or

Const xlUp As Long = -4162
...
... End(xlUp)

Upvotes: 3

Related Questions