excel - 通过 Access 控制 Excel 时 Range.End 1004 错误

Question

每次完成表单时，我都试图让 Access 在 Excel 工作簿的两个单元格中输入一个条目。

使用时出现 1004 错误Range().End()。

R并且C是我用来隔离函数以找出导致问题的原因的变量。

Option Compare Database

Private Sub Form_AfterUpdate()
'This macro will update the Excel WO Tracker for a project each time an NCR is closed or resolved

Dim xl As Object 'Excel
Dim tracker As Object 'Book
Dim sn As Object 'Sheet\
Dim connect(1) As Variant 'WO and NCR#

'Debugging variables
Dim R As Object
Dim C As Object

'Exit if required fields are blank
If (Title.Value = "") Or (status.Value = "") Or (Program.Value = "") Or _
  (Disposition.Value = "") Or ([Work Order Number].Value = "") Then
    Exit Sub
End If
'On Error GoTo Handler

If (status.Value = "Closed") Or (status.Value = "Resolved") Then
    Set xl = CreateObject("Excel.Application")
    xl.ScreenUpdating = False
    Select Case Program.Value
    Case "SE07"
        Set tracker = xl.Workbooks.Open("Z:\Operations\Projects\SE07\" & Year(Now()) & " SE07 WO Tracker.xlsm")
    Case "VS02"
        Set tracker = xl.Workbooks.Open("Z:\Operations\Projects\VS01\" & Year(Now()) & " VS02 WO Tracker.xlsm")
    Case Else 'Program does not have a WO Tracker
        xl.ScreenUpdating = True
        Set xl = Nothing
        Exit Sub
    End Select

    Set sn = tracker.Sheets("SN")
        'Record NCR connection
        connect(0) = [Work Order Number].Value
        connect(1) = Title.Value
        Set R = xl.Intersect(sn.Range("FirstCol"), sn.Range("NCRConnect"))

        'Error 1004 thrown here:
        Set C = R.End(xlToRight)

        xl.Intersect(sn.Range("NCRConnect"), sn.Columns(C.Column + 1)) = xl.Transpose(connect)
    End If

    tracker.Save
    tracker.Close
    Set tracker = Nothing
    Set sn = Nothing
    xl.ScreenUpdating = True
    Set xl = Nothing
End Sub

此外，此代码运行速度极慢。有没有办法加快这个速度？

标签： excelvbams-access

Answer 1

xlToRight是 Excel 对象模型的成员，即XlDirection 枚举。

如果您是后期绑定，请添加以下内容：

Const xlToRight As Long = -4161