Delete all rows which have data from linked Excel workbook

[patriciaxxx]

I have the following code which copies the recordset from an Access query to a linked Excel workbook, and it works.

I need to modify it so it first deletes any/all rows which have data in them from the worksheet (but not the first row which is the header).

Any help will be much appreciated.

Private Sub CopyRecordsetToExcel(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = CurrentProject.Path & "\LinkedTables.xls"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("Sheet2")
    xlwsSheet.Activate
    'Getting the first cell to input the data.
    xlwsSheet.Cells.Select '.SpecialCells(xlCellTypeLastCell).Select
    y = xlApp.ActiveCell.Column - 1
    xlApp.ActiveCell.Offset(1, -y).Select
    x = xlwsSheet.Application.ActiveCell.Cells(2, 1).Address '.Cells.Address
    'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
    rs.CursorLocation = adUseClient
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.Open SQL, con
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        x = "A2" 'Replace(x, "$", "")
        y = Mid(x, 2)
        Set rng = xlwsSheet.Range(x)
        xlwsSheet.Range(x).CopyFromRecordset rs
    End If
    xlwbBook.Close True
    xlApp.Quit
    Set xlwsSheet = Nothing
    Set xlwbBook = Nothing
    Set xlApp = Nothing

End Sub

 

[vbajock]

Sub clearExcelContent()

Rows("2:65536").Select
Selection.Clear
Range("A2").Select

End Sub

 

[SkipVought]

Private Sub CopyRecordsetToExcel(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = CurrentProject.Path & "\LinkedTables.xls"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("Sheet2")
[b]
    With xlwsSheet
       .Range(.Cells(2,3), .Cells(.rows.count,1)).entirerow.delete
    End With[/b]
    'Getting the first cell to input the data [highlight]which is row 2, since all the data has been deleted[/highlight]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[patriciaxxx]

Thank you vbajock and SkipVought.

I was just testing something along those lines, good to know I was on the right track.

Problem solved.

Thanks again.