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
Next cmnt
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
    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

    'Delete Sheet1
    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
    'r = 0
    Next xlSheet
    End Sub


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.