access 2010 getting max row in excel 2010
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:UsersmyaccountDesktopLRLVmydata.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.
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:UsersmyaccountDesktopLRLVmydata.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
.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