Remove space and NewLine character on Export to Excel

[ShanVel]

Hi folks,
Can someone help me here? I have a field with memo data type where I store like 1500 charcaters. I have a command button which when the user clicks export a query to Excel and and format it. The problem is it puts a tiny square on CR and newline as follows:

For the guaranteed value of 0.003 the risk is low.□
□
He has not requested at this time. This was likely omitted.□
□
The following note will be included in the guarantee conditions: □
Following is the code line which takes the access data from the filed and put it in Excel cell:
objWS.Cells(intRowCount, intColCount).Value = fld.Value

I tried to use the trim function (Trim (fld.Value)) and it didn't work.

Any help is greatly appreciated.

Shan.

 

[databasedonr]

Have you had any success with this? I am trying to do something similar (almost exactly the same, actually), but in my spreadsheet I get "#VALUE" rather than any text. I'd be delighted to hear about a solution ....

 

[databasedonr]

Solution, methinks!

Hi Shan, and any others trying to do this, I've found a solution, maybe not the best, but it is working for me....

When I tried to do this, I would get "#VALUE" in my spreadsheet, and this must be based purely on the length of my data.

I tried writing the value of the field in my record set to a string, and then wrote the value of the string to the spreadsheet. My problem was solved, although the CR Newline characters are still in the string, I now have data in my spreadsheet.

Hope this helps.

Don