Hi, I am getting data in which has multiple spaces in, I have tried using the following code which works in both VB5 and VB6, but does not appear to in VBA. Can anyone point me as how to amend this. Thanks


Public Function RemoveExtraSpaces(str As String) As String
' Removes Extra Spaces in between the words

str = Trim(str)

Dim L As Integer
Dim i As Integer
Dim S As String
Dim Prev_char As String * 1

S = ""

L = Len(str)
i = 1
Do
Prev_char = Mid(str, i, 1)
i = i + 1

S = S + Prev_char
If Prev_char = " " Then
Do While (i < L) And (Mid(str, i, 1) = " ")
i = i + 1
Loop
End If

Loop Until i > L
str = S
RemoveExtraSpaces = S

End Function