Getting the number of rows in a table with Access VBA without using an Excel Reference

Question

I am trying to edit an Excel file from MS Access without using Excel as a reference. I found an answer in this question that helps with this problem. Instead of using references to Excel, I use Objects. This seems to work for the most part, but I don't get all the functions I need from Excel in order to count the rows on a table. A snippet of my code is as follows:

    Dim xlBook As Object
    Dim xlApp As Object
    Dim xlSht As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Some\Location.xlsx")
    Set xlSht = xlBook.Sheets("SomeSheet")

    tableRows = xlSht.Cells(xlSht.Rows.Count, 2).End(xlUp).Row

I get an error on the tableRows = xlSht... line which reads:

Run-time error '1004':

Application-defined or object-defined error

I've tried numerous different ways of fixing this problem, such as rewriting the line, changing the sheetnames. I can't seem to find any documentation on this scenario. I'd appreciate any help.

If it's on your activesheet try this: `activesheet.cells(activesheet.rows.count, 2).end(xlup).row` I've had the same problem more then once, referencing not to the variables you've set, but directly to the object fixed it for me Otherwise try something as `activeworkbook.sheet("Somesheet").cells(activeworkbook.sheet("Somesheet".rows.count, 2).end(xlup).row` I know it's not pretty, but it works. At least it did for me — Wouter, Nov 30 '15 at 13:45
I agree with HansUp ... xlUp is a constant defined in Excel. If you don't have a reference to the object library and work with so called late binding, you have to declare/replace this constants by yourself. PS: as very often ... OPTION EXPLICIT on top of the module would give you a clear indication for the problem. — cboden, Nov 30 '15 at 14:13
As cboden said - you'll have to replace your constants. The easiest way I've found to do this is open Excel and, for example, type `?xlUp` into the VBE Immediate window - this will return the number you need. — Darren Bartrup-Cook, Nov 30 '15 at 15:41

score 2 · Accepted Answer · edited May 23 '17 at 12:30

Without a reference to the Excel type library, the Excel named constant xlUp is unknown to Access VBA.

You could avoid the error supplying the constant's value, -4162, instead of the constant's name. Or define the constant in your VBA code ...

Const xlUp As Long = -4162

As @cboden suggested, include Option Explicit in the Declarations section of your code module. Then when you run Debug->Compile from the VB Editor's main menu, it will alert you about any similar issues with unrecognized names.

Getting the number of rows in a table with Access VBA without using an Excel Reference

1 Answers1