在 Excel 中用 VBA 替换 Chr(160) 时遇到问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20620293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trouble replacing Chr(160) with VBA in excel
提问by user2405778
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:
我已经收到了一段时间的 excel 文件，这些文件通常在帐户后受到特殊字符 alt+0160 的困扰。我通常只是在 excel 中手动替换它，但最近我变得懒惰并想使用我的 VBA 脚本替换它。此脚本用于将所需的列插入到我们的数据库中：
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 Replacebut 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.
现在我做了一些研究，发现要替换这些字符，您可以使用 Chr(160)。我在我的中使用过它，Replace但它似乎并没有起到作用，帐户仍然会上传那些可怕的特殊字符。任何帮助将不胜感激。
采纳答案by user2405778
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
