Word formatting

[Queryman]

I have a process that produces a list in several columns, due to the complexity involved in producing this list, which involves several steps including inferring the last row & carrying over a letter to the next column, also counting the number of rows that can appear on each column based on the input data, the process is done in excel. This process is automated a produces a formatted list. The user wants the report in word but they want the functionality to edit the list, currently the only way I can think of reproducing the formatted list in word is to take the list that is produced in Excel and pasting it in word as a picture. This however disables the option to edit it as you would a normal word document. Also the pasting as a picture is done automatically via a SAS program, so if there are other ways to do this, it would have to be something that would lend itself readily to automation. Any suggestions.
Thanks

Michael

 

[Dreamboat]

Why not just paste? That puts it in as a Word table.

Why not paste as an Excel worksheet object? That makes it editable too. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Queryman]

Thanks, excellent suggestions.

Michael

 

[mousexpert]

Also try doing Edit/Paste Special/Paste Link......then the functionality of it is there from excel to word also

 

[Queryman]

Is that different from the above?

Michael

 

[mousexpert]

Yes it is.

Just pasting it does not update from the "source" to the "destination".

If you paste link it, this means that you can work in your excel sheet, and it will update the word document at the same time.

 

[Queryman]

I have been using Dreamboat's suggestion to copy the excel file & paste it in word as a word table. This seems to work fine except when the excel cell seems to contain a lot of words. Then the word table cell that contains that info get elongated to the right and pushes the cells to it right over. I then have to manually split the cell and paste the data in the correct cells.

Is there a way to just select the cells with data in excel, currently this process is automated and the whole spreadsheet is highlighte dto copy before pasting in word. I think if I can find a way to do that, maybe it will prevent that above from shifting cells.

Michael

 

[Dreamboat]

Try this after pasting:

Click inside any cell.
Hit Table-Select table.
Hit Table-Table properties.
Set Table width to 100% (type 100 in and it will change to percent)

See if that corrects the text wrapping itself.

Other things to try.
Select the table as above.
Hit Format-Paragraph.
Make sure the left and right indents are set to 0".

Before copying, select the area.
Hit Format-Cells, Alignment tab.
Make sure no cells are merged. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Queryman]

Thanks, also is there a way in VBA to select in Excel to paste in word only the cells which have data

Michael

 

[Dreamboat]

Not sure that you need VBA.

Try this:

Select the area.
Hit Edit-->Go to-->Special
Choose "Visible cells only"
Then copy.

Otherwise it'll require VBA, and if I were you'd I'd re-ask my question with a title like "Copy only non-blank cells using Excel VBA" Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX