Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #7  
Old 07-16-2015, 11:42 PM
macropod's Avatar
macropod macropod is offline Extract Line of Text w/ specific characters up to the paragraph character, send to Excel Windows 7 64bit Extract Line of Text w/ specific characters up to the paragraph character, send to Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I was thinking of something like:
Code:
Sub Demo()
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]{7,9}[ ]{3}[!^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
This produces a tab-delimited text file and the whole process should be much faster than looping through every paragraph.
To produce a csv file instead (e.g. for import into Excel), simply change CorpData.txt to CorpData.csv and change vbTab to ",".
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
 

Tags
excel 2013, extract word to excel, word 2013



Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Line of Text w/ specific characters up to the paragraph character, send to Excel How to insert paragraph character after every 500 characters? aditya_bokade Word VBA 28 11-13-2021 10:48 PM
Extract Line of Text w/ specific characters up to the paragraph character, send to Excel 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
Extract Line of Text w/ specific characters up to the paragraph character, send to Excel How can select from a specific character to another character mohsen.amiri Word 2 02-19-2015 11:38 PM
Extract Line of Text w/ specific characters up to the paragraph character, send to Excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft