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?

    <FONT size=2>

    Str = .GetText(0, Counter)   <FONT color=#ff0000>[ here the value is "2609*"]</FONT>

    .SetText(0, Counter, GetWildCard(.GetText(0, Counter))) </FONT><FONT color=#008000 size=2>'replace * character with % character

    </FONT><FONT size=2>

    Str = .GetText(0, Counter)  <FONT color=#ff0000>[here the value is "2609 % - there is a space between 2609 and %]</FONT>


    <FONT size=2>

    </FONT><FONT color=#0000ff size=2>Public</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Function</FONT><FONT size=2> GetWildCard(</FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> StringToFix </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String</FONT><FONT size=2>) </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>String

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>If</FONT><FONT size=2> StringToFix <> </FONT><FONT color=#a31515 size=2>""</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Then

    </FONT><FONT size=2>

    StringToFix = Replace(Trim(StringToFix), </FONT><FONT color=#a31515 size=2>"*"</FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2>"%"</FONT><FONT size=2>)

    StringToFix = Replace(Trim(StringToFix), </FONT><FONT color=#a31515 size=2>"'"</FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2>"''"</FONT><FONT size=2>)

    </FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>If

    </FONT><FONT size=2>

    GetWildCard = StringToFix <FONT color=#ff0000>[here the value is "2609%"]</FONT>

    </FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>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