Convert the VBA as Addin in the word

Hi Team,

Thank a lot for al the help that your team is doing . I would really appreciate all the help and its worth taking the membership.

I require quick assistance on the below code.  I have the standardised VBA attached below and I would like to make this VBA code in to the display as add in Icon in the Ribbon tool. As of now its not happening. Please help.

Sample image attachedCapture.PNG
Option Explicit

' Global constants
Const strPassword = "password"
Const strFolder = "C:\Cleanup1"

' Global variables
Dim objWord As Object
Dim objExcel As Object
Dim objCommentSheet As Object
Dim intRow As Long

Sub CopyCommentsToExcel()

    ' Constants
    Const xlOpenXMLWorkbook = 51
    Const xlTop = -4160
    
    ' Local variables
    Dim objFso As Object
    Dim objShell As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim objCommentWorkbook As Object
   
    ' Create general use objects
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("Shell.Application")
    Set objWord = Application

    objWord.DisplayAlerts = 0

    ' Load Excel, show it
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True

    ' Create Excel workbook for comments listing, add header row
    Set objCommentWorkbook = objExcel.Workbooks.Add
    Set objCommentSheet = objCommentWorkbook.Sheets(1)
    intRow = 0
    AddComment "File", "Person", "Date", "Comment", "Error"

    ' Access the folder to process
    Set objFolder = objFso.GetFolder(strFolder)

    ' Process all files in folder
    For Each objFile In objFolder.Files

        ' Only process Word and Excel files
        Select Case Left(LCase(objFso.GetExtensionName(objFile.Path)), 3)
            Case "xls"
                DoExcel objFile.Path, objFile.Name
            Case "doc"
                DoWord objFile.Path, objFile.Name
        End Select

    Next

    ' Resize columns
    With objCommentSheet.Cells
        .VerticalAlignment = xlTop
        .EntireColumn.AutoFit
        .EntireRow.AutoFit
    End With

    ' Enable alerts
    objExcel.DisplayAlerts = True
    objWord.DisplayAlerts = -1

End Sub

Sub DoExcel(strPath As String, strName As String)
    ' Local variables
    Dim objWorkbook As Object
    Dim intCount As Long
    Dim i As Long
    Dim objSheet As Object
    Dim objComment As Object

    ' Try to open the file, report and exit if errors
    On Error Resume Next
    Set objWorkbook = objExcel.Workbooks.Open(strPath, 0, True, , strPassword)
    If Err.Number > 0 Then
        AddComment strName, "", "", "", "Error """ & Err.Number & " - " & Err.Description & """ opening workbook."
        On Error GoTo 0
        Exit Sub
    End If

    On Error GoTo 0

    ' Extract any comments and report them
    intCount = 0
    For Each objSheet In objWorkbook.Worksheets
        For Each objComment In objSheet.Comments
            AddComment strName, objComment.Author, "", objComment.Text, ""
            intCount = intCount + 1
        Next
    Next

    ' If no comments found report that
    If intCount = 0 Then
        AddComment strName, "", "", "", "No comments found."
    End If

    ' Close file
    objWorkbook.Close (False)
End Sub

Sub DoWord(strPath As String, strName As String)
    ' Local variables
    Dim objDoc As Object
    Dim intCount As Long
    Dim i As Long

    ' Try to open the file, report and exit if errors
    On Error Resume Next
    Set objDoc = objWord.Documents.Open(strPath, False, True, , strPassword)
    If Err.Number > 0 Then
        AddComment strName, "", "", "", "Error """ & Err.Number & " - " & Err.Description & """ opening document."
        On Error GoTo 0
        objWord.Visible = False
        Exit Sub
    End If

    On Error GoTo 0

    ' Extract any comments and report them
    intCount = 0
    For i = 1 To objDoc.Comments.Count
        With objDoc.Comments(i)
            AddComment strName, .Initial, FormatDateTime(.Date, 2), .Range, ""
            intCount = intCount + 1
        End With
    Next

    ' If no comments found report that
    If intCount = 0 Then
        AddComment strName, "", "", "", "No comments found."
    End If

    ' Close file
    objDoc.Close (False)
End Sub

Sub AddComment(strName As String, strInitial As String, strDate As String, strComment As String, strError As String)
    ' Move to next row of report and add an entry
    intRow = intRow + 1
    objCommentSheet.Cells(intRow, 1).Value = strName
    objCommentSheet.Cells(intRow, 2).Value = strInitial
    objCommentSheet.Cells(intRow, 3).Value = strDate
    objCommentSheet.Cells(intRow, 4).Value = strComment
    objCommentSheet.Cells(intRow, 5).Value = strError
End Sub

Open in new window

Veena RajuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
What do you mean? Add a button in the Ribbon or amend the code to work for Word.

Can you check the last question that I helped you with.
0
Roy CoxGroup Finance ManagerCommented:
If you want to add a button the the Ribbon then you need to work with the actual document containing the code. See this

Do you want to distribute the addin to other users?

You will probaly need to use the CustomUI Editor to do this. See this

Which Office program are you using?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
This article should get you started:
https://bettersolutions.com/word/WCA723/NI825618332.htm

Basically, make your word document with whatever functionalities you want (VBA, ribbon, ect ect ....), and save it as a document template.

Additional notes:
Unless you have a very valid reason, avoid global variables. It is better to give variables as parameters to functions.
Make sure to unload any ressource whenever you no longer need it.
Whenever you instanciate a ressource (in other words: whenever you use the new keyword or createObject function), always write an error Handler.
0
Veena RajuAuthor Commented:
Again your just awesome. am pleased to meet you and you made my life easier.
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Addins

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.