Macro To Convert Table To Text In Word

daamyour · Jan 19th 2008

I tried recording a macro to convert a table copied from excel to word, but in record mode "convert table to text" is greyed out.

Step 1. After pasting the table to word I need to convert table to text paragraphs.

Step 2. Replace all spaces with tabs

Step 3. Remove hanging tabs (^t^p replaced with ^p)

Step 4. Remove periods.

Step 5. Remove commas.

Step 6. Replace JR with JR.

Step 7. Convert text to table by tabs

Page one is before and page two is after.

Thanks for any help you're able to give.

PeteNZ · Feb 7th 2008

Re: Macro To Convert Table To Text In Word

Below code may help with the convert to text issue.

Code

Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
        NestedTables:=True

Wonder why you need to put into Word?
Cheers
Pete

mgrands · Feb 7th 2008

Re: Macro To Convert Table To Text In Word

Okay this is silly to ask here but how do I start a new post. For some reason I can not find this option on my computer.

Please help.

Thanks.

PeteNZ · Feb 7th 2008

Re: Macro To Convert Table To Text In Word

You need to go to the start of the forum section - here's a quick link

Quote

http://www.ozgrid.com/forum/forumdisplay.php?f=34

There's a button for creating a new thread on that page.
Cheers

daamyour · Feb 13th 2008

Re: Macro To Convert Table To Text In Word

Quote from PeteNZ
Below code may help with the convert to text issue.
Code
Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
        NestedTables:=True
Wonder why you need to put into Word?
Cheers
Pete

Thanks for your input and sorry for the delay in responding. I'm using word because the data I'm converting may hanging spaces at the end of each line. I tried creating a macro in excel for text to column, but it wont work and my post seems to have been deleted. I have no idea why. I just know it's gone. It seems all my posts get deleted after I'm thinking I'm following the rules. I tried attaching a sample, but I'm having trouble doing this. I can email it to you if needed.

If a macro can automate this for me this is what I'm looking for:

Columns C-H are named C1-1 through C1-6 for claimant 1.
Columns I-N are named C2-1 through C2-6 for claimant 2.
Columns Q and R are for State and Zip.

In my raw data Column C hase claimant 1 full name and Column I had claimant 2 full name and Column Q has State and Zip.

1. From my excel file I want to copy the data in column C to word and "convert table to text" as paragraphs.
2. I want to then remove any hanging spaces at the end of each line to make sure my data remains 6 columns wide.
3. I want to remove commas and periods before converting any instances of "JR" to "JR." and convert all spaces to tabs.
4. After that, "convert text to table" using tabs.
5. Copy from word back to sheet 2 in excel and return to step 1 using column I and repete and back to step 1 for column Q.

If you have an easier way to accomplish this, by all means.

Thanks for any help,
Jim[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I guess reading back on my origanal post I forgot I already posted a sample. :0ops:

Shirley · Feb 16th 2008

Re: Macro To Convert Table To Text In Word

This macro code does what you say you want to do in Word.

Code

Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
        NestedTables:=True
    With Selection.Find
        .Text = " "
        .Replacement.Text = "^t"
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = "^t^p"
        .Replacement.Text = "^p"
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = "."
        .Replacement.Text = ""
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = ","
        .Replacement.Text = ""
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = "JR"
        .Replacement.Text = "JR."
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=4, _
        AutoFitBehavior:=wdAutoFitFixed

Display More

daamyour · Feb 17th 2008

Re: Macro To Convert Table To Text In Word

Quote from Shirley

This macro code does what you say you want to do in Word.

Thanks Shirley,

Your code works well.
Is there a way for the macro to select the table? It seems that for it to work I need to manually select the table.
Also, is it possible for this macro in word to select the data in excel, copy it, and paste it into word, run it's process and return the new data to excel?

I can work with this, but if the macro can eliminate one or two more steps - GREAT!

Thanks again for your time.

Shirley · Feb 17th 2008

Re: Macro To Convert Table To Text In Word

Quote from daamyour

Thanks Shirley,

Your code works well.
Is there a way for the macro to select the table? It seems that for it to work I need to manually select the table.
Also, is it possible for this macro in word to select the data in excel, copy it, and paste it into word, run it's process and return the new data to excel?

I can work with this, but if the macro can eliminate one or two more steps - GREAT!

Thanks again for your time.

Display More

Hi

If you have pasted directly from Excel you will probably need the first of these two lines of code to place your cursor within the table, the second then selects the table.

Code

Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.Tables(1).Select

Myself, I'm not advanced enough in VB to cross between applications but it can be done. I would probably find a way to do it in Excel to be honest.

daamyour · Feb 18th 2008

Re: Macro To Convert Table To Text In Word

Shirley,

Thanks for your help. I'll give your suggestion a try. At this point you've given me enough help to eliminate several rutine steps. I can consider this matter resolved and closed.

Thanks for your time.

Jimmy

Macro To Convert Table To Text In Word

Participate now!

Share

Users Viewing This Thread