I have searched all over and have not been able to find a way to solve my problem. In short, we use text files to save customer information that was created in userforms in Excel (save button on the userform will create a text file. My problem is that the original code created text files that had a delimiter, which I have since changed but there are older text files out ther that users are importing where the delimiter causes a problem.
The problem is caused by a text box that I created where the user can put in "Notes" about the customer such as credit ratings, stock symbol, etc. and they have copied and pasted from an Excel cell from another workbook where there are carriage returns in that cell (multiple lines in one cell). My current process will open up the text file from Excel and they will copy and paste the cells into my model. The old text files, if carriage returns were used, messes up my import cell references because it creates one more line per carriage return used in the original Excel sheet.
I think I can resolve this by having the user select the text file, open up a new workbook and then generate the Clean code below and then copy and paste the updated information into my model (hopefully without the user seeing anything other than selecting the text file.
For Each cl In ActiveSheet.UsedRange
cl.Value = Application.Clean(cl.Value)
Next
Any help would be appreciated. It seems like an easy task but getting frustrating.
The problem is caused by a text box that I created where the user can put in "Notes" about the customer such as credit ratings, stock symbol, etc. and they have copied and pasted from an Excel cell from another workbook where there are carriage returns in that cell (multiple lines in one cell). My current process will open up the text file from Excel and they will copy and paste the cells into my model. The old text files, if carriage returns were used, messes up my import cell references because it creates one more line per carriage return used in the original Excel sheet.
I think I can resolve this by having the user select the text file, open up a new workbook and then generate the Clean code below and then copy and paste the updated information into my model (hopefully without the user seeing anything other than selecting the text file.
For Each cl In ActiveSheet.UsedRange
cl.Value = Application.Clean(cl.Value)
Next
Any help would be appreciated. It seems like an easy task but getting frustrating.