SetText is adding a space to the text... I think

Posted by: ginacresse on 8 September 2017, 12:52 pm EST

  • Posted 8 September 2017, 12:52 pm EST


    In the following code, there is a value of "2609*" in the cell I'm checking.  Before doing anything with the value, I change the * to a % for a SQL search but the .SetText command seems to put a space before the % character (I stepped through the code and checked values at each point), which causes my SQL select statement to find no matches.  Is there something I don't understand about .SetText?

    Str = .GetText(0, Counter)   [ here the value is "2609*"]

    .SetText(0, Counter, GetWildCard(.GetText(0, Counter))) 'replace * character with % character

    Str = .GetText(0, Counter)  [here the value is "2609 % - there is a space between 2609 and %]


    Public Function GetWildCard(ByVal StringToFix As String) As String

    If StringToFix <> "" Then

    StringToFix = Replace(Trim(StringToFix), "*", "%")

    StringToFix = Replace(Trim(StringToFix), "'", "''")

    End If

    GetWildCard = StringToFix [here the value is "2609%"]

    End Function

  • Replied 8 September 2017, 12:52 pm EST


    The Get/SetText methods convert the raw cell value to/from a text represent of the cell value.  The conversion process uses the Format/Parse method of the cell's CellType object.  You did not mention what CellType was involved.  I am assuming the cell has the GeneralCellType (which is the default CellType).  The cell originally contains the string value "2609*".  Your application calls GetText which calls the GeneralCellType's Format method which returns the string value "2609*".  Your application then changes the string to "2609%".  Your application then calls SetText which calls the GeneralCellType's Parse method returns the number 26.09 (i.e. 2609% is recognized as a numeric percentage where 2609% = 2609/100 = 26.09).  The number 26.09 is is stored in the cell.  Your application then calls GetText a second time which calls GeneralCellType's Format method which returns "2609 %" (i.e. number 26.09 has been reformatted as a percentage).

    Since you are interested in keeping the cell values as strings, your application should use the Get/SetValue methods which retrieve/assign the raw value.  Note that the Get/SetValue method retrives/assigns the value using the Object data type, so you will need to cast the retrieved value from Object to String.

  • Replied 8 September 2017, 12:52 pm EST

    Thanks, Bob.  This fixed my problem.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels