asked on

Getting Excel Last Row via VBA with no reference

I want to find the Last Row and Last Column of an Excel Spreadsheet via VBA in Access without using the MS Excel 16.0 Reference
This is what I am using the get the Last Row and Last Column with the reference.

lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, "A").End(xlUp).Row
                lLastColumn = oExcelWrkSht.Cells(iCols, oExcelWrkSht.Columns.Count).End(xlToLeft).Column

Open in new window

Normally this wouldn't be an issue but I have users that don't have Excel 2016 loaded on their system.
I have no idea why it is not using the Excel 2016 or 2013 in most of the users case reference. But it is not.

Thanks john

Joe Overman

You can try this:
lLastRow = ActiveCell.SpecialCells(xlLastCell).Row
lLastColumn = ActiveCell.SpecialCells(xlLastCell).Column

John Sheehy

ASKER

That throws a Error 438,

Anders Ebro (Microsoft MVP)

If it is a matter of NO VERSION of excel not being installed, then you cannot automate the application as you are doing there. You are then forced to use something like Docmd.Transferspreadsheet to get the information. I belive this will work even if excel is not installed, but I have not tried it.

If it is a matter of the user having different versions of excel, then you will need to use LATE BINDING instead.

ASKER CERTIFIED SOLUTION

Joe Overman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Sheehy

ASKER

Here is what I have for binding

'#Const EarlyBind = True    'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        Dim oWSHShell
        Dim oExcel            As Excel.Application
        Dim oExcelWrkBk       As Excel.Workbook
        Dim oExcelWrkSht      As Excel.WorkSheet
    #Else
        'Late Binding Declaration/Constants
        Dim oWSHShell
        Dim oExcel            As Object
        Dim oExcelWrkBk       As Object
        Dim oExcelWrkSht      As Object
        Const xlCenter = -4108
    #End If
    Dim bExcelOpened          As Boolean
    Dim iCols                 As Integer
    Dim lWrkBk                As Long

Open in new window

I believe I am using Late Binding.

John Sheehy

ASKER

So once again I have two solutions that worked. Joe, your solution works for both early and late binding and Anders I turned EarlyBind to True and the users were able to use it.

Thank you both for the quick response.

John Sheehy

ASKER

Quick and reliable. Amazing today

Anders Ebro (Microsoft MVP)

Your welcome. Just remember to define the constants used (xlLastCell), or it will fail when the reference to excel is removed.

John Sheehy

ASKER

Anders,

When you say define the constants do you mean do a DIM xlLastCell as Long or Const xlLastCell = ???

John Sheehy

ASKER

So as it turn out I can use this:
lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, "A").End(xlUp).Row
lLastColumn = oExcelWrkSht.Cells(iCols, oExcelWrkSht.Columns.Count).End(xlToLeft).Column

As long as I set:
Const xlUP = - 4162 and
Const xlToLeft = -4159