CODEWITHSUNDEEP
excelvba
BCLtd
BCLtd

Reputation: 1461

Insert Data With Carriage Returns

I have a command button that inserts data in to a worksheet from a userform, it then saves it as a .csv.

We then load the csv data using another userform - However, the problem arises when a Carriage Returns is inserted into a text box and inserted. Obviously the first solution I can think of is to stop commas being entered - however, is there a better solution?

Upvotes: 0

Views: 74

Answers (1)

NeepNeepNeep
NeepNeepNeep

Reputation: 913

@Miguel has the right idea (his comment is on the main question thread).

This approach involves defining a list of integers relating to the ASCII codes for the characters you're having trouble with. Line feed (10) will definitely be in there and you can decide on carriage return (13) commas (44) or speech marks (34).

const ListOfSpecialChars = "10,13,34,44"

You'll need an 'encoding' proc that accepts a string and outputs a string. It would transform text in this kind of fashion: ab,cd"ef -> ab<<44>>cd<<34>>ef

This would be achieved by splitting the const and looping through each of the constituents, executing a replace:

For Each splitCharVal In Split(ListOfSpecialChars, ",")
    stringToEncode = Replace(stringToEncode, Chr(splitCharVal), "<<" & splitCharVal & ">>"
Next

You'll also need a 'decoding' proc that does the opposite, which I'll let you work out.

So, when saving a file to CSV, you'll need to loop through the cells of each row in turn, encoding the text found within, then writing out a row to the file.

When reading in a row from the encoded CSV, you'll need to run the decode operation prior to writing out the text to the worksheet.

Upvotes: 1

Related Questions