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
– Wouter, Commented Nov 30, 2015 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
– cboden, Commented Nov 30, 2015 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
– Darren Bartrup-Cook, Commented Nov 30, 2015 at 15:41

Community · Accepted Answer · 2017-05-23 12:30:17Z

2

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.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Nov 30, 2015 at 17:27

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related