CODEWITHSUNDEEP
excelms-accessvba
Alex Weber
Alex Weber

Reputation: 448

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

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.

Upvotes: 1

Views: 1775

Answers (1)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 2

Related Questions