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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.