Home › Forums › eaDocX queries › Carriage return handling between Excel and EA
Home › Forums › eaDocX queries › Carriage return handling between Excel and EA
- This topic has 3 replies, 2 voices, and was last updated 10 years, 9 months ago by Guillaume Finance.
-
AuthorPosts
-
6 February 2014 at 3:33 pm #7307Guillaume FinanceParticipant
Hi
I imported a number of requirements (name + ref + description) using eaXL.
When I open one of the imported requirements in EA, carriage returns are all stripped out from the Notes/description.
I’m looking for the right way to format the cells in Excel where the description is copied (text including carriage returns).I tried to find a solution by exporting a description from EA to Excel. In Excel,I added characters to the end of a line and exported the requirement back to EA -> result: the carriage return is stripped out.
Thanks,
Guillaume6 February 2014 at 5:54 pm #7308Adrian SupportParticipantHi Guillaume,
I have checked your scenarios, and what you describe does happen. In looking at the code we get the cell back from Excel complete with CR/LF’s that we may have put in e.g. on export. However, I noticed that if I did a cut and paste from a document to a cell it looks like Excel removes the CR, and it looks like this is an issue for EA. I did some further digging and indeed Excel will on data entry substitute LF to replace CR + LF!!
To verify I did a test by checking for LF’s and adding a CR, if not present. and that seemed to do the trick.
Before making any changes I’ll have to work through other scenarios and do some checking but looks like there may be a solution in the future.
But back to the now – not sure how to force Excel to include both CR/LF in the cell.
I’ll take a look and see whether i can find a way in Excel e.g. a macro that could be run to reintroduce the CR as it looks that we have no issues with exporting CR LF into a cell – it seems to be the cut and paste of data into the cells.
Regards
Adrian
6 February 2014 at 6:29 pm #7309Adrian SupportParticipantHi Guillaume
Further to my previous I’ve just done a quick check and produced a small VBA function that may be a temporary workaround to address the issue with Excel stripping the CR from CRLF which EA needs.
The approach is to enter your data into a dummy column and using a VBA function modify the string that will be in the description field for import.
So –
- Add a VBA module to the workbook – see code below {/li]
- Put your notes into the relevant cell
- In the Description put the function =addCR(Cell where the notes are) e.g. =addCR(F5) – this should created a copy of the notes that you have in that cell and you want to import – you won’t see any visual difference as the function just inserts control characters
Create another column BUT DO NOT NAME IT – otherwise it will be recognised as a tagged value!
You should then be able to import and get the CRLF as you expected.
Here is the code – I suggest you inspect and test as this is a 10min job – but thought you’d like the potential workaround quickly.
[code]
Function addCR(a As String)
Dim L As Integer
L = Len(a)
Dim sout As String
Dim s2 As String
For i = 1 To L
s2 = Mid(a, i, 1)
If Asc(s2) = 10 Then
sout = sout + Chr(13)
End If
sout = sout + s2
Next
addCR = sout
End Function
[/code]Any issues let me know.
we will look into a longer term solution – but hope this helps today.
BR
Adrian
- This reply was modified 10 years, 9 months ago by Adrian Support.
7 February 2014 at 2:15 am #7311Guillaume FinanceParticipantHi Adrian
The VBA function worked well 🙂
I can use it until this issue is fixed in a future release.Thanks,
Guillaume -
AuthorPosts
- You must be logged in to reply to this topic.
Download a free trial
Download eaTeamWorks today for several free for life features, plus no obligation, 30-day trials of all the products: eaDocX, ea Revision Manager, eaSheets, Model Expert and PortfolioManager. Discover for yourself why we sell the world’s best-selling Enterprise Architect extension.
Download