Access VBA End(xldown) error

[Climbo]

Hello there,

I am trying to get the last row value in a variable but always getting a define-application errror, Please help.

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim LastRow As Integer
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("J:\RC Tools\Pilots\2008 Q4 Hype\Hype Results.xls")
 
Set xlWS = xlWB.Worksheets("Report")
LastRow = xlWS.range("C5").End(xlDown).Row

 

[boblarson]

When using late binding (Dim objXL As Object) you have to supply the constants yourself. So, go into Excel and find out what value xldown returns and create a constant for your own variable. Or just supply the value instead of the constant name.

 

[DCrake]

Use the following to find the last row and/or column that has been used in an Excel worksheet

iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Where oSheet is the active sheet

The good thing about this is that is goes to the last dirty row / column even if there is no data in that particular cell.

-ABCD
1XXX
2 XXX
3X
4 X
5XX
6 X

Would return

iRow = 6
iCol = 4

So D6 is the bottom right coordinate

David

 

[boblarson]

Use the following to find the last row and/or column that has been used in an Excel worksheet



Where oSheet is the active sheet

The good thing about this is that is goes to the last dirty row / column even if there is no data in that particular cell.

-ABCD
1XXX
2 XXX
3X
4 X
5XX
6 X

Would return

iRow = 6
iCol = 4

So D6 is the bottom right coordinate

David

This still won't work unless you provide the constant values for xlCellTypeLastCell

You can't use Excel constants with late binding unless you first provide the values for them and create them as your own constants.

So you would need in the declarations section of a standard module to declare:

Const xlCellTypeLastCell As Integer = 11

so that you can then use that constant in your code. If you use early binding (where you set a reference to Excel) you don't need to do that because Access then has the values for those constants at its fingertips, but if you are using late binding (which I do suggest) then you must supply your own constants to use them.

 

[DCrake]

Bob, here is the full code I use to determine the bottom right cell in a worksheet

Function LoadExcelSpreadsheet(xlfilename)

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim tDate As String

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(1)


Dim rs As DAO.Recordset

iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

With ActiveSheet.UsedRange
iCol = .Cells(1, 1).Column + .Columns.Count - 1
iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With

iRow = 1

Forms("frmVerifyList")("ProgressBar").Max = iLastRow
Forms("frmVerifyList")("lblAction").Caption = iLastRow & " rows"
etc
etc.

As long as you have Excel nn Object library referenced it is aware of the constants.

 

[Jamesss]

As long as you have Excel nn Object library referenced it is aware of the constants.

Might seem dumb but, how do you do that?

 

[DCrake]

Go to Tools > References and press M for Microsoft then scroll down to Microsoft Excel nn Object library and tick the box then click ok

 

[Jamesss]

DCrake - I have made some modifications to the code you kindly provided here and in another thread and for the ".UsedRange" mmethod, Access spits an error indicating "Method or data member not found". I guess this is similar to the previous problem.

How do we define this method (from Excel) in Access?

Option Compare Database
Option Explicit
Dim xlfilename As String
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim iRow As Integer
Dim iCol As Integer
Dim iLastRow
Dim cValue As String
Dim cGroupValue As String
Dim cTaskCode_Row As Integer
Dim cTaskCode As String
Private Const xlCellTypeLastCell As Long = 11
Dim ActiveSheet As Property
 
Function LoadExcelSpreadsheet(xlfilename)
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(xlfilename)
Set oSheet = oBook.Worksheets(1)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("ImportTable")
iRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iCol = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
With ActiveSheet.UsedRange '>>>> ".UsedRange" error:"Method or data member not found"
    iCol = .Cells(1, 1).Column + .Columns.Count - 1
    iLastRow = .Cells(1, 1).Row + .Rows.Count - 1
End With
'iRow is the last row in the worksheet that contains data
For nRow = 1 To iRow
    cGroupValue = oSheet.Range("D" & nRow).Value ''' Column where "Task Code" resides
    If InStr(1, cGroupValue, "Task Code :") = 1 Then
        '''Extract Task Code as a string for values following colon ": "
        cTaskCode = Right(cGroupValue, Len(cGroupValue) - InStr(cGroupValue, ": "))
    End If
    
    cValue = oSheet.Range("G" & nRow).Value ''' "Initialized" data field is never empty
    ''' "Initialized" is the field heading in the MXi file
    If cValue <> "" Or cValue <> ("Initialized") Then
        rs.AddNew
        rs(TaskCode) = cTaskCode
        rs(Aircraft) = oSheet.Range("A" & nRow).Value
        rs(Rego) = oSheet.Range("B" & nRow).Value
        rs(EngineAPU_PN) = oSheet.Range("C" & nRow).Value
        rs(EngineAPU_SN) = oSheet.Range("D" & nRow).Value
        rs(Component_PN) = oSheet.Range("E" & nRow).Value
        rs(Component_SN) = oSheet.Range("F" & nRow).Value
        rs(Initialised) = oSheet.Range("G" & nRow).Value
        rs.Update
    End If
Next
rs.Close
oExcel.Quit
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set rs = Nothing
End Function

Cheers
James

 

[Jamesss]

I think I found what was wrong. I unnecessarily defined ActiveSheets as a property. Referencing the Excel Object library fixed it up.

However, I have one question about entering variable string values into the recordset.

In the statements:

If cValue <> "" Or cValue <> ("Initialized") Then
        rs.AddNew
        rs("TaskCode") = cTaskCode
        rs("Aircraft") = oSheet.Range("A" & nRow).Value
        rs("Rego") = oSheet.Range("B" & nRow).Value
        rs("EngineAPU_PN") = oSheet.Range("C" & nRow).Value
        rs("EngineAPU_SN") = oSheet.Range("D" & nRow).Value
        rs("Component_PN") = oSheet.Range("E" & nRow).Value
        rs("Component_SN") = oSheet.Range("F" & nRow).Value
        rs("Initialised") = oSheet.Range("G" & nRow).Value
        rs.Update
    End If

I wanted the variable cTaskCode to be entered as a string into the DB under the field "Task Code".

 

[boblarson]

1. If you want to use Late Binding (to avoid reference issues for people who may be on multiple versions of Excel) you need to provide your own constants.

2. If you use the Excel Reference then you don't need to provide your own constants, but you can have MISSING reference errors if you have different users using different versions of Excel.

So you have to decide which to use for your situation. I've gone to pretty much using early binding for development and late binding for production so as to avoid running into issues when the users start using the program.