Get Last Row in Excel from Visio VBA

[matthew]

Hi,
when I use the following in my visio vba code after opening an excel file:
lastrow = objXLBook.sheets(mssheet).Cells(Rows.count, "A").End(xlUp).Row
I get a run time error 424.

The excel file is open and I can do other things with it, it is just that this function doesn't seem to work, I don't want to loop through thousands of entries so was hoping there was a quick way.

Can anyone advise please?

thanks
Matthew

[Surrogate]

Code Select Expand

lastrow = objXLBook.sheets(mssheet).Cells(Rows.count, "A").End(xlUp).Row
I get a run time error 424.

in which code line you get error 424?
Do you use early binding (with add reference to Excel application to VBA project) or late binding (just define excel related variable use CreateObject/GetObject) ?

[matthew]

Hi, thanks for the quick response,
before I use the 'lastrow =.............' statement I have :
Set objXLApp = CreateObject("EXCEL.APPLICATION")
Set objXLBook = objXLApp.Workbooks.Open(thisPath)   
objXLApp.Application.Visible = True
which lets me open the file
The error comes when the app gets to the 'lastrow = ................' statement
I can use a loop and find the last row but was hoping to use this statement instead
thanks
Matthew

[Surrogate]

I try your code and also get error! But this code works for me...

Code Select Expand

lastrow = objXLBook.sheets(mssheet).UsedRange.Rows.CountThis issue related with Excel side 

[matthew]

thanks you for your help, much appreciated
Matthew

[Visio Guy]

Try breaking it up so that you can see what is happening. It will make your code more flexible and easier to debug anyway:

Code Select Expand

Option Explicit

Public Sub TalkToExcel()

  Const WorksheetName As String = "Customers" '//...AKA: mssheet in your case

  '// Get the Excel application:
  Dim xlApp As Object
  Set xlApp = GetObject(, "Excel.Application")
  Debug.Print xlApp, TypeName(xlApp)
 
  '// Get the active workbook:
  Dim xlWkbk As Object
  Set xlWkbk = xlApp.ActiveWorkbook
  Debug.Print "Workbook: " & xlWkbk.Name
 
  '// Get a worksheet from the workbook:
  Dim xlWksht As Object
  'Set xlWksht = xlApp.ActiveSheet '...active worksheet
  Set xlWksht = xlWkbk.Worksheets(WorksheetName) '//...worksheet by name
  Debug.Print "Worksheet: " & xlWksht.Name
 
  '// Get the used range:
  Dim xlRng As Object
  Set xlRng = xlWksht.UsedRange
  Debug.Print VBA.TypeName(xlRng)
  Debug.Print "Rows: " & xlRng.Rows.Count
 
Cleanup:
  Set xlRng = Nothing
  Set xlWksht = Nothing
  Set xlWkbk = Nothing
  Set xlApp = Nothing
End Sub

[Croc]

The result can be obtained using several different methods.
But remember that different methods will return different results in special cases.

Code Select Expand

Sub CheckEmptyRow()
    RowCount = ActiveSheet.Cells(1, 2).CurrentRegion.Rows.Count
    RowLast = ActiveSheet.Cells(1, 2).CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
    RowUsed = ActiveSheet.UsedRange.Rows.Count
    Debug.Print "CurrentRegion.Rows.Count: " & RowCount
    Debug.Print "CurrentRegion.SpecialCells(xlCellTypeLastCell).Row: " & RowLast
    Debug.Print "ActiveSheet.UsedRange.Rows.Count: " & RowUsed
End Sub


Code Select Expand

CurrentRegion.Rows.Count: 10
CurrentRegion.SpecialCells(xlCellTypeLastCell).Row: 17
ActiveSheet.UsedRange.Rows.Count: 17

Code Select Expand

CurrentRegion.Rows.Count: 10
CurrentRegion.SpecialCells(xlCellTypeLastCell).Row: 15
ActiveSheet.UsedRange.Rows.Count: 15

[matthew]

Thank you, I'm now using the usedrange.rows.count to get the last row.

I've tried to use what I've seen here on a similar topic of finding the row number of an item in an excel column but to no avail.
I'll raise that as a separate item as it isn't exactly the same, can you help with that please?

thanks
Matthew