"OutputToExcel" is Overwriting instead of Adding

[mca2k4]

I've got a command button named "Output2" which is SUPPOSED TO add entries onto an open Excel worksheet; however, it just overwrites everything on Row1 and downwards.

Anyone have suggestions on how to fix this? Any help would be greatly appreciated

 Private Sub Output2_Click()
On Error GoTo err_handler
Dim x As Integer, y As Integer
Dim itm As Variant
Dim sht
Dim xlApp As Excel.Application, mFilename As String
Dim i As Integer
 
 
 
   On Error Resume Next
   Set xlApp = GetObject(, "Excel.Application")
   On Error GoTo err_handler
   
   
   
    
   If TypeName(xlApp) = "Nothing" Then
      'Excel was not open
      Set xlApp = CreateObject("Excel.Application")
   End If
 

    With xlApp
        
        For Each itm In Me.lstCustInfo.ItemsSelected
            x = x + 1
                For y = 1 To Me.lstCustInfo.ColumnCount - 1
                    .Sheets(1).Cells(x, y) = Me.lstCustInfo.Column(y, itm)
                Next
        Next
    End With
    
xlApp.Visible = True
Exit Sub
err_handler:
    If Err.Number = 429 Then
        Resume Next
            Else
        MsgBox Err.Number & " " & Err.Description, vbOKOnly, "Error"
    End If
End Sub

 

[Pat Hartman]

I don't see any place in the code where it finds the last row of the current spreadsheet data. It would need to find that point in order to add rows after that.

IMHO, I wouldn't create this type of process. Either recreate the spreadsheet entirely, or eliminate the spreadsheet. The only reason to try to tack new records on to the end would be to preserve changes made to the spreadsheet outside of Access. That is a recipe for disaster (having two sources that both update the same data).

 

[mca2k4]

Would it be possible to have the command button run code to ADD LINES to the top of the spreadsheet when exporting selected listbox entries? If so, what would this code look like?