StrCmt = StrCmt & Replace(Replace(.Text, vbTab, ""), vbCr, "") StrCmt = StrCmt & Replace(Replace(.Text, vbTab, ""), vbCr, "") & vbTab GoTo(What:=wdGoToBookmark, Name:="\HeadingLevel").ListString & vbTab Reference.Information(wdActiveEndAdjustedPageNumber) & _ StrCmt = Replace("Page,Author,Date & Time,H.Lvl,Commented Text,Comment,Reviewer,Resolution,Date Resolved,Edit Doc,Edit By,Edit Date", ",", vbTab) Set wdDoc = (Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False) StrFile = Dir(strFolder & "\*.doc", vbNormal) StrFolder = GetFolder: If strFolder = "" Then Exit Subĭim wdApp As New Word.Application, wdDoc As Word.Document, xlWkSht As Worksheet 'Note: this code requires a reference to the Word object model.ĭim strFolder As String, strFile As String, StrCmt As String, StrTmp As String, i As Long, j As Long It loops through all Word documents in the selected folder, adding the comments from each commented document to new worksheets in the active workbook. StrTitle = Left(strTitle, Len(strTitle) - 1) ' Finds the first outlined numbered paragraph above the given paragraph objectĭo While ParaAbove.OutlineLevel = Para.OutlineLevel 'Set the fileName to the next available fileįunction ParentLevel(Para As Word.Paragraph) As String Cells(i + HeadingRow, 7).Formula = ActiveDocument.Comments(i). Cells(i + HeadingRow, 6).Formula = Format(ActiveDocument.Comments(i).Date, "MM/dd/yyyy") Cells(i + HeadingRow, 5).Formula = ActiveDocument.Comments(i).Initial Cells(i + HeadingRow, 4).Formula = ActiveDocument.Comments(i).Range Cells(i + HeadingRow, 3).Value = strSection Cells(i + HeadingRow, 2).Formula = ActiveDocument.Comments(i).Reference.Information(wdActiveEndAdjustedPageNumber) Cells(i + HeadingRow, 1).Formula = ActiveDocument.Comments(i).Index StrSection = ParentLevel(myRange.Paragraphs(1)) ' find the section heading for this comment Set myRange = ActiveDocument.Comments(i).Scope Set oDoc = Documents.Open(Filename:=sFilePath & vFile)įor i = 1 To StrSection = "preamble" 'all sections before "1." will be labeled as "preamble" Cells(HeadingRow, 6).Formula = "Reviewer" Cells(HeadingRow, 5).Formula = "Comment" Cells(HeadingRow, 4).Formula = "Paragraph" Cells(HeadingRow, 2).Formula = "Comment" Cells(HeadingRow, 1).Formula = "File Name" Set xlApp = CreateObject("Excel.Application") UPDATED CODE: 'VBA List all files in a folder using Dir It appears to open each word document but doesn't populate the excel sheet with the comments. I updated the code to open each word file, but I get the following error: Run-time error '5': Invalid procedure call or argument This website looked promising for what I need to do, but no one answered his questionĮxtracting data from multiple word docs to single excel I've looked at numerous websites and found plenty of references for extracting comments into excel, but not for all word files in a directory. The code only gets comments from the first file in the directory, then errors, it's not looping. When I run my code I get the following error "Run-time error '91' Object variable or With block Variable not set. Let me know if you have any question and for sure, any help will be awesome.I'm trying to loop through all word documents in a folder and put all the comments for each file into an Excel workbook. I was able to find different solution using VBA to link the excel data to the word doc but not the opposite ! The excel spreadsheet attached shows an example on how I need to upload the data into different spreadsheet: the project 1 will be uploaded into the spreadsheet "project1" (I would like to do it with a button for example), project2.doc into project2 tab etc. My previous thought was to use the form function but using that it seems that you can enable the track changes function because the word doc is protected. The word doc will use a userform to complete the document and it will allow other user to write some comments and/or track changes. Keep in mind, that each word file has a different name and location in our server when saving. I did some search and finally the most promising solution seems to use "userform" and bookmark in word (and build a template rather than a form). We are using word mainly for the track changes function. The list of project is extensive and we need for an other purpose to upload all data written in the word doc into an excel spreadsheet. My team is using a word document to track some data related to different project.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |