Enter (Carriage return) difference Excel/Word

[VBA-Bumbler]

I searched for some help on this but couldn't find anything similar. I hope this is the right forum for this question.
I have an Excel app that opens a Word document which then copies the contents of the last selected cell from Excel to the document, and has a macro to store the text back in the cell when it is done. I have two issues:
1) There appears to be a difference between the CRLF in Excel and Word. When I store text from Word into an Excel cell that contains paragraph marks created while in Word they appear as rectangular boxes in the Excel cell but do not cause a carriage return line feed the way the "Alt-Enter" combination does. When I edit a cell in Excel with these boxes and interject "Alt-Enter"s there they appear, by their action and presentation, to be different but I created a series of Code statements to examine the characters in the cell and they all return a character code of 13 (consistent with the code I would use in Word VBA code to cause a CRLF). When the text is copied to Word they act like regular paragraph marks. and get converted to the rectangles when stored back in the Excel cell. I have attached two files; the workbook and a template. If you save them to any folder (but they have to both be in the same folder) they will work as long as you have only this one workbook open (no other instances of Excel or other workbooks open). Open the workbook and review my other comments below. Then let the macro attached to the "Edit" button create a Word document from the template to experiment. There is no real need to open the template directly. Cell A4 shows a comment with 4 "Enter"s after it. Cell A6 shows 4 "Enter"s I created in the Word doc and then edited in Excel to add "Alt-Enter" after the second, third, and fourth one. You can see that they look different but A8-A13 show they all have a code of 13. My machine is so slow that I can see this cell change in the Excel formula bar to 5 rectangular boxes while the macro is running prior to the Word window coming up but I don't know what that means. Does anybody know what is happening here? I'd like to have the paragraph marks in Word turn into "Alt-enter"s in Excel so as much of the comment as possible appears the same in Excel as it does in Word.

2) I'd would love to hear from anyone who knows a better way to do this but so far this is the only way I have found to address some issues my users are complaining about. One issue is a cell being able to hold 32,767 characters but display only 1,024. Even that is sometimes cut off because the autofit row height doesn't expand enough to show the whole 1024 characters. Another is the spell checker but I found an article that would allow the use of the Word spell checker in Excel. The user has asked for a "Word or Word like window to edit the text" and I'm trying to accommodate. I'm even open to using another application.
Thanks for any help or ideas you can offer!

[VBA-Bumbler]

Here's the second file, the template. Since I can't attach a .dot file I changed the extension to .doc You will need to change it to.dot for the example to work.
Thanks

[HansV]

1. Use this line in the On Click code of the command button in the template:

oXL.ActiveSheet.Range("A" & eRow).Value = Replace(Selection.Text, vbCr, vbLf)

This replaces the Cr (character 13) that Word uses as paragraph end to the Lf (character 10)that Excel uses as line end.

2. You won't want to hear this, but in my opinion, you and your users shouldn't expect Excel to behave like a word processor, even with assistance from Word. You could put a text box (from the Drawing toolbar) on the worksheet, or a Word object (Insert | Object..., New tab, Microsoft Word Document)

[BakerMan]

Is it possible to extract data from a WORD document, in FORM format, into an excel sheet so the data can be stored in a data base in Excel?

[sdckapr]

you can read from word and fo various things to parse the data an put into various cells.

If it is in a table in word, copy/paste usually will work directly. If it is just text it can take more manipulation, though copy/paste and a text to columns might work.

Could you provide an example word file and how you want the excel "database" to look (before you add the items, with some sample data and after you "import" the word items) so that we have an idea of what you are trying to accomplish

Steve

[BakerMan]

This is a very simple example the actual word form has many more entries but is set up this way. The excel sheet will follow. it is also very simple.

[BakerMan]

Here is the excel sheet. I want to get the info from the word sheet to the excel sheet without manually copy/paste

[HansV]

1. Why do you want these data in Excel at all? It's just text, so Word would seem much more suitable than Excel.
2. If you do need them in Excel, why not use a table format in Word too? It's very easy to copy a Word table into Excel.

[sdckapr]

You can try some code like this. Change the name of the file as desired. You could use GetOpenFilename if you want to find a particular file.
I used the bookmarks object in Word to get the items (I rarely use WORD, this is from trial and error). I was not not sure why FORMTEXT was in the "text" of the range, but this seemed to work. You might need some WORD VB help to do the coding using wrdApp since I am very limited.

Steve

