transfert a .doc to a .xls file 1

[bluelive]

I have a word document with formatted text and some tables.
I would like to transfert all (involved pages break) to an excel file. I've tried to do with copy paste but everything is copied inside the first cell in excel and some formatted elements as well as tables are not good applied.
I would like to find a solution with vba code instructions to transfert all correctly into excel.
Any Idea?
Thank for your help.

Remark: the final aspect would seems the same as if I did all from excel.

 

[RobBroekhuis]

The answer will depend on what your Word document looks like, and how you want those tables, formats, etc to transfer. As a first approach, you can do the copy - paste like you already did, and use Data - TextToColumns in Excel to get the data from the first column formatted into several columns, according to your parsing instructions. That's fairly easy to code. Anything beyond that becomes a complex project.


Rob

 

[bluelive]

Well, I go back with further informations about the document. This one is composed from 1 to n pages (depending of some conditions and data from AS/400). I use ITP to produce word documents.
Let say that the result document from ITP program is a word document about 5 pages (with pages break). Each page has differents fonts with mixed of bold, underline, ... + tables with 2, 3 or more colums. Header colums are formatted with bigger fonts and colors, ....
The page layout is fixed to landscape.

From that document, if I do a copy paste through a macro, it is hugly. What I do for now, is transferring the .doc to an html file then create an Excel object and open the html document and save it to an excel document with vba codes.

I would like to get the code for reading each line of my word document and copy it to an excel sheet without loosing anything (or perhaps is there any other way to go quickly). As I said, the final aspect must look the same if I did all directly in excel...

(sorry for my poor english, As you certainly seen, I'm french).

 

[dbsquared]

Can you save it in a .txt for example. If so then you can use Excel to open the doc and that brings up the text import wizard (I would record this section if you repeat this function often and put in your code) choose the format you want to see and where you want to be the seperators for the columns. and see if that works for you.



To go where no programmer has gone before.

 

[RobBroekhuis]

Doing a direct VBA translation of Word to Excel is a large undertaking. The way documents are structured in Word is very different from workbooks in Excel. It can certainly be done, but, once again, the details depend on the exact format of your Word document. It might be easier to rely on information you know a priori (such as fonts, sizes, etc), rather than trying to read that information from Word.
Do you want the different pages in Word to go to different worksheets in your Excel book?


Rob

 

[bluelive]

Well,

I found a solution.
First, all the "fixed text" are done in an excel sheet. I save it as a "template".
Using my result .doc file (I said result, because the data writted in the doc file come from AS/400 with the help of ITP technology to produce doc file for retrieving AS/400 data values,...).
Inside my word document, I use VBa codes to transfert each line to the .xls template (the one I did before).
To known where to place my values, I divided 'virtually' my xls template into several parts. In front of each line writted in my doc file I put a pseudo-index key automatically (done when ITP produces the doc file).
Using this index key, I know where to place my text inside my .xls....

"Maybe a little too hard to explain in full details + in english, but the result is what I intended!).

Last note, all this process is done on a batch document server, thus the solution must be fully automatic.

Thank to all for your answers and the time you spend for me. I print all of your answers for later use, if needed.

 

[VBAjedi]

ROB!

Are you back, or just making a driveby appearance?

VBAjedi

 

[RobBroekhuis]

Work is a tad bit slow, so I'm just stopping by for a few days. Don't expect to see me up in the rankings, though


Rob

 

[VBAjedi]

Good to hear from you, anyway. It was a sad day when the guy that taught me so much about clear, concise VBA signed off. . . it was a bit like when Obie-Wan died in Star Wars!

LOL

Have a star for old times sake!




VBAjedi

 

[RobBroekhuis]

I still got y'all bookmarked ;-)


Rob