Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Enter (Carriage return) difference Excel/Word

    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!

  2. #2
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Enter (Carriage return) difference Excel/Word

    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

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

    Re: Enter (Carriage return) difference Excel/Word

    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)

  4. #4
    BakerMan
    Guest

    Re: Enter (Carriage return) difference Excel/Word

    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?

  5. #5
    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: Enter (Carriage return) difference Excel/Word

    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

  6. #6
    BakerMan
    Guest

    Re: Enter (Carriage return) difference Excel/Word

    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.

  7. #7
    BakerMan
    Guest

    Re: Enter (Carriage return) difference Excel/Word

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

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

    Re: Enter (Carriage return) difference Excel/Word

    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.

  9. #9
    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: Enter (Carriage return) difference Excel/Word

    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>


  10. #10
    BakerMan
    Guest

    Re: Enter (Carriage return) difference Excel/Word

    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.

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Enter (Carriage return) difference Excel/Word

    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.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Enter (Carriage return) difference Excel/Word

    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

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

    Re: Enter (Carriage return) difference Excel/Word

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

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Enter (Carriage return) difference Excel/Word

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

Posting Permissions

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