remove all non keyboard characters using a vba module 1

[DevelopV]

I have code that exports data to a csv file.
When I open the csv file with NotePad++ I notice that the last character in some of the fields, in some of the records, may be a non keyboard character. Examples of characters returned include a carriage return, ascii character 32

How can I remove all non keyboard characters using a vba module?

 

[Andrzejek]

a carriage return, ascii character 32"
Don't you think a carriage return should be at the end of each line?

And an ascii character 32 is a Space.



Have fun.

---- Andy

 

[PHV]

Have a look at the Replace function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DevelopV]

Don't you think a carriage return should be at the end of each line?
Yes it should be, but ONLY at the end of the line!

an extract of my csv file is:

"CustomerContactId","CustomerID","ContactPersonFirstName","ContactPersonLastName","UserName","UserPassword"CR LF
9,1364,"RUTH",,"rapunzels","ruth"CR LF
3001,2099379,"Marnette","LoubserCR
","marnetteCR
","marnette01"CR LF
2133,2098654,"Karen","Rousseau ","salon rouss","69chickens"CR LF

I have made the CR & LF bold.
Look at the line 3001,2099379......
There should not be a CR after Loubser or marnette
In fact this record should be on one line, not 3

In line 2133,2098654.....
after the word "Rousseau " NotePad++ shows a series of spaces (there are about 14 of them)

I have following code

Function RemoveCR(strData As String) As String

'Last character cannot be a carriage return
Do Until right(strData, 1) <> vbCr
    RemoveCR = Left(strData, Len(strData) - 1)
Loop

'Last character cannot be a space
Do Until right(strData, 1) <> " "
    RemoveCR = Left(strData, Len(strData) - 1)
Loop

End Function

the code

Do Until right(strData, 1) <> " "
    RemoveCR = Left(strData, Len(strData) - 1)
Loop

just runs in a continuous loop. The space is not removed!

These are just two examples that I have found in my csv file!
I need to modify my function to ensure that all characters in the field are a keyboard character (can be entered from the keyboard), and that the last character is not a space.

How should I do this?

Many thanks

 

[Andrzejek]

I would use PHV's suggestion with Replace function, but more than that...

You said: "I have code that exports data to a csv file." So you are putting all those unnecessary characters into your csv file. I would modify the “code that exports data to a csv file” to have a clean csv file.


Have fun.

---- Andy

 

[Andrzejek]

BTW, this code:

[green]'Last character cannot be a space[/green]
Do Until right(strData, 1) <> " "
    RemoveCR = Left(strData, Len(strData) - 1)
Loop

could be simple:

RemoveCR = Trim(strData)

Have fun.

---- Andy