在EXCEL中用VBA替换CHR(160)时出现问题 [英] Trouble replacing Chr(160) with VBA in excel

问题描述

我收到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

现在我做了一些研究，发现要替换这些字符，您可以使用CHR(160)。我已经在我的Replace中使用了它，但它似乎没有起到作用，帐户仍然上载了那些可怕的特殊字符。如有任何帮助，将不胜感激。

推荐答案

以下是我解决该问题的方法。我创建了一个函数来帮助处理这种情况，现在它起作用了。

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

这篇关于在EXCEL中用VBA替换CHR(160)时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！