<pre>Option Explicit
Sub ExampleCode()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdBM As Word.Bookmark
Dim lRow As Long
Dim iCol As Integer
Dim bNotActive As Boolean
Dim sTemp As String
Dim AWF As WorksheetFunction

On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
On Error GoTo ErrHandler

lRow = Range("b65536").End(xlUp).Row + 1
bNotActive = False
Set AWF = Application.WorksheetFunction
If wrdApp Is Nothing Then
Set wrdApp = CreateObject("Word.Application")
If wrdApp Is Nothing Then
MsgBox "Problem starting Word."
Exit Sub
End If
bNotActive = True
End If

With wrdApp
Set wrdDoc = .Documents.Open("C:wordexampleform.doc")

If .ActiveDocument.ProtectionType <> wdNoProtection Then _
.ActiveDocument.Unprotect

For iCol = 1 To 3
sTemp = .ActiveDocument.Bookmarks(iCol).Range.Text
Cells(lRow, iCol + 1) = AWF.Substitute(sTemp, " FORMTEXT ", "")
Next
End With
ExitHandler:
wrdDoc.Close SaveChanges:=wdDoNotSaveChanges
If bNotActive And Not (wrdApp Is Nothing) Then _
wrdApp.Quit
Set wrdApp = Nothing
Set wrdDoc = Nothing
Exit Sub

ErrHandler:
On Error Resume Next
MsgBox Err.Description
Resume ExitHandler
End Sub</pre>

[BakerMan]

Thanks for the reply. The reason we need this in Excel is mostly lack of WORD knowledge by the crew where I work. The form is a report that is completed and sent in from various locations. We need to extract the data from each form that is sent in and arrange it in a data base in order to manipulate the data for charts reports etc. I know nothing about word tables. Thanks again.

[VBA-Bumbler]

Han's, you are the MAN!!! Thanks again for understanding my problem and giving exactly the right answer! It works like a charm. Now that you put me on the right path I see the mistake I made in my CODE() formula (1,C8 instead of C8,1). If I had entered that correctly I might have seen the problem but I still wouldn't have come up a solution as elegant as yours.
I couldn't agree with you more that the users are expecting too much from Excel in terms of word processing. We have started an initiative to examine other technology for a complete re-write of the app and they are going to consider whatever a group of us can present. If you have any ideas, please let me know. I have some other people from my company doing some investigation and we are going to look at another application that was developed in house that may be able to be modified to do the job. Unfortunately, in the short term, we are stuck with what we have and the pile of requests to make it better.
I am intrigued by the Insert Object concept and will give it a try and let you know what I find.
Thanks again for this fix.

[VBA-Bumbler]

Oh how I love a challenge! That said, I have been working with the insert Word object in a spreadsheet. Talk about annoyances. There could be a whole book written about this. If you know of a good book on the subject, please let me know. I tried searching in a number of places but can't find much about it.
I created a sheet with a series of Word objects. The objects seem to size themselves according to the amount of text entered into them. That's good but the rows don't size automatically even though I have AutoFit on. When I click on an object it gains focus but I have to double click to open the Word object (which changes the toolbar and allows access to the Word Vb Editor). I thought it would be fairly easy to create event macros to handle some of these things. I can create a _Click macro for each object so I can open the document on a single click with this code:
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
Now when I click on one object it opens fine. If I click a cell, then click another object, it opens fine. If I try to click on another object while one is open it doesn't work. The new object seems to open fine but then reverts to a selected state in Excel. When I walk through the code the point where the change back to Excel seems to occur is after the last line of code in the _Click macro. Any ideas?
I was able to get _Change, _Activate, _DeActivate, and , _SelectionChange events to fire but nothing consistently fires when a particular object is closed. I guess I'm looking for a _Exit, _Close, or _Leave type event where I can put code to resize the row based on the new height of the object.
I tried creating event macros in the Word VB Editor figuring I might be able to capture the close but I can't seem to get any event (_New, _Open or _Close) to fire even if I change the first verb above to xlOpen so the document opens in a Word window instead of the Excel window. I am beginning to understand your strong objection to allowing users to edit text in Excel but this interface seems to offer so much promise. Can these problems be overcome or am I beating a dead horse here?
Thanks

[HansV]

I don't think I can offer useful suggestions, sorry. You already know my opinion, so I won't repeat it here.

[VBA-Bumbler]

Thanks anyway. I'm just about to abandon this approach.