Macro to import text from Word to Excel

pwyf · 03-05-2009, 01:13 AM

Hi everyone,

I am working on several Word documents which look like so.

Here is how each block of text is arranged:

Numbers tab Numbers Paragraph
Text Paragraph
Text Paragraph
Paragraph

I would like to import this data into Excel to make it look like this.

First column: first series of numbers
Second column: second series of numbers
Third column: First line of text(alt+return)Second line of text

I hope all of this is clear.

I have tried creating a macro on my own, but I don't know Visual Basic, so I have failed miserably.

I need to do this on 40 documents which contain up to 100 blocks of text.

Any help would be appreciated!

Thanks a lot.

**macropod** · 03-06-2009, 11:49 PM

Hi pwyf

Does the 1st paragraph everhave more than the two times and a single tab separating them?
Do the other paragraphs have tabs or commas in them?

pwyf · 03-07-2009, 04:05 AM

Hi macropod,

The tab only appears in the lines containing numbers.
On the other hand, there are often commas in the lines of text.

Hope that helps!

pwyf

**macropod** · 03-08-2009, 04:03 AM

Hi pwyf,

Here's some code that takes the active document and converts its contents to a tab-delimited format suitable for exporting to Excel:

Code:

Sub MakeTabDelimitedText()
Dim EndRng As Range
With ActiveDocument
  .Paragraphs(1).Range.Text = Replace(.Paragraphs(1).Range.Text, vbCr, vbTab)
  With .Content.Find
  .ClearFormatting
  .Replacement.ClearFormatting
    .Text = "^p"
    .Replacement.Text = Chr(10)
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
  End With
  Set EndRng = .Range.Characters.Last
  With EndRng
    .MoveStart wdCharacter, -1
    .Delete
  End With
End With
End Sub

There is plenty of code about for looping through multiple files in a folder and also for exporting the content of such files to an Excel workbook - I've posted some in various forums myself. A web search should locate some suitable examples which, with the code I've posted above, you should all you'll need to build a suitable routine.

pwyf · 03-10-2009, 03:58 AM

Thanks a lot, macropod!

03-06-2009, 11:49 PM	#2 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 559 OS: Vista	Re: Macro to import text from Word to Excel Hi pwyf Does the 1st paragraph everhave more than the two times and a single tab separating them? Do the other paragraphs have tabs or commas in them? __________________ Cheers macropod (MS MVP -Word)

03-07-2009, 04:05 AM	#3 (permalink)
pwyf Registered User Join Date: Mar 2009 Posts: 3 OS: Windows XP	Re: Macro to import text from Word to Excel Hi macropod, The tab only appears in the lines containing numbers. On the other hand, there are often commas in the lines of text. Hope that helps! pwyf

03-08-2009, 04:03 AM	#4 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 559 OS: Vista	Re: Macro to import text from Word to Excel Hi pwyf, Here's some code that takes the active document and converts its contents to a tab-delimited format suitable for exporting to Excel: Code: Sub MakeTabDelimitedText() Dim EndRng As Range With ActiveDocument .Paragraphs(1).Range.Text = Replace(.Paragraphs(1).Range.Text, vbCr, vbTab) With .Content.Find .ClearFormatting .Replacement.ClearFormatting .Text = "^p" .Replacement.Text = Chr(10) .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute Replace:=wdReplaceAll End With Set EndRng = .Range.Characters.Last With EndRng .MoveStart wdCharacter, -1 .Delete End With End With End Sub There is plenty of code about for looping through multiple files in a folder and also for exporting the content of such files to an Excel workbook - I've posted some in various forums myself. A web search should locate some suitable examples which, with the code I've posted above, you should all you'll need to build a suitable routine. __________________ Cheers macropod (MS MVP -Word)

03-10-2009, 03:58 AM	#5 (permalink)
pwyf Registered User Join Date: Mar 2009 Posts: 3 OS: Windows XP	Re: Macro to import text from Word to Excel Thanks a lot, macropod!