Powerpoint VBA: How to get last used column in a row from an Excel sheet

Question

Q: How do I get the last used column in a specific row from an Excel sheet.
While this is a simple task using Excel VBA its more difficult using Powerpoint VBA.

Here is an example of my Excel sheet

Steps to reproduce

• create & save a new Excel sheet like my example above
• open a new PowerPoint presentation & hit ALT+F11 for the VBA editor
• insert the code below and customize the path to your test Excel file in line 3
• execute the code line per line using F8

Sub findlastcolumn()

    Set objExcel = CreateObject("Excel.application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\<USERNAME>\Desktop\data.xls")

    objExcel.Visible = True

    'works in Powerpoint VBA but delivers the wrong column
    lastcol = objWorkbook.Sheets(1).UsedRange.Columns.Count

    'produces an error in Powerpoint VBA
    'but works in Excel VBA, correct column in Excel VBA
    lastcol = objWorkbook.Sheets(1).Cells(1, 254).End(Direction:=xlToLeft).Column

    'produces an error in Powerpoint VBA
    'but works in Excel VBA, and wrong column in Excel VBA too
    lastcol = objWorkbook.Sheets(1).Cells.SpecialCells(xlLastCell).Column

    'wrong column. Powerpoint VBA' find method differs from Excel' find method
    'searchdirection isn't available in Powerpoint VBA
    lastcol = objWorkbook.Sheets(1).Rows(1).Find(what:="*", _ 
        after:=objWorkbook.Sheets(1).Cells(1, 1), searchdirection:=xlPrevious).Column

    objExcel.Quit
    Set objWorkbook = Nothing
    Set objExcel = Nothing

End Sub

Desired result: I want to get lastcol = 3

The comments in my code show you what I have tried so far and what error they produce.
I'm thankful for any advice.

^{There is no PowerPoint 2003 tag? O.o}

Answer 1

The Excel specific code you posted should actually work and give you the same result as if you execute it in Excel.

The reason you do get error message is most likely that you did not in include the Excel reference in your Powerpoint VBA! You do use Late Binding, so it's generally not necessary to reference this library. However, the VBA compiler does not know the internal values of the Excel constants/enums xlToLeft, xlLastCell and xlPrevious and therefore produces the error!

Two options to solve your issue:

1. Include the Microsoft Excel library in your references
2. Instead of using the Excel specific conmstants/enums, just use their underlying numerical values. To do so, go to the Excel Visual Basic editor. In the Immediate window (Ctrl-G), type ? xlToLeft - and you'll get the result (in this example -4159). Then replace the name in your Powerpoint VBA with the number and it should work. (For better code readability, I usually leave the name of the constant in a comment in the same line, e.g.

   lastcol = objWorkbook.Sheets(1).Cells.SpecialCells(11).Column 'xlLastCell = 11
   

Answer 2

@nixada, You can do it from POWERPOINT vba:

You need to get to the workbook object: (assuming you have the excel data in slide #1, on chart #1:

Set cht = ActivePresentation.Slides(1).Shapes(1).Chart
Set DataWorkBook = cht.ChartData.Workbook

Then you should get your last blank column using the blank type:

LastCol = DataWorkBook.Sheets(1).Cells.SpecialCells(4).Column -1 'xlCellTypeBlanks = 4

That should give you the desired answer "3"

Note: for the list of types you can refer to XlCellType enumeration (Excel)

EDIT: Using xlCellTypeLastCell ( = 11: The last cell in the used range) will not necessary give you the desired answer as if a cell was used but its content now deleted, it will be considered as used cell in the range.

Answer 3

Here is what you can do. * proviso: untested code. If this doesn't work, tell me in the comments *

Dim r as Range
Dim col = 0
set r = objWorkbook.Sheets(1).Range("A1")
while(** test that r has something in it **)
  col = col + 1
  set r = r.offset(0, 1)
end

I don't have the setup handy to figure out what the test should be - but that ought to be easy. The point is that this doesn't use constants the Powerpoint VBA doesn't know (like xlDown).

Answer 4

I just want to post the corrected code if anyone stumbles over a similar issue. (Thanks to Peter Albert)

Sub findlastcolumn()

    Set objExcel = CreateObject("Excel.application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\<USERNAME>\Desktop\input data.xls")        
    objExcel.Visible = True
    icolumn = 1 'which column should be checked (in sheet 1) 

    'works
    Set r = objWorkbook.Sheets(1).Cells(icolumn, objWorkbook.Sheets(1).Columns.Count)
    Do: Set r = r.offset(0, -1)
    Loop While r.Value = ""
    lastcol1 = r.Column

    'works
    lastcol2 = objWorkbook.Sheets(1).Cells(icolumn, _ 
       objWorkbook.Sheets(1).Columns.Count).End(Direction:=-4159).Column

    'works
    lastcol3 = objWorkbook.Sheets(1).Rows(icolumn).Find(what:="*", _
        after:=objWorkbook.Sheets(1).Cells(1, 1), searchdirection:=2).Column

    'works - but not if your columns have different last cells
    lastcol4 = objWorkbook.Sheets(1).UsedRange.Columns.Count

    'works - but not if your columns have different last cells
    lastcol5 = objWorkbook.Sheets(1).Cells.SpecialCells(11).Column

End Sub