I am adapting code writen by, Crystal, to use with Access 2010 and Excel 2010. (The code is only an extract)
Code:
'written by Crystal
'this was written for training purposes
'please share freely but do not remove these lines
'strive4peace2004@yahoo.ca
'modified April 21, 2005
I have the following code that gives an "Compile error: Variable not defined" error on the following line, with the word "xlDown" highlighted.
I am unsure how to rectify this, any help would be greatly appreciated.
Code:
mNumRows = .Range("A1").End(xlDown).Row
Code:
Private Function ImportSheet(pTable As String) As Boolean
On Error GoTo ImportSheet_error
ImportSheet = False
'Dim e As Excel.Application ' changed to the line below, as this code does not work in Access 2010
Dim xlApp As Object
Dim mFieldnames As String
Dim s As String
Dim mNumCols As Long
Dim mNumRows As Long
Dim i As Long
Dim j As Long
Dim mField As String
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
'If an instance of Excel was not open, then open it
On Error GoTo ImportSheet_error
If TypeName(xlApp) = "Nothing" Then
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.Workbooks.Open Me.ImportFile
With xlApp.Sheets(Me.SheetNumber)
'see how may rows there are
mNumRows = .Range("A1").End(xlDown).Row
'see how mamy columns there are
mNumCols = .Range("A1").End(xlToRight).Column
If mNumRows < (1 + Abs(Me.chkFirstRowFieldnames)) Then
MsgBox "You don't have any rows in " & Me.SheetName & " to import", , "Aborting import..."
GoTo ImportSheet_exit
End If
If mNumCols < (1) Then
MsgBox "You don't have any columns in " & Me.SheetName & " to import", , "Aborting import..."
GoTo ImportSheet_exit
End If