#1
|
|||
|
|||
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:
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 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 Last edited by mwayan; 01-19-2023 at 06:36 PM. Reason: Inserted screenshot of desired output |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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 |
Tags |
data transposition, vba |
Thread Tools | |
Display Modes | |
|
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 |