Extract word strings into excel

AES_AES
NewLounger
Posts: 3
Joined: 27 Jun 2021, 05:02

Extract word strings into excel

Post by AES_AES »

Hi im new here :cheers: and ive been helped before by HansMVP :thankyou: on the microsoft community but for some reason the macro provided then isnt working anymore i have no clue why! im posting here in case HansMVP or anyone else can come up w a solution:

heres what i wrote there: How can I go about extracting certain strings of words in Microsoft Word that are between brackets or {} , along with the brackets or braces , in a folder of word docs and place them line by line in Excel.

the code i have is:

Code: Select all

Sub ExtractText()
    Dim xApp As Object
    Dim xWbk As Object
    Dim xWsh As Object
    Dim lRow As Long
    Dim bNew As Boolean
    
    Dim sFld As String
    Dim sFil As String
    
    Dim cDoc As Document
    Dim cRng As Range
    
    ' Prompt for folder
    With Application.FileDialog(4) 'msoFileDialogFolderPicker
        If .Show Then
            sFld = .SelectedItems(1) & Application.PathSeparator
        Else
            Beep
            Exit Sub
        End If
    End With
    
    ' Get or start Excel
    On Error Resume Next
    Set xApp = GetObject(Class:="Excel.Application")
    If xApp Is Nothing Then
        Set xApp = CreateObject(Class:="Excel.Application")
        bNew = True
    End If
    
    ' Create new workbook with one sheet
    xApp.ScreenUpdating = False
    Set xWbk = xApp.Workbooks.Add(Template:=-4167) ' xlWBATWorksheet
    Set xWsh = xWbk.Worksheets(1)
    
    ' Loop through the documents in the folder
    sFil = Dir(sFld & "*.doc*")
    Do While sFil <> ""
        Set cDoc = Documents.Open(FileName:=sFld & sFil, AddToRecentFiles:=False)
        Set cRng = cDoc.Content
    
        With cRng.Find
            .Text = "\{*\}"
            .ClearFormatting
            .Replacement.Text = ""
            .Replacement.ClearFormatting
            .Forward = True
            .Wrap = wdFindStop
            .MatchWildcards = True
            Do While .Execute
                lRow = lRow + 1
                xWsh.Range("A" & lRow).Value = sFil
                xWsh.Range("B" & lRow).Value = cRng.Text
            Loop
        End With
        cDoc.Close SaveChanges:=False
        sFil = Dir
    Loop
    
    xWsh.Range("A1:B1").EntireColumn.AutoFit
    xApp.ScreenUpdating = True
    
    If bNew Then
        xApp.Visible = True
    End If
End Sub
the issue are: on one computer it loops through all docs and never pulls up excel afterwards so does basically nothing.

strangely enough, on a diff computer it pulls up excel right away and doesnt do any looping and thats it.

User avatar
HansV
Administrator
Posts: 72003
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Extract word strings into excel

Post by HansV »

Welcome to Eileen's Lounge!

Above the line

Code: Select all

    ' Create new workbook with one sheet
insert

Code: Select all

    On Error GoTo 0
Then run the macro on each of the computers. If you get an error message, what does it say?
In the error dialog, click Debug, then note which line of code is highlighted.

If you don't get an error message and yet the code does not do what you want:
Click anywhere in the code.
Press F8 repeatedly to single-step through the code.
See if you can find out what happens.
Regards,
Hans

AES_AES
NewLounger
Posts: 3
Joined: 27 Jun 2021, 05:02

Re: Extract word strings into excel

Post by AES_AES »

hi ive added that line and nothing different has happened on either comp: one loops thru and doesnt open excel, the other opens excel immediatly after choosing a folder and ends there.

AES_AES
NewLounger
Posts: 3
Joined: 27 Jun 2021, 05:02

Re: Extract word strings into excel

Post by AES_AES »

In addition, Ive created 2 new docs w words surrounded by [ ] instead of { } and adjusted the macro to look for brackets and the same result happens.

its like the macro just decided not to work anymore!

User avatar
HansV
Administrator
Posts: 72003
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Extract word strings into excel

Post by HansV »

Thanks for your email. This is the result when I run the macro and select the folder that you sent:

S0546.png

So the code works. It's difficult for me to know why it fails for you, and in two different ways...
You do not have the required permissions to view the files attached to this post.
Regards,
Hans