PDA

View Full Version : Some questions regarding word and excel



Sissyfoo
11-24-2005, 09:37 PM
I was wondering if anyone could give me some tips for a project I'm working on.

I am toying with the idea of importing word documents into a pre-built excel report. The trouble with these documents is that they do not have a standard layout and they change constantly from month to the month. The excel report that they would be imported into has a constant layout and cannot really be altered much.

If the reports were just text then I'd probably be fine but they also have tables which is causing a bit of trouble. I had toyed with the idea of opening the .docs, pulling all the data into a control textbox and then closing the .doc file. This works great for plain text (although I lose the formatting...no biggie) but it isn't really printer friendly if the textbox requires the use of scroll bars. It is terrible, however, for tables.

Is there a way that anyone can suggest to go through a .doc, pull out all the text and place it in a text box and then pull out any tables and insert them into excel?

Is it possible (making my life a tad more complicated, yay) to go through the .doc and export the text to a textbox but if it comes across a table in the range of paragraphs to export the table and place it into the spreadsheet and then create a new textbox and carry on exporting text?

For example, if the doc contained *text text text* *table* *text text text* then this would be exported to excel in the form *textbox* *table* *textbox*.

It isn't the end of the world if this can't be done. I was just curious to know whether or not it is possible and, if it is, I'll take a crack at it. If not, then the users will have to make do with hyperlinks to the .docs stored on some webserver somewhere. :)

Thanks,

James http://vbaexpress.com/forum/images/smilies/039.gif

Dave
11-24-2005, 11:30 PM
James...just to be clear, it is the values from the Word report you want, or are you using XL to make a report that cuts and pastes snippets of a Word doc. The method is likely dependent upon this. I'm pretty sure someone will be able to help but it seems fairly complex. Dave

Killian
11-25-2005, 03:56 AM
There are certainly a number of ways to approach this...
Looking at how the elements can be identified in Word, you can use Paragraphs.Count and Tables.Count on the document range to iterate through each item an transfer it across.
However, Paragraphs.Count will include the paras in the tables, so a way around this would be to deal with each table first, remove it and then deal with the paragraphsSet rngWordWholeDoc = ActiveDocument.Content
With rngWordWholeDoc
If .Tables.Count > 0 Then
For i = 1 To .Tables.Count
.Tables(i).Range.Cut
'paste into Excel tables worksheet
Next
End If
For i = 1 To .Paragraphs.Count
.Paragraphs(1).Range.Copy
'paste into Excel Text worksheet as textboxes
Next
End WithExactly how you handle each element depends on how you want to format the excel output - in the example, I've suggested having the Text and tables on seperate sheets - if you replace the tables in Word with a text marker when you Cut them (e.g. "TableLink " & i) and use the same string to name the table's range in Excel, you can then sort out the Excel items by replacing textboxes with the appropriate table or provide a link.

fumei
11-25-2005, 06:32 PM
May I ask why the text from the Word doc is going to go into a textbox? I mean, why does it have to go into a shape, rather than simply a cell?