Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    XL to Word table (2000)

    I have a workbook containing raw data on a range of consumer products. The client wants to be able to print this "catalog" in a more readable form, and insists on a MS Word doc, which is effectively one big table. I can consolidate data from groups of cells and produce the "readable" Word table cell entries.

    e.g. the XL cells containing price structure: 2.45, 15, 2.10, 50, 1.85 can be consolidated into another cell to appear in the Word table as a single cell:
    $2.45 each
    $2.10 each (minimum order 15)
    $1.85 each (minimum order 50)

    The biggest problem I have is that one XL column contains the filename of the graphic associated with each product e.g. pk107.jpg. I want this graphic to be displayed in a cell of the printable Word doc. I thought of using an INCLUDEPICTURE field but can't figure how to place this in an XL cell, so that it will function as a field when Word converts a copied XL range to a table. I'm wondering what's the best way to achieve this and, if indeed, my method is appropriate.

    I'm also after any general suggestions on such a conversion. I know that the client's staff have only rudimentary Office knowledge, and it is they who will have to periodically carry out this conversion to produce updated catalogs. Anything in the way of "automated" would be an advantage.

    thanks for any ideas

    Alan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XL to Word table (2000)

    One option I see if they only need to print out in word is to format exactly how you want the table to look in excel and then copy that range and then open a word file and pastespecial PICTURE. You will get a "picture" of the excel spreadsheet including formatting that you can resize in word but that is not "editable" in a word sense (you would have to use a picture editor)

    YOu would have to open the jpg in excel and place them where you want them but it should be doable.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Thanks Steve, but not really the kind of solution that would be appropriate. I suspect that they still might want to "fiddle" the Word table a bit - alignment, cell widths etc. If only I could work out some code that could be associated with each Word table cell, that would link to the appropriate graphic, I think I might be able to get somewhere.

    Alan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XL to Word table (2000)

    Could you post an example of what your excel sheet looks like and the what you want the "word Table" to look like?

    It is possible to open and work with word from excel and this might be what you want. It is tough to "play" without something to work with.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Hi Allan

    Have you considered pasting the XL 'table' as a link? (In MS Excel copy the range, switch to MS Word, choose Edit | Paste Special... Paste link and choose HTML format) In this way the data (including the picture) are linked to the XL spreadsheet. Any changes made in the spreadsheet is automatically updated in the Word document whenever it is opened, etc. I tested a small example, changed the graphic and updated some figures and it worked. Users can still 'fiddle' with their MS Word copy but it will update automatically when they 'refresh' the link or close and reopen, or print preview, etc.

    If you choose to Paste Special... Paste link as Microsoft Excel Worksheet Object it can't be fiddled with but users can double-click and return to the source MS Excel file and edit the information.

    HTH, Leigh

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Thanks Leigh. I have a question regarding your method. I presume that you have an actual graphic displaying in a particular XL cell. How did you manage to "attach" such a graphic to a particular cell, rather than having it sit/ float in the drawing layer? Also, I'm probably stuck with just a textual path/ file name in the XL sheet, but I would like to give your method a go. So how, for instance, could I "insert" a graphic file into cell B4 and have the cell height & width pull around its edges?

    Alan

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Hi Allan

    I have just recreated the original test that I did before (permanently deleted them prematurely) and I am experiencing a difficulty that I did not see before. This is when I use the option to paste a link as an HTML format not as a MS Excel Worksheet Object.

    In particular, the pictures that I aligned to the cells have decided to float and as such are not affixed to the cells to which they should. I did use a command in MS Excel to Move and size with cells (right-click on picture, click on Format Picture... and from the Properties tab choose Move and size with cells.) see the attachment for how the graphics displayed using either method.

    I had not noted this before and I do not know a solution. I will explore some more. (BTW, I have tested in Office XP only even though you asked about version 2000.)

    Leigh

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Hi Steve
    I've attached a quite small (and rough) example of what I'm trying to achieve. There are 2 XL sheets in the workbook. Sheet 1 has some "raw" data, typical of how things are stored at the moment. Sheet 2 shows this data "consolidated" to readable form, ready for a Word table. In the actual existing workbook, I use a toolbar button to display the pictures in a self-sizing userform.

    The Word doc shows how the client would like things to be, and a couple of my attempts to get things that way. There are hundreds of these products, so manual methods (other than page breaks, table border dragging etc.) is really out of the question. The .JPG is the sample picture I've used.

    Any ideas you have would be greatly appreciated. BTW, I've used char(10) extensively here for the purpose of linebreaks in the layout. Is there a character code for a tab, within a cell? I doubt it, but thought it worth an ask.

    thanks

    Alan

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Hi Leigh

    Thanks for your example. I'll have a further play with the Paste Special options and see if any might be suitable/ adaptable. I've had the problem of being unable to reliably "fix" a graphic to a cell since I started on this project. It's for this reason I'm trying <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> to generate some code that can link the graphic in Word, and place that link inside a table cell. As you might see from the attachment I have posted in reply to Steve, I can do it from within Word by creating a manual link. But there's no way I can see to generate this in Excel. The last resort I can think of is to use HTML export of the XL sheets, and have them contain cells with hyperlinks like
    [img]graphic.jpg[/img]
    I could open & edit the HTML in Word and hopefully the pics would display the way they're meant to.

    Alan

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    This link looks as if it might be helpful to you
    http://www.computorcompanion.com/LPMArticle.asp?ID=126

    HTH

    Peter

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XL to Word table (2000)

    In word you can use this code:

    <pre>ActiveDocument.Tables(1).Cell(2, 4).Select
    Selection.InlineShapes.AddPicture _
    FileName:="d:filename.jpg", _
    LinkToFile:=True, _
    SaveWithDocument:=False
    </pre>


    to add a picture to a table (specifically tables(1), the 2nd row and the 4th col. Change the table number and row/column (via loops in macro) and filename as appropriate. This links and does not save with file, change as appropriate.

    Steve

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Holy Toledo! I knew Cindy Meister wrote good stuff, but I didn't know she read minds <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. This looks like it might be the elusive path I've been seeking. Many thanks Peter.

    Alan

    (and Hans for letting me click <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  13. #13
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    Unfortunately the PC I was working from will not open another window from a link so I could not get to the "1-Click TagPanel". <img src=/S/sad.gif border=0 alt=sad width=15 height=15> A failed attempt by IS to block the internet on that machine I think. <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26> I have gotten so used to using the tag panel that I could not work out how to activate the link with out it <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    Peter

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: XL to Word table (2000)

    Hi Peter,

    Big Brother is watching you!
    The way to make a URL clickable is to put <!t><!/t> before it and <!t><!/t> after it.
    There is also a second form: <!t><!/t>WOPR Portal<!t><!/t> - the web address after url= within the first tag, and a descriptive text between the tags.

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XL to Word table (2000)

    But I see you're making up for it now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Just FYI, a lot of advert filters/ popup blockers will prevent the opening of secondary browser windows. Some of the more well thought out ones will NOT prevent the opening of such windows, if they're the result of a deliberate user action, like a mouse click. Alas, others don't differentiate <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •