DEVelopers HUT

'---------------------------------------------------------------------------------------
' 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")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Jan-28             Initial Release
'---------------------------------------------------------------------------------------
Sub ClearXLSWrkSht(sXLSFile As String, sXLSWrkSht As String)
   Dim xlApp      As Object
   Dim xlBook     As Object
   Dim xlSheet    As Object
On Error GoTo Error_Handler
 
   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
   
   xlSheet.Cells.Select
   xlSheet.Cells.ClearContents   'Clear the contents
   
   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 "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
   Err.Number & vbCrLf & "Error Source: ClearXLSWrkSht" & vbCrLf & "Error Description: " & _
   Err.Description, vbCritical, "An Error has Occured!"
   Resume Error_Handler_Exit
End Sub