access 2010 getting max row in excel 2010

Question

I have a problem in accessing excel 2010 via MS access 2010. From access 2010, I want to get max row from my excel data. Here is my code:

Dim Xl As Excel.Application

Dim XlBook As Excel.Workbook

Dim XlSheet As Excel.Worksheet

Dim lastRow As Long, i As Integer

MySheetPath = "C:\Users\myaccount\Desktop\LRLV\mydata.xlsx"

Set Xl = CreateObject("Excel.Application")

Set XlBook = GetObject(MySheetPath)

Xl.Visible = True

XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

With XlSheet

    lastRow = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

When I have no excel opened, everything is okay. But when I have 1 or more excel which has been opened the variabel "lastRow" always give me "Type mismatch" error. Now I need to know how to fix it. Thank you so much before.

Answer 1

your problem is the unqualified reference to A1. also I'm not sure why you use GetObject when you already have an application reference

Dim Xl As Excel.Application

Dim XlBook As Excel.Workbook

Dim XlSheet As Excel.Worksheet

Dim lastRow As Long, i As Integer

MySheetPath = "C:\Users\myaccount\Desktop\LRLV\mydata.xlsx"

Set Xl = CreateObject("Excel.Application")
Xl.Visible = True
Set XlBook = XL.Workbooks.Open(MySheetPath)

XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

With XlSheet

    lastRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

Answer 2

.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) is not returning a valid range in this instance. This meas the find is unsuccessful. That is why you are getting a type mismatch.

To solve, do this:

Dim rng As Excel.Range

Set rng = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If Not rng Is Nothing Then
    lastRow = rng.Row
Else
    'ToDo - handle the error here
EndIf