Importing data from excel:

[Mclaren]

I am adapting code writen by, Crystal, to use with Access 2010 and Excel 2010. (The code is only an extract)

'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.

mNumRows = .Range("A1").End(xlDown).Row

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

[ssanfu]

Do you have a reference set to Microsoft Excel xx.0 Object Library? ("xx" is the version number. For me, it is 11.0 ( A2K3))

[June7]

I tested code and the xlDown worked. Can't replicate the issue. Do you want to provide files for analysis? Follow instructions at bottom of my post.

In 2010 the reference is 12.0. Excel should have automatically selected that reference when the VBA editor was opened, unless maybe the workbook was originally created with an earlier version.

[Mclaren]

Excel workbook is 2003. Extension is .xls

Will check my references now

[Mclaren]

Ok, checked my references and;

The Excel 14.0 Object Library was NOT checked. I have now checked it.

Now i get the following error:

Error 1004

Application-defined or object-defined error

[Mclaren]

I stand corrected, i had a goto to skip the problem area. so no more error. thanks guys. You helped a lot.