Extract Line of Text w/ specific characters up to the paragraph character, send to Excel

dmarie123 · #9 07-19-2015, 08:56 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to insert paragraph character after every 500 characters?	aditya_bokade	Word VBA	28	11-13-2021 10:48 PM
Macro to Insert text into the beginning on specific paragraphs unless the paragraph is blank	caboy	Word VBA	2	04-01-2015 07:00 AM
How can select from a specific character to another character	mohsen.amiri	Word	2	02-19-2015 11:38 PM
Replace paragraph-marks (line-breaks) in tables with a character-string	Aztec	Word VBA	2	04-02-2013 10:52 PM
How to import a text file but skip the first line regardless of characters?	omahadivision	Excel Programming	7	02-01-2013 08:30 PM