Subscript out of range - Excel.Application.WorksheetFunction.CountA

Question

I'm trying to count the number of non-empty cells in an Excel spreadsheet from MS-Access VBA.

The idea is that I click a button on a form in MS-Access and the number of non-empty cells are stored as string.

The Excel workbook file is called "MattExcelFile.xls", the sheet within that workbook is called "Sheet1" and the range I want to use Excel's COUNTA function on is "C1:C500".

Here's the code I've pieced together so far:

Option Compare Database

Sub ImportDataFromRange()

Dim xlFilePath As String
Dim rowVariable As String

xlFilePath = "C:\Users\Matt\Desktop\MattExcelFile.xls"

rowVariable = Excel.Application.WorksheetFunction.CountA(Workbooks(xlFilePath).Sheets("Sheet1").Range("C1:C500"))

Debug.Print rowVariable

End Sub

Private Sub Command0_Click()

ImportDataFromRange

End Sub

And here's the error message I receive when running this code, highlighting the rowVariable = ... line:

Run-time error '9':

Subscript out of range

Answer 1

I am not sure if you set appropriate references therefore here is complete code for you with some comments inside.

Sub test()
Dim rowVariable As String '<--why string?

'1st
Dim xlFilePath
    xlFilePath ="C:\Users\Matt\Desktop\MattExcelFile.xls"

'2nd- set refernces
Dim EXL As Object
Set EXL = CreateObject("Excel.Application")

'3rd open worksheet- required
With EXL
   .Workbooks.Open xlFilePath

'4th count using WF- set appropriate reference to workbook
'be careful about sheet name- give error '9'
    Dim tmpName() As String
    tmpName = Split(xlFilePath, "\")
    rowVariable = .WorksheetFunction.CountA(.Workbooks(tmpName(UBound(tmpName))).Sheets("Arkusz1").Range("A2:A10"))
'control of value
    Debug.Print rowVariable
End With

'5th-close everything and clean
EXL.Quit
Set EXL = Nothing
End Sub