View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
garibaldi garibaldi is offline
external usenet poster
 
Posts: 2
Default VBS Script and ClearContents -- Code Throws An Error

Need VBS script to clear the contents for range all worksheets in workbook.
Must run as VBS script. Having issue with line

"currentWorkSheet .Range( cellRange ).ClearContents"; error generated

excelPath = "<Enter a UNC path to a workbook with 1+ worksheets"
cellRange = "A1:Z100"

WScript.Echo "Reading Data from " & excelPath
WScript.Echo "cellRange = " & cellRange

Set oExcel = CreateObject("excel.application")
oExcel.Workbooks.open( excelPath )

workSheetCount = oExcel.Worksheets.Count

WScript.Echo "We have " & workSheetCount & " worksheets"

For counter = 1 to workSheetCount
WScript.Echo "Reading data from worksheet " & counter & vbCRLF

Set currentWorkSheet = oExcel.ActiveWorkbook.Worksheets( counter )

WScript.Echo "Call to Range method next" & vbCrLf

currentWorkSheet .Range( cellRange ).ClearContents ' <-- Fails

Set currentWorkSheet = Nothing
Next

oExcel.Workbooks(1).Close
oExcel.Quit

Set currentWorkSheet = Nothing
Set oExcel = Nothing