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

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.

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).Colu mn

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

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.

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).Colu mn

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.

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?

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

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).Colu mn
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

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

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.