Thread: [Solved] Extract Line of Text w/ specific characters up to the paragraph character, send to Excel
View Single Post
 
Old 07-19-2015, 08:56 PM
dmarie123 dmarie123 is offline Windows 8 Office 2013
Novice
  
Join Date: Dec 2012
Location: New Yuk
Posts: 23
dmarie123 is on a distinguished road
Default
Hi Paul,

Thank you for your help!! Once again MS Office is singing because of the macro you wrote. Initially when I ran the macro it wasn't extracting anything but I played around with the .text line and figured it out. It was my description to you that was not accurate, I used "XXX" which was interpreted as characters, I should've said digits. I'm happy I figured it out .

Now I have more questions, please let me know if I should start a new thread and mark this one solved...
So, I've processed about 10 of the reports with the macro and then queried the imported text file (which is AWESOME) in Access. I have around 45 search terms in the Access query but I'm only getting hits on about 2% of the names so I'm wondering if it would be better/possible to search what the macro extracts?

When you helped me with this: https://www.msofficeforums.com/word-...xtensions.html, the macro used a spreadsheet to search and replace which I think could be applied here? How would I build wildcards in to that? For example, if I want to search for "* MD*", so a space plus "MD", with wildcards at either end but outside the space that precedes the "M".

There are only 5 parameters in Access that need a wildcard then a space then the word to be searched and then another wildcard. Maybe I could have Word use an Excel spreadsheet for concrete search words and then still use Access for the 5 different ones? Throwing ideas out, not sure what the best approach is...

This is similar to the bulk find replace macro in that my string got to long, that what lead me to search and ultimately brought me to the world of VBA haha. If you think it's better to use the current macro and import to Access I trust your advice.

This is what is working for me right now, I changed the second curly bracket to "12":
Code:
Sub CorpData()
Application.ScreenUpdating = False
Dim StrTmp As String, StrRec As String, StrDat As String
Dim i As Long, j As Long, x As Long, StrFlNm As String
StrFlNm = "C:\Users\" & Environ("UserName") & "\Documents\CorpData.txt"
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "<[0-9]{12}[!^13]{1,}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found = True
    x = x + 1
    StrRec = .Text
    StrTmp = Split(StrRec, " ")(0)
    i = Len(StrRec): j = Len(StrTmp)
    StrDat = StrDat & Chr(34) & StrTmp & Chr(34) & vbTab & _
      Chr(34) & Trim(Right(StrRec, i - j)) & Chr(34) & vbCrLf
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
  If Len(StrDat) > 1 Then
    Close #1
    StrDat = Left(StrDat, Len(StrDat) - 1)
    Open StrFlNm For Output As #1
    Print #1, StrDat
    Close #1
  End If
End With
StatusBar = "Done! The output for " & x & " records is now in: " & StrFlNm
Application.ScreenUpdating = True
End Sub
Reply With Quote