Have you ever needed to blank an Excel worksheet from an Access (or other programs as well – Word, PowerPoint, …) database? The following procedure does exactly that!

With this procedure, you can blank an entire WorkSheet or a specified Range on a WorkSheet.

'---------------------------------------------------------------------------------------
' Procedure : ClearXLSWrkSht
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Clear the specified worksheet in a given excel workbook from MS Access
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sXLSFile     Excel workbook filename with full path (ie: "C:\test.xls")
' sXLSWrkSht   Excel worksheet to be cleared (ie: "Sheet1")
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ClearXLSWrkSht("C:\test.xls", "Sheet1") 'Clear Sheet1
' ClearXLSWrkSht("C:\test.xls", "Sheet1", "D7:H23") 'Clear range D7:H23 on Sheet1
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Jan-28             Initial Release
' 2         2014-Jun-04             Added Ability to clear a specified Range rather than
'                                   the entire worksheet
'---------------------------------------------------------------------------------------
Sub ClearXLSWrkSht(sXLSFile As String, sXLSWrkSht As String, Optional sRng As String)
    On Error GoTo Error_Handler
    Dim xlApp           As Object
    Dim xlBook          As Object
    Dim xlSheet         As Object

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True    'Control whether or not Excel should be visible to
    'the user or not.
    Set xlBook = xlApp.Workbooks.Open(sXLSFile)  'Open the workbook
    Set xlSheet = xlBook.Worksheets(sXLSWrkSht)  'Worksheet we are working with

    If sRng = "" Then
'        xlSheet.Cells.Select
        xlSheet.Cells.ClearContents   'Clear the contents
    Else
'        xlSheet.Range(sRng).Select
        xlSheet.Range(sRng).ClearContents   'Clear the contents
    End If

    xlBook.Close True    'Close and save the workbook
    xlApp.Quit        'Close the instance of Excel we create

Error_Handler_Exit:
    On Error Resume Next
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ClearXLSWrkSht" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub