Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Help needed with VBS file...

bijunatorbijunator Posts: 1Member
Wrote a VBS to get comments from all the word documents present in a folder and write them into an excel sheet with separate sheets for each document. But the page number and line number seems to be not working. Tried many different ways, googled a thousand times but to no avail...frustrated now...please if someone could point out whats the problem here...

Option Explicit
On Error Resume Next
Dim appObj, exlObj, wBkObj, wShtObj, wrdObj
Dim p, q, r
Dim srcFolder, strFolder, strFile, wordDoc, fileName, docPath, sheetName
Dim myStr
Dim FSO, FLD, FIL
Dim cmntCntr, totCmnts, cmntObj
Set wrdObj = createObject("Word.Application")
Set exlObj = CreateObject("Excel.Application")
Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder...", 0)
If (Not srcFolder Is Nothing) Then strFolder = srcFolder.Items.Item.Path
Set srcFolder = Nothing
MsgBox " Folder --> " & strFolder
fileName = strFolder & "" & "review_comments_details.xls"
MsgBox "XLFileName -->" & fileName
q = 0
docPath = strFolder & "*.doc"
MsgBox "Word Path -->" & docPath
'Create the filesystem object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Get a reference to the folder you want to search
set FLD = FSO.GetFolder(strFolder)
'loop through the folder and get the file names
For Each Fil In FLD.Files
MsgBox Fil.Name
MsgBox Fil.Type
if Fil.Type = "Microsoft Word Document" then
Set wordDoc = wrdObj.Documents.Open(strFolder & "" & Fil.Name)
wrdObj.Application.visible = false
wrdObj.Application.DisplayAlerts = False
With wordDoc
MsgBox "opened word document -->" & wordDoc
sheetName = Mid(Fil.Name, 1, Len(Fil.Name) - 4)
MsgBox "Sheet Name -->" & sheetName
p = 0
r = 1
totCmnts = wordDoc.Comments.count
If totCmnts > 0 Then
q = q + 1
MsgBox "Document No -->" & q
'Start Excel
MsgBox "No of comments -->" & totCmnts
If q = 1 Then
MsgBox "Opening Excel Workbook"
Set wBkObj = exlObj.workbooks.Add()
End If
MsgBox "Adding Workbook Sheet --> " & q
Set wShtObj = wBkObj.Worksheets(q)
wShtObj.Name = sheetName
wShtObj.Cells(r, 1) = "Serial No"
wShtObj.Cells(r, 2) = "Page No"
wShtObj.Cells(r, 3) = "Line No"
wShtObj.Cells(r, 4) = "Author"
wShtObj.Cells(r, 5) = "Comment Text"
wShtObj.Cells(r, 6) = "Status"
For Each cmntObj In wordDoc.Comments
r = r + 1
p = p + 1
wShtObj.Cells(r, 1) = p
'the trouble code starts
wShtObj.Cells(r, 2) = cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
wShtObj.Cells(r, 3) = cmntObj.Scope.Paragraphs(1).Range.Information(wdFirstCharacterLineNumber)
'the trouble code ends
wShtObj.Cells(r, 4) = cmntObj.Author
wShtObj.Cells(r, 5) = cmntObj.Range.Text
MsgBox "Comment1 --> " & p
' cmntCntr = cmntObj.Scope.Information(wdActiveEndPageNumber)
' cmntCntr = cmntObj.Scope.get_Information(wdActiveEndPageNumber)
cmntCntr = cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
MsgBox "Comment2 --> " & cmntCntr
Next
End If
end with
wordDoc.Close
end if
Next
wrdObj.quit
If Not exlObj Is Nothing Then
MsgBox "Saving Excel Workbook"
wBkObj.SaveAs fileName
End If
exlObj.quit
MsgBox Err.Description, vbExclamation

Sign In or Register to comment.