Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2023, 06:27 AM
mwayan mwayan is offline Transferring standardized data from Word to Excel Windows 11 Transferring standardized data from Word to Excel Office 2021
Novice
Transferring standardized data from Word to Excel
 
Join Date: Jan 2023
Posts: 2
mwayan is on a distinguished road
Wink Transferring standardized data from Word to Excel

Hi, I have a relatively straightforward problem but have been unsuccessful at solving it so far.

I would like to transfer data from Word to Excel. The information in Word exists in the exact same format a few thousand times:

Quote:
Cake description:•
This is a description of a nice cake.¶
Blue¶
Color¶
Striped¶


Pattern¶
Ice frosting¶
Decoration¶

Cake description:•
This is a description of a second cake.¶
Green¶
Color¶
Lines¶
Pattern¶
Chocolate rice¶
Decoration¶

Cake description:•
This is a description of a third cake.¶
<some text to be ignored>¶
Pink¶
Color¶
Stars¶
Pattern¶
Hundreds and thousands¶
Decoration¶
The dataset is completely uniform in this regard. All three properties are always present in the exact same order with the exact same line break/paragraph break at the end. There is a new line/paragraph break at the end of every cake, but not at the end of description.

I have indicated the paragraph marks as this affects the VBA code used. I would like to iteratively append the data into an Excel sheet, as such:



I attempted to take inspiration from https://www.msofficeforums.com/word-...paragraph.html but did not get far. The code was able to get the Cake description, but I could not delete the words "Cake description:". I also thought of using
Code:
Selection.Previous(unit:=wdParagraph, Count:=1).Select
to select the Color, Pattern, and Decoration, but was not able to get the syntax right. I have attached a sample input .docx and output .xlsx.

My best attempt was this, but it did not work:

Code:
Sub Demo()
Application.ScreenUpdating = False
Dim strFnd As String, DocSrc As Document, DocTgt As Document
strFnd = InputBox("What is the Text to Find")
If Trim(strFnd) = "" Then Exit Sub
Set DocSrc = ActiveDocument: Set DocTgt = Documents.Add
With DocSrc.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = strFnd
    .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

    Selection.Previous(unit:=wdParagraph, Count:=1).Select

    DocTgt.Characters.Last.FormattedText = .Paragraphs(1).Range.FormattedText
    .End = .Paragraphs(1).Range.End
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub
I would love any kind of help. Thanks in advance!
Attached Files
File Type: docx Cake description.docx (18.3 KB, 3 views)
File Type: xlsx Cake description.xlsx (14.8 KB, 2 views)

Last edited by mwayan; 01-19-2023 at 06:36 PM. Reason: Inserted screenshot of desired output
Reply With Quote
  #2  
Old 01-19-2023, 03:44 PM
Guessed's Avatar
Guessed Guessed is offline Transferring standardized data from Word to Excel Windows 10 Transferring standardized data from Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I think this is a lot simpler than you are trying to make it.

In Word you can Select All and then go to Convert to Table, 8 columns, separate by paragraph. Then copy/paste the required columns to Excel.

In code terms the important steps in Word are
Code:
  Selection.WholeStory
  Selection.ConvertToTable Separator:=wdSeparateByParagraphs, NumColumns:=8, NumRows:=3, AutoFitBehavior:=wdAutoFitFixed
If you wanted the first column to be split into two columns, you could find/replace the soft return with a hard return and then make it a 9 column table
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 01-19-2023, 06:36 PM
mwayan mwayan is offline Transferring standardized data from Word to Excel Windows 11 Transferring standardized data from Word to Excel Office 2021
Novice
Transferring standardized data from Word to Excel
 
Join Date: Jan 2023
Posts: 2
mwayan is on a distinguished road
Default

Hi @Guessed, thanks for the suggestion.

Unfortunately there are some paragraphs that need to be omitted as indicated by the <some text to be ignored> and I am afraid a simple copy/paste of a few thousand entries will require a lot of cleanup.
Reply With Quote
  #4  
Old 01-19-2023, 07:36 PM
Guessed's Avatar
Guessed Guessed is offline Transferring standardized data from Word to Excel Windows 10 Transferring standardized data from Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
data transposition, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transferring standardized data from Word to Excel How to transferring Word data to an Excel sheet statue.919 Word VBA 16 06-18-2020 05:14 PM
Transferring standardized data from Word to Excel transferring data tofimoon4 Excel 3 10-03-2016 11:57 PM
Transferring standardized data from Word to Excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:04 PM.


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