Results 1 to 14 of 14
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import MSWord table (2000)-MULTI-Import MSWord table (2000)

    I need to import a number of large tables from MSWord 2000 into Excel spreadsheets and need advice on a couple of issues.

    One of the table columns contains cells with descriptive text including paragraph marks, which I would like preserved in the corresponding Excel cells.

    Another column contains 12 digit product codes, which I need to appear "verbatim" rather than be treated as numbers (and hence appear incorrectly in scientific notation).

    I would (ideally) like to be able to use find & replace to get the Word table into a suitable format, copy and paste (special?) into a blank worksheet, then apply appropriate commands column at a time, to get the worksheet "looking right". I realise that it may not be possible to do things so simply, so I would much appreciate any suggestions on how I might go about this.

    thanks

    Alan

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table (2000)

    Try formatting the columns where those entries go as Text before you do the copy and paste.
    Legare Coleman

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

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Thanks for that suggestion Legare. Certainly solved the long integer problem, but the problem of preserving line breaks from the table text cells remains.

    I had thought of replacing them in Word with *** or similar, then trying to replace *** in Excel with a linebreak. Unfortunately I don't know how to specify this as a "special character". I did see mention of a "substitute" function in these threads, but have no idea of how to implement it in this context.

    Alan

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    It could be done easily with a macro, but I am not sure how to do it using the Excel Find/Replace command. Maybe someone else can tell you how to do that before we write a macro to do it.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Legare,

    there was a similar <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=89235&page=&v iew=&sb=&o=&vc=1#Post89235> post </A> which didn't seem to have a good solution either for large number of Word table cells being imported into Excel.

    I tried Excel's find/replace but it is nowhere as powerful as Word's. You can't use as a replace object any special characters, at least I couldn't think of a way. Maybe one could invoke Word's replace dialog from within Excel?

    However, Jimbythebay just wrote almost the reverse function in this post on <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=103324&page=& view=&sb=&o=&vc=1#Post103324> Search for Alt-Enter </A> where you can replace Alt-Enter in Excel with spaces. So, if, in Word, one replaces line returns with some special character like maybe a tab (or non-sensical sequence of characters not likely to occur in the text, like ^&*), you now have a Word table that can be copied/pasted into Excel and maintain row integrity (ie, one Word table row per Excel row). The operation I described in Word is easy, kind of well-known, and certainly usable in Word's find/replace.

    I'd like to see the function when done too. This issue has been a headache for me also.

    Fred

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

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    I noticed this post also. From what little I know of VBA, I believe that it is possible to replace some delimiter (I suggested *** in my original post) with Chr(10) and achieve the desired result, as was done in this macro.

    I was hoping for a "hidden feature" rather than a macro solution, since my VBA is so "basic" <img src=/S/smile.gif border=0 alt=smile width=15 height=15> But hopefully somebody who has encountered this (seemingly common) requirement before, will come up with a neat solution.

    Alan

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Alan,

    My VBA, especially for Excel, is pretty bad too (I do use VBA with Access). But JIMbythebay's posting that I referenced seemed like it could do the trick - if you know the right incantation <img src=/S/grin.gif border=0 alt=grin width=15 height=15> . So I'm hoping, too, that someone will pick up on that. I don't see how to do it without VBA for lots of cells using Excel's Find/Replace (which should be replaced with the Word version, which I think was the problem in the first posting I referenced.

    Fred

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Without getting too deep into the technicalities, one of the reasons that the Find/Replace options are so much broader in Word than Excel is that Word was written in a particular dialect of Basic whereas Excel was not - for reasons that are almost certainly off-topic.

    What puzzles me at the moment is the distinction that you appear to be drawing between a "hard return" Chr(10) and a "linebreak". I have heard the terms used interchangeably, but you may have different things in mind. If they are the same thing, as has already been said, all you need to do is to (very slightly) adapt the existing <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=103324&page=& view=&sb=&o=&vc=1#Post103324>code</A> as follows;

    For Each C In Selection.Cells
    NewStr = "" ' Start with empty replacement string
    If InStr(1, C.Text, <font color=red>"
    Gre

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

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Hi unkamunka

    The reason I've tried to distinguish between linebreak, hard return etc. relates to the original problem of a table cell containing paragraph marks in Word. Clearly, Excel treats paragraph marks in word cells differently to its own "internal" Alt + Enter linebreaks used within its own cells.

    If such a cell is pasted directly into Excel, it will appear as multiple cells, with the paragraph marks within the single Word cell being treated as delimiters between individual Excel cells. If, OTOH, several such cells are pasted into columns already formatted as "Text", the original cell structure of the Word table will be preserved, but the paragraph marks will be lost. That is, a Word table cell containing the text:

    These are words:
    dog, cat, bird
    These are numbers:
    123, 456
    789, 567

    will appear as:
    These are words:dog, cat, birdThese are numbers:123, 456789, 567

    The layout is lost. But it's quite possible to introduce "linebreaks" in Excel itself using the Alt + Enter key combination.

    I had hoped that the Alt + Enter combination might have a keycode say (Alt + numeric pad) that could be simply entered into the "Replace with" field of the find/replace function in exactly the way you used the

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Alan

    In "Excel-land" a new paragraph means a new cell! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    In broad terms, the issue is that text-based programs, such as Word, tend to leverage off the binary lower-level aspects of a computer's operations (where all the real work is done) in a completely different way from numeric-based programs, such as Excel. To some extent, there is a whole "text" interface with the 0s and 1s of the lower level operating system. Because this "text" interface is so comprehensively drawn, Word's Find/Replace engine can be so powerful; whereas its advanced formula capabilities (in detailed tables) are somewhat limited. AFAIK, reworking the Excel Find/Replace engine to incorporate the range of choices available in Word would require tinkering with the "core program". Given the number of changes to the superstructure of Excel over the years, I suspect that Microsoft would approach such a project with extreme caution.

    You can attach the customised macro to an icon on a toolbar. (Tools|Customise; right-click on your custom icon and choose Assign Macro.) If you have a large number of Word Tables, or are going to have to do this frequently, maybe you could use an Excel template for pasting the initial documentation in; rather than clicking your way through the import engine.

    HTH
    Gre

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table (2000)

    Alan,

    I noticed your exchange with unkamunka. Interesting distinctions between Word and Excel and their interactions with the OS. I didn't quite follow what you were saying about formatting the receiving Excel cells as text vs as numbers. I think in either case that a pasted Word table won't be received properly in Excel if the goal is to maintain 1 Excel row per 1 Word table row, although it seems the (undesired) results depend on the Excel formating of the receiving cells.

    I've had the same concern as you did on importing a Word table into Excel, so I decided to take your challenge. This was done in Excel 97 but should run in later versions. I did a fair amount of testing per the Word table. But note my earlier warning - my Excel VBA is not that good. However, this code used (almost) no Excel object-model items. It's just general VBA. If I had to reference an Excel object, I'd be pretty-much lost.

    The file attached is a zip file with:
    - my source Word document
    - an Excel file with the macro.

    I hope the Excel file is straightforward in terms of its use. The sheets just show illustrations but the main things are:
    - I set it up so that you have to define the replacement string in a 1-cell range named replace_string somewhere in the workbook. But the code has commented lines if you want to do it there.
    - select the cells to run the macro on; there's a keyboard shortcut of CTRL+t

    I took a slightly different approach than JIMbythebay (and unkamunka's alteration of that code). Rather than checking each character, I realized that you could just search for the replacement string. When you find it, you just want to concatenate everything before that to the new string and replace the replacement string with chr(10) - the alt+enter. In the case of importing a Word table (with either hard returns created by pressing Enter or soft returns created by pressing Shift+Enter), it is likely that the replacement string for the Enter will be several characters rather than a single character. Of course, I have to take care of the case when there is valid text after the last enter. This means there are no more enters left to find but you don't want to leave off any text after the last enter.

    A few other interesting things (maybe useful to post as a separate item)
    - I thought Excel had a limit of 256 chars/cell. I wanted to see what happened if I exceeded this. So I had a word table cell with >400 characters. When I pasted this into Excel, it didn't seem to bat an eyelash. Hmmm...
    - when I got the big cell, I stretched the row so I could see the entire cell contents. If the col is narrow enough, you will find Excel 97's limit as to how big a row can be: 409.50 points.
    - an interesting 'bug' in Word on its word count - which I'll post there.

    Post if any questions. If anyone improves on the code, let us know.

    Fred

    PS: worth cross-referencing on the Word lounge? I'll do it.
    Attached Files Attached Files

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

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Hi Fred

    Many thanks for your efforts on this problem. My sequence of posts was somewhat disjoint, since I was trying to solve several problems at once. The "formatting the receiving Excel cells as text vs as numbers" was a separate issue, relating to pasting long codes such as 598832001683 into cells and avoiding having Excel convert to scientific notation.

    I tried your code and it looks like exactly what I am after. I will certainly try to apply it to the problem at hand. Many thanks.

    Further to the discussion with unkamunka regarding the lower level operation of Word/Excel, I can see (without understanding much of what's involved) that my "simple wish" for "special characters" in the search/replace function might not be so simple to implement.

    But having seen your macro, I imagine it *would* be simple enough to include this kind of functionality in a macro, tool etc. as opposed to a lower level functionality. It could operate, perhaps, as an alternative search/replace dialog, offering a dropdown list of "special characters" for the find & replace fields. This could include the chr(10) linebreaks of course. The macro could then use the dialog selections as internal variables.

    That said, I "lost contact" with macros after Word Basic was replaced with VBA, so I'm rather out of touch with what can/can't be achieved.

    Your input to this thread, as well as that of the other contributors, has given me a solution to my problem, as well as some (sorely needed) understanding of some of the difficulties involved.

    Many thanks to all.

    Alan

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Hi Alan,

    I'm still not convinced why Excel's find/replace can't handle special characters and be more like Word's. However, that is something to contemplate another day.

    As far as the macro goes, it would not be hard to have added another input to it that says what to replace with (the current input is basically the "what" of find/replace). That could have been in another cell. Using the char() built-in function, you could enter anything you want into a cell. Also interestingly enough, if you put an alt-enter or a char(10) [which is what I used for alt-enter in the macro] into a cell, copy it to Word, it gets converted into char(13).

    Fred

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

    Re: Import MSWord table (2000)-MULTI-Import MSWord table

    Hi Fred

    I also remain unconvinced that this kind of search/replace is a technical limitation, rather than an oversight/omission. I note that it's possible to use "special characters" entered via the Alt + numeric keypad combinations, so unless an internal linebreak within a cell is not such a character, it should be easy. As said though, there may be other issues involved.

    I also would have thought that such functionality would have become an issue when packages became bundled as Office suites, with a supposedly more common interface and features. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Interesting about the char 10/char 13 conversion. From memory, the traditional DOS interpretations of these are new line for char(10) and carriage return for char(13). Obviously they have been adopted to mean CR+LF in each of Word and Excel. BTW, how did you identify it as char(13) in Word? Another macro I guess <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    cheers

    Alan

Posting Permissions

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