Find last cell in embedded Excel sheet with Powerpoint VBA macro

In a Powerpoint document, I have an embedded Excel Worksheet.

I have to perform a check on each cell in the sheet with a Powerpoint Macro.

With the attached code, how can a perform the equivalent of...
         "ActiveSheet.SpecialCells(xlLastCell).Select"
which I use in an Excel Macro to know how many lines and how many columns to select.

Thanks





Dim oPPTFile As Presentation
Dim oPPTShape As Object
Dim xlsheet As Worksheet
Dim xlcell As Cell
Dim LRow, LCol As Integer

    Set oPPTFile = ActivePresentation
    oPPTFile.Slides(1).Select
    
    Set oPPTShape = oPPTFile.Slides(1).Shapes("Object 17")
    Dim oxl As Excel.Workbook
    Set oxl = oPPTShape.OLEFormat.Object
    Set xlsheet = oxl.Worksheets(1)
    
    ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
    
    oPPTFile.Slides(33).Select
    Set xlsheet = oxl.Worksheets(1)
    
    'this next line is the one I cannot figure out how to code in Powerpoint VBA
    ActiveSheet.SpecialCells(xlLastCell).Select
    LRow = ActiveCell.Row
    LCol = ActiveCell.Column
    
    For i = 1 To LRow
        For j = 1 To LCol
            'perform action
        Next
    Next
    
    Set xlsheet = Nothing
    Set oxl = Nothing

Open in new window

theomyshAsked:
Who is Participating?
 
Ken ButtersConnect With a Mentor Commented:
Try something like this...
Sub test()

Dim oPPTFile As Presentation
Dim oPPTShape As Object
Dim xlsheet As Worksheet
Dim xlcell As Cell
Dim LRow, LCol As Integer

    Set oPPTFile = ActivePresentation
    oPPTFile.Slides(1).Select
    
    Set oPPTShape = oPPTFile.Slides(1).Shapes("Object 17")
    Dim oxl As Excel.Workbook
    Set oxl = oPPTShape.OLEFormat.Object
    Set xlsheet = oxl.Worksheets(1)
    
    ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
    
    oPPTFile.Slides(33).Select
    Set xlsheet = oxl.Worksheets(1)
    
    'this next line is the one I cannot figure out how to code in Powerpoint VBA
    'ActiveSheet.SpecialCells(xlLastCell).Select
    
    LastCell(xlsheet).Select
    
    LRow = ActiveCell.Row
    LCol = ActiveCell.Column
    
    For i = 1 To LRow
        For j = 1 To LCol
            'perform action
        Next
    Next
    
    Set xlsheet = Nothing
    Set oxl = Nothing
End Sub
Function LastCell(ws As Worksheet) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
  
  Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

Open in new window

0
 
theomyshAuthor Commented:
Excellent!

Just one question...

How does searching for "*" return the last line or last column number?
0
 
Ken ButtersCommented:
It is a wildcard.... basically start at the bottom and search upwards until you find something.

This function will return the cell in the lower right hand corner of your spreadsheet... since the range returned is a cell... you can use it to determine either the last row or the last column used... or in your case the last cell.

Normally I use it like this:

rownum = LastCell("Sheet1").row

0
All Courses

From novice to tech pro — start learning today.