Sunday, March 02, 2008

Delete Comments from Excel Workbook using VBA

Remove Comments Programmatically using Visual Basic Applications (VBA)

Most of the times comments are used for internal purpose. This need not go with the workbbok, here is the way to remove it

The following code uses RemoveDocumentInformation. It removes all information of the specified type from the workbook. It is compatible with Excel 2007

Sub Remove_Comments_From_WKBK()
'
' Remove Comments from Excel 2007 Workbook
'

'
ActiveWorkbook.RemoveDocumentInformation (xlRDIComments)
End Sub

If you want the same for Excel 2003 and before here is the code

Sub Remove_Comments_From_WKBK_2003()
'
' Remove Comments from Excel 2003 Workbook
'

'
Dim wks As Worksheet
Dim cmnt As Comment

For Each wks In ActiveWorkbook.Sheets
For Each cmnt In wks.Comments
cmnt.Delete
Next cmnt
Next
End Sub



1 comment:

  1. Anonymous4:06 AM

    Sub ShowFolderList()
    Dim fs, f, f1, fc, s, fr, r, D, WSN
    Dim folderspec
    Dim I As Integer

    'Delete all the worksheets
    Workbooks.Add
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
    If Worksheets(I).Name <> "Sheet1" Then Worksheets(I).Delete
    Next I

    'Insert worksheet with folder name
    folderspec = "D:\Dinakaran" 'CurDir()
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.SubFolders
    For Each f1 In fc
    With ActiveWorkbook.Sheets.Add
    ActiveSheet.Name = f1.Name
    End With
    Next

    'Delete Sheet1
    ActiveWorkbook.Sheets("Sheet1").Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    'List all the files
    For Each xlSheet In ActiveWorkbook.Worksheets
    WSN = xlSheet.Name
    D = "D:\Dinakaran\" & WSN & "\"
    xlSheet.Cells(1, 1) = "Filenames"
    r = 2
    fr = Dir(D, 7)
    Do While fr <> ""
    xlSheet.Cells(r, 1) = fr
    r = r + 1
    fr = Dir
    Loop
    'r = 0
    Next xlSheet
    End Sub

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group