Access 2010 write to Excel 2013, application defined or object defined error 50290

[Lifeseeker]

Hi there,

I have an access application that writes data to Excel 2013. I have tested it on both Access 2010 to Excel 2010, and it worked, but not when I switch to Excel 2013.

Is there a way to have Access 2010 open Excel 2010 instead of 2013?? This is the code in Access 2010

Dim xlApp As Object
       
        Set xlApp = CreateObject("Excel.Application")
       
        xlApp.Visible = True
        xlApp.Workbooks.Open "M:\test\testAutomatic.xlsx", True, False
        lastRow = xlApp.activesheet.usedrange.rows.Count
        xlApp.worksheets("test").Activate
          
        'delete existing records in Excel
         Dim i As Long
         Dim j As Long
  
        For i = 13 To lastRow
          For j = 1 To 11
            xlApp.worksheets("test").cells(i, j).Value = ""
          Next j
        Next i

It fails on this line:

xlApp.worksheets("test").cells(i, j).Value = ""

Can someone help?

 

[AOB]

You've missed out on the object between xlApp (the Excel Application) and the worksheet - namely, the workbook :

Dim xlApp As Object    ' Excel Application
Dim xlWbk As Object    ' Excel Workbook
Dim xlSht As Object    ' Excel Worksheet
 
Set xlApp = CreateObject("Excel.Application")
 
With xlApp
 
    .Visible = True
 
    Set xlWbk = .Workbooks.Open "M:\test\testAutomatic.xlsx", True, False
 
    With xlWbk
 
        Set xlSht = .Worksheets("test")
 
        With xlSht
 
            lastRow = .UsedRange.Rows.Count
            .Activate
 
        End With
 
    End With
 
End With