![]() You cannot update the data in Excel as the Power BI report evolves.You can only export data from one visual at a time, in a new CSV.This first type of Power BI export to Excel is simple but limited as: If (Not oFolder Is Nothing) Then GetFolder = can now edit your data or re-make graphs directly in Excel. Columns("E").NumberFormat = i = 0 To UBound(Split(StrCmt, vbCr)) Reference.Information(wdActiveEndAdjustedPageNumber) & _ StrCmt = StrCmt & vbCr & Split(strFolder, ".doc")(0) & vbTab StrCmt = Replace("File,Page,Author,Date & Time,H.Lvl,Commented Text,Comment,Reviewer,Resolution,Date Resolved,Edit Doc,Edit By,Edit Date", ",", vbTab)ĭim wdApp As New Word.Application, wdDoc As Word.Document This version of the Excel macro outputs all the document comments to the active worksheet(starting at row 1), with the filenames in column A. If (Not oFolder Is Nothing) Then GetFolder = Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) Set wdDoc = Nothing: Set wdApp = Nothing: Set xlWkSht = Nothing Cells(i + 1, j + 1).Value = Split(StrTmp, vbTab)(j) Columns("D").NumberFormat = i = 0 To UBound(Split(StrCmt, vbCr))įor j = 0 To UBound(Split(StrTmp, vbTab)) StrCmt = StrCmt & Replace(Replace(.Text, vbTab, ""), vbCr, "") StrCmt = StrCmt & Replace(Replace(.Text, vbTab, ""), vbCr, "") & vbTab GoTo(What:=wdGoToBookmark, Name:="\HeadingLevel").ListString & vbTab 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. I'm trying to loop through all word documents in a folder and put all the comments for each file into an Excel workbook. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |