Trouble replacing Chr(160) with VBA in excel
11,212
Here is what I did to solve it. I created a function to help with the situation and it now works.
Function CleanText(strText As String) As String
If strText = "" Then
CleanText = ""
Else
strText = Replace(strText, "'", "''")
strText = Replace(strText, " ", "")
strText = Replace(strText, vbTab, " ")
strText = Replace(strText, vbCrLf, " ")
strText = Replace(strText, Chr(160), "")
CleanText = strText
End If
End Function
Author by
user2405778
Updated on June 04, 2022Comments
-
user2405778 almost 2 years
I have been receiving excel files for awhile that are usually plagued with the special character alt+0160 after the accounts. I usually just manually replace it in excel but recently I've grown lazy and want to replace it using my VBA script. This script is used to insert the needed columns into our database:
Sub insert() Dim sSql As String Dim db As New ADODB.Connection 'Go to Tools, References and turn on ActiveX Data Objects 2.8 Library db.Open "DSN=XXXX;uid=XXXX;pwd=XXXX" 'INSERT ORACLE NAME AND PASSWORD For i = 2 To 92 'Change Rows where it starts and ends strAccount = Replace(Trim(Range("a" & i).Text), Chr(160), "") If IsNumeric(strAccount) Then While Len(strAccount) < 8 strAccount = "0" & strAccount Wend Else strAccount = UCase(strAccount) End If sSql = "insert into XXXXX (" sSql = sSql & " BATCH_ID" sSql = sSql & " , ACCOUNT " sSql = sSql & " , ATTORNEY_ID" sSql = sSql & " , ORG_ID" sSql = sSql & " , TRANSACTION_DATE" sSql = sSql & " , DATE_INSERTED" sSql = sSql & " , TRANSACTION_CODE" sSql = sSql & " , AMOUNT" sSql = sSql & " , DESCRIPTION" sSql = sSql & " , DEBTOR_SSN" sSql = sSql & ") VALUES (" sSql = sSql & " (SELECT MAX(BATCH_ID) FROM XXXX)" sSql = sSql & " , '" & strAccount & "'" sSql = sSql & " , (SELECT ATTY_ID FROM XXXX WHERE BATCH_ID = (SELECT MAX(BATCH_ID) FROM XXXXX))" sSql = sSql & " , (SELECT ORGANIZATION_ID FROM XXXX WHERE BATCH_ID = (SELECT MAX(BATCH_ID) FROM XXXXX))" sSql = sSql & " , TO_DATE ('" & Trim(Range("B" & i).Text) & "', 'MM/DD/YYYY') " sSql = sSql & " , SYSDATE" sSql = sSql & " , '" & Trim(Range("D" & i).Text) & "'" 'CHANGE TO COLUMN TRANSACTION CODE IS IN sSql = sSql & " , '" & Replace(Replace(Replace(Replace(Trim(Range("C" & i).Text), "$", ""), ",", ""), ")", ""), "(", "") & "'" sSql = sSql & " , '" & Replace(Trim(Range("H" & i).Text), "'", "''") & "'" sSql = sSql & " , '" & Replace(Replace(Trim(Range("F" & i).Text), " ", ""), "-", "") & "'" sSql = sSql & ")" db.Execute sSql DoEvents Debug.Print i Next i End Sub
Now I did some research and found out that to replace these characters you use Chr(160). I have used that in my
Replace
but it does not seem to be doing the trick, the accounts still get uploaded with those terrible special characters. Any help would be greatly appreciated.