Excel and Work Automation - need help with formatting tables 1

[Anna007]

I am populating book-marks with tables from excel. The tables in Excel are big and dynamic in size; so, creates problems for me in word. I need to autoformat it in such is a way that it won't take much room in the word doc. How do I do this via VBA code?
Hope this is descriptive enough.

Anna

 

[SkipVought]

Anna,

What is you business case?

What do you need to accomplish, not HOW do you think that it needs to be accomplished?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[macropod]

Hi Anna007,

If you use named ranges in Excel, a LINK field in word can use the named ranges to retrieve the data so that your Word tbale grows/shrinks as the number of source rows/columns in Excel changes. If you specify an appropriate link format (copy an area from Excel then use Edit|Paste Special in Word to see what's available), you can control the appearance of the linked data in Word.

Cheers

 

[Anna007]

Thanks for helping.
What I'm supposed to have are these large tables (lots of columns. ie., 14 -15 columns and about 6-7 rows. Since the number of columns are large, it doesn't fit on the page of the word document. I can't split it up too bc it's a business contract. Legal!
So, the size of the columns needs to be very small and the fonts small enough but readable. This is why I need to be able to edit the fields of the table that I bring into word.
Now, this table is dynamic. Only sometimes, the table I'm copying into word from excel has about 3-4 columns only. So, in this case, I have to resize the columns again. So, basically, I need to format it in such a way that it always fits on the page.. the columns.

Thanx sooooo much for helping.
Anna

 

[Anna007]

I have another question. What's the security like in Word? Does anyone know? I'm populating these word doc via Excel and I'm making them read-only but that's all. Can I password protect it via the VBA code??
I don't want anyone to change it afterwards unless they provide the password or something like this.

Thanx,
Anna

 

[macropod]

Hi Anna007,

For a table with many columns, I'd suggest using a landscape page layout. Alternatively/additionally, if you insert the linked object as an Excel worksheet, you can re-scale it by clicking on it and using Format|Object|Size. If, when doing that, you uncheck the 'Lock aspect ratio' option, you can scale the object's width and height independently. You can also do much the same by clicking on the object and dragging the vertical/horizontal picture handles.

As for security, you can password protect a Word document, via File|Save As|Tools|General Options. For vba-based proection, check out the Protect Method in the vba help. If you're using links to external Excel worksheets, though, you'd probably want to protect the workbooks too. Otherwise, they could be changed and the changes would then be reflected in your Word document. Again, you can protect a workbook manually or with vba.

Cheers

 

[Anna007]

Hi,

Thanx but this is not helping me much.
Ok. this is the case:
I have a bookmark in my word document and then I have this range in Excel (Amortization Table)
I am doing the following to copy and paste it into the word doc where the book-mark is:

range("AmortizationTable").copy
mydoc.Bookmarks("MyBookMark").range.pastespecial

Now, it's pasted as a table that it was in excel. But does word treat this as a table?? I need to do an AutoFit (to fit in the windowP) after pasted it - but via VBA Code!
there is an autofit property but not sure how to use it in this case. I tried but it was pasting it in excel intead!
I need it pasted into the word doc.

Thanx,
Anita

 

[macropod]

Hi Anna,

If you add an appropriate DataType to your PasteSpecial command, you could paste the data into Word as an Excel object. For example:
mydoc.Bookmarks("MyBookMark").Range.PasteSpecial DataType:=wdPasteOLEObject '(I think, or maybe wdPasteShape)

You could then re-size the pasted object. This has the distinct advantage of scaling everything together so that column/row relationships are maintained. You might find this much easier than trying to reformat what amounts to a pasted table in Word. The other thing to note, which might be a plus, is that a pasted object will retain any colouring/formatting that existed in the source (other than non-white font colours, which all go to black).

Cheers

 

[Anna007]

Thanx but this didn't help me.
The Excel range (like a table) is pasted into the word doc but then this is a table in word since I went to the menu of my word doc and then table, autofit to window, I selected. This worked but I have to automate this.

So, after it's pasted, how do I refer to it as a table??!

Thanx,
Anna

 

[macropod]

Hi Anna,

If you'd pasted the range in as an Excel object, it wouldn't be a table - it would be an object.

As for automating the autofit to window process for a table, try using something based on:
Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
This is what the macro recorder returns for the 1st table in a document.

Cheers

 

[Anna007]

Thank you. I actually did this a few days ago ... before you posted this but this was not good enough. So, I used another feature (autofit the content) and that seems to do the job.
Thanx anyways for your help.

Anna