remove all non keyboard characters using a vba module

[TimTDP]

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?

 

[mdlueck]

Perhaps build a VBA Function to sanitize the data. Here is some sample code which validates that a provided value is numeric only. Expand the allowed characters in the For/Next loop to allow the characters you want included.

Also, you need to collect up in an output variable each valid character, and return the sanitized data rather than a Boolean.

Public Function datatypevalidation_RequiredNumericOnly(ByRef vntExpression As Variant) As Boolean
  On Error GoTo Err_datatypevalidation_RequiredNumericOnly

  Dim lngMaxStep As Long
  Dim lngStep As Long
  Dim intThisChar As Integer

  'Assume invalid data
  datatypevalidation_RequiredNumericOnly = False

 'Size up how many characters / digits in the variable we were passed
  lngMaxStep = Len(vntExpression)

  'Go through one character at a time, validate 0-9
  For lngStep = 1 To lngMaxStep
    'Grab the next character...
    intThisChar = Asc(Mid(vntExpression, lngStep, 1))

    'Perhaps it is a number...
    If (intThisChar >= 48) And (intThisChar <= 57) Then
      'Valid character, do nothing...
    Else
      'Out of valid options, must not be valid
      GoTo Exit_datatypevalidation_RequiredNumericOnly
    End If
  Next lngStep

  'Checks out valid
  datatypevalidation_RequiredNumericOnly = True

Exit_datatypevalidation_RequiredNumericOnly:
  Exit Function

Err_datatypevalidation_RequiredNumericOnly:
  Call errorhandler_MsgBox("Module: modshared_datatypevalidation, Function: datatypevalidation_RequiredNumericOnly()")
  Resume Exit_datatypevalidation_RequiredNumericOnly

End Function

 

[gemma-the-husky]

chr(32) is a space, not a non printing character

carriage return / linefeed (ie a line break) is chr(13) chr(10)
you may need these.



you can trim surplus spaces with trim, ltrim, and rtrim functions