首页 文章

访问2010获得excel 2010中的最大行

提问于
浏览 次
0

我在通过MS Access 2010访问excel 2010时遇到问题 . 从访问2010开始，我想从我的Excel数据中获取最大行数 . 这是我的代码：

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

当我没有打开excel时，一切都还好 . 但是当我有一个或多个excel被打开时，变量"lastRow"总是给我"Type mismatch"错误 . 现在我需要知道如何解决它 . 非常感谢你 .

excel vba access-vba

2 回答

  • 0

    你的问题是对A1的不合格的引用 . 我也不确定为什么在已有应用程序引用时使用GetObject

    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
    回复于
  • 0

    .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 在此实例中未返回有效范围 . 这种测量结果不成功 . 这就是你得到类型不匹配的原因 .

    要解决，请执行以下操作：

    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
    回复于

相关问题