VBA Runtime Error : Clear Contents of an Excel sheet

3
votes

I have written following VBA code in Excel 2003 to clear the contents of an excel sheet; But it shows

RunTime Error 438: Object doesn't Support this property or Method

Here is my code,

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set wbk = Workbooks.Open("C:\a.xls")
wbk.Sheet1.Cells.ClearContents 'Error at this line
wbk.Save
wbk.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Could any one please let me know if wbk.Sheet1.Cells.ClearContents is not supported when excel is invisible. How can I clear content of an excel sheet when it is invisible?

1
vbaexcelexcel-2003
Try wbk.Sheets("Sheet1").Cells.ClearContents – Siddharth Rout
@SiddharthRout: Great! Its working; You can add it as an answer... Thanks – logan
That's Ok :) You can also answer your own questions :) – Siddharth Rout
@SiddharthRout in his case he is opening the workbook invisibly, is that right? :) Coz if it's fully closed then, there will not be a workbooks.open line. Correct me if I am wrong. – bonCodigo
@bonCodigo: Yup the workbook is opened in invisible mode but that is not the main issue here. :) – Siddharth Rout

1 Answers

4
votes

I have used the following and it is solved now!

wbk.Sheets("Sheet1").Cells.ClearContents