#1
|
|||
|
|||
Automatically Convert Text to Table with unique character as deliminator
Hi,
I have a piece of software that outputs a report in text format which is a mixture of words and tables. The report uses spaces to make the report look correct and ordered in notepad but when this is copied over to our final report and center aligned it looks like a mess. I have been using the record macro feature to remove the extra spaces throughout the report and to add a unique character '£' where the deliminator for the tables needs to be. I can now go through the document and manually select the tables and use the convert text to table feature which works well but I want to automate this process using VBA. I am thinking of having word VBA to loop through the document, find all sentences / lines that have £ in them, select the line and then convert text to table for that individual line but I cannot find anything on the web like this so I don't know where to start. I cannot select the whole document and covert it due to it being a mixture of text and tables and some tables having 3 columns and others 7. This is a sample of the text with £ in it which has the first 5 lines as either text or paragraphs then the rest of the data as a table Code:
Adjusted Observations and Residuals =================================== Adjusted Distance Observations (Meters) From£To£Distance£Residual£StdErr£StdRes£File:Line SEL01£SEL06£294.3875£-0.0003£0.0015£0.2£1:31 SEL01£SEL02£15.3749£-0.0003£0.0014£0.2£1:32 SEL07£SEL05£307.6708£-0.0001£0.0015£0.0£1:35 SEL07£SEL06£309.3499£-0.0006£0.0015£0.4£1:36 SEL05£SEL07£307.6708£0.0001£0.0015£0.1£1:39 SEL05£SEL04£314.7247£-0.0004£0.0015£0.3£1:40 SEL04£SEL05£314.7247£0.0006£0.0015£0.4£1:43 SEL04£SEL03£52.4068£-0.0001£0.0014£0.1£1:44 SEL03£SEL04£52.3564£0.0006£0.0014£0.4£1:47 SEL03£SEL02£33.3976£0.0005£0.0014£0.4£1:48 Thanks |
#2
|
||||
|
||||
You could use Find/Replace to change the £s to tabs, then use the normal text-to-table process. No VBA required.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
I have just tried this but the results don't look good.... because the report is a mixture of worded paragraphs and tables that may have 3 columns or up to 7 columns, selecting all of the document and using text to table the whole document including the sentences will then have 7 columns.
What I need to be able to do is to automatically select either individual lines and convert them to tables or to be able to make an automatic selection of certain parts of the document and convert this to a table. I have thought about using bookmarks in the document and then automatically selecting the text between the bookmarks but I could not figure this out. The report may contain 4 pages or 50 pages depending on the size of the job but there are always certain headers or words that are consistent between all reports which I can use to find / replace or format. Thanks |
#4
|
||||
|
||||
Well, obviously, you'd only use the process I outlined for the ranges that should be converted to tables, not to the whole document. For a macro, you might try something like:
Code:
Sub Demo() Application.ScreenUpdating = False With ActiveDocument.Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "?" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute End With Do While .Find.Found .Paragraphs.First.Range.ConvertToTable Separator:="?", AutoFit:=True, _ Format:=wdTableFormatList3, AutoFitBehavior:=wdAutoFitContent .Collapse wdCollapseEnd .Find.Execute Loop End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thank you - this looks very good. I just need to do some more formatting at my end before running this.
Is there something that I can add / change to your code that will center align the tables to the document when they are created? Selecting each individual table will align them but not if I press control+A on the whole document. |
#6
|
||||
|
||||
Based on Paul's code you could use the following which will centre the table contents
Code:
Sub Demo2() Dim oTable As Table Application.ScreenUpdating = False With ActiveDocument.Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "£" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute End With Do While .Find.Found Set oTable = .Paragraphs.First.Range.ConvertToTable _ (Separator:="£", AutoFit:=True, _ Format:=wdTableFormatList3, _ AutoFitBehavior:=wdAutoFitContent) oTable.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter .Collapse wdCollapseEnd .Find.Execute Loop End With Application.ScreenUpdating = True End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
Thank you both. This works perfectly. I had to change the table formatting to none to align it to the centre but this worked out better for me anyway
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Row seperator control character in Text To Table | AndreQ | Word Tables | 2 | 04-12-2019 07:23 AM |
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY | Lady18 | Excel | 5 | 09-10-2016 03:12 AM |
How to convert VLOOKUP values to text on another sheet AUTOMATICALLY | Lady18 | Excel | 1 | 09-10-2016 12:15 AM |
Convert Text To Table: Separate text at (Other)? | tinfanide | Word VBA | 2 | 01-12-2015 05:26 AM |
Convert graphic to text in a table | Jennifer Murphy | Word | 6 | 01-24-2013 12:56 PM |