Can someone please help? Below is the code I'm trying to use.

I have an excel file that has ascii characters I want removed and when I debug the following code it's showing the removal on the immediate window....however, it's not changing the string in excel....If someone can please, please point me in the right direction I'd greatly appreciate it.

Thanks!


Option Explicit
Private Sub Main()
ABSAscii
End Sub

Public Function ABSAscii()

' Declare object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim lLen As Long
Dim sAns As String
Dim lCtr As Long
Dim sChar As String
Dim sAddress As String
Dim iCol As Integer
Dim iRow As Integer

Const cColumns As Integer = 9
Const cRows As Integer = 54

On Error GoTo ErrorHandler

'Create a new Excel Application Instance
Set xlApp = New Excel.Application
xlApp.Visible = True

'Open workbook with slApp
Set xlBook = xlApp.Workbooks.Open("c:\temp\testascii.xls", , , , , , , , , , False)
xlBook.Application.Visible = True
Set xlSheet = xlBook.Worksheets("RicksSouth")
For iRow = 2 To cRows
For iCol = 1 To cColumns
' Assign object references to the variables.


sAddress = Trim(xlSheet.Cells(iRow, iCol).Value)
Debug.Print sAddress

lLen = Len(sAddress)
For lCtr = 1 To lLen
sChar = Mid(sAddress, lCtr, 1)
If IsAlphaNumeric(Mid(sAddress, lCtr, 1)) Then
sAns = sAns & sChar
End If
Next
Debug.Print sAns
sAns = vbNullString
Next iCol
Next iRow


' Save the Worksheet.
xlSheet.SaveAs "c:\Temp.xls"

' Close the Workbook
xlBook.Close

Cleanup:

' Close Microsoft Excel with the Quit method.
xlApp.Quit

' Release the objects.
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing




Exit Function

ErrorHandler:

MsgBox Err.Description

Select Case Err.Number
Case 53
MsgBox "Just a test!", vbInformation, "Test"
Resume Next
Case Else

End Select

Resume Cleanup

End Function


Public Function IsAlphaNumeric(sChr As String) As Boolean
IsAlphaNumeric = sChr Like "[0-9A-Za-z-( )(.)(/)(!)(#)(&)(+)(,)(`)(~)( )(\)]"
End Function