I have an Access database that, among other things, imports a multi-tabbed Excel file into multiple tables in Access. Wanting to make sure that there are no import errors (so that no records get "dropped"), I created a UDF that opens up the Excel file, and counts the number of records on a tab and compares it to the number of records that were imported to the table (and warning messages are returned if the two numbers do not match).
I thought that I had it all working during testing, but now people are reporting errors, and I can't figure out what is going on. First, here is my UDF:
Code:
Function CountRows_Excel(myFileName As String, mySheetName As String, myColumn As String) As Long
' Counts the number of data rows in an Excel file using the specified column (excludes first row for header)
' myFileName = full file path and name of Excel file
' mySheetName = name of Excel sheet containing data you want to count
' myColumn = letter of column on the Excel to look at to use to determine the row count (i.e. "C")
Dim excelapp As Excel.Application
Dim myRange As Range
Dim myLastRow As Long
Dim myCount As Long
' Set and open Excel workbook
Set excelapp = CreateObject("excel.application")
excelapp.Workbooks.Open (myFileName)
' Count number of rows in specified column
Set myRange = excelapp.Sheets(mySheetName).Range(myColumn & ":" & myColumn)
On Error GoTo err_Chk
excelapp.Sheets(mySheetName).Activate
myCount = Excel.Application.WorksheetFunction.CountA(myRange)
On Error GoTo 0
' Ignore first row (header)
If myCount > 0 Then
CountRows_Excel = myCount - 1
Else
CountRows_Excel = 0
End If
' Close Excel workbook
excelapp.Quit
Set excelapp = Nothing
Exit Function
err_Chk:
' Return which sheet is having error, and list error description
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR ON " & mySheetName & " SHEET!"
CountRows_Excel = -1
excelapp.Quit
Set excelapp = Nothing
End Function
Here is how it is being called:
Code:
If CountRows_Excel(myExcelFileName, "Participant", "C") <> DCount("*", "qry15-PT_Export") Then ...
The myExcelFileName variable is the same one being used to import the data, and data is importing, so I know that the file name is correct. I verified the sheet and column references also. Basically, when I try this, I get the following error message:
"1004: Unable to get the CountA property of the WorksheetFunction class".
I also verified that the "Microsoft Excel 12.0 Object Library" is selected in my VB References.
Any idea of why this might be happening?
Thanks
EDIT: I just uncovered a big clue. I get this error when I try to import a ".xlsx" (Excel 2007) file. However, it works fine for ".xls" (Excel 97-2003) files. BTW, the database is an "accdb" file, as I am using Access 2007. Is there some issue with my VBA code that it will not handle "xlsx" files?