Carriage return handling between Excel and EA

[Guillaume Finance]

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,
Guillaume

[Adrian Support]

Hi 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

[Adrian Support]

Hi 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]

Create another column BUT DO NOT NAME IT – otherwise it will be recognised as a tagged value!

• 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

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, 10 months ago by Adrian Support.

[Guillaume Finance]

Hi Adrian

The VBA function worked well 🙂
I can use it until this issue is fixed in a future release.

Thanks,
Guillaume

[Author]

Posts