Transferring standardized data from Word to Excel

Guessed · #4 01-19-2023, 07:36 PM

Your <some text to be ignored> didn't appear in your sample doc. If you provide some real examples of the text to be ignored we can offer some other methods of dealing with them. I still think that it can be a ConvertToTable method but we need to identify the pattern of correcting that extra paragraph of data.

Perhaps this code will sort out the columns correctly. If that works it should be easy to remove the extra columns either before or after making it a table.

Code:

Sub MacroTest()
  With ActiveDocument.Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "(Cake description:•)^l([!^13]{1,})^13([!^13]{1,})^13Color"
    .Replacement.Text = "\1^p\2^p^p\3^pColor"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
    .Text = "^l"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
  End With
  Selection.WholeStory
  Selection.ConvertToTable Separator:=wdSeparateByParagraphs, NumColumns:=10, NumRows:=3, AutoFitBehavior:=wdAutoFitFixed
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to transferring Word data to an Excel sheet	statue.919	Word VBA	16	06-18-2020 05:14 PM
transferring data	tofimoon4	Excel	3	10-03-2016 11:57 PM
Problem transferring form controls from excel to word	pluqk	Word	1	11-19-2015 07:23 PM
transferring data from ACT to Outlook?!	Rudi	Outlook	0	12-05-2012 12:56 PM
Standardized Task Lists in Outlook 07	sai_rlaf	Outlook	0	06-11-2012 01:48 PM