Here is a bit of code I put together rapidly.
Sub ClearXLSWrkSht(sXLSFile As String, sXLSWrkSht As String)
' sXLSFile :: Excel file with full path and extension
' sXLSWrkSht :: Worksheet name to be cleared
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
' For Early Binding you'd use these 3 lines instead of the 3 above
' Dim xlApp As Excel.Application
' Dim xlBook As Excel.Workbook
' Dim xlSheet As Excel.Worksheet
On Error GoTo Error_Handler
Set xlApp = New 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
It will clear the worksheet, preserving formatting. If you truly wish to
delete everything then you need to change the ' xlSheet.Cells.ClearContents
'Clear the contents' part of the code.
--
Hope this helps,
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
