Consulting

Results 1 to 9 of 9

Thread: Format of the Cell needs to be Text type using Visual Basic

  1. #1
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location

    Format of the Cell needs to be Text type using Visual Basic

    Hi ,

    I have created one .xls sheet and inserted some data in one column using Visual Basic code.Unfortunately some datas are entered in Scientific format in the cells.Is there any property in Visual Basic excel reference which will help to format the cells as text tyep not anyother type.

    thanks for the respose.

    Regards
    Nutana

  2. #2
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    You can convert the value to a string before filling in the cell:
    range("A1").value = cstr(value)
    You can add ' before your value:

    range("A1").value = "'" & value
    Another solution could be setting the format of the cell to text before filling in the value:

     
    Range("A1").NumberFormat = "@"
    Hope this helps.

  3. #3
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    Thanks Gollem for the reply ,i just wanted to know what is the "Range"?

    Here is my code:

    Dim oXLApp As Excel.Application 'Declare the object variables
    Dim oXLBook As Excel.Workbook
    Dim my_variable As String
    Dim oXLSheet As Excel.Worksheet
    Set oXLApp = New Excel.Application 'Create a new instance of Excel
    Set oXLBook = oXLApp.Workbooks.Open(filexlspath) 'Open an existing workbook
    Set oXLSheet = oXLBook.Worksheets(1)
    oXLSheet.Cells(i, 1).Value = Mid(kline, 1, 8) 'Kline is variable of string type.

    where i can put the
    Range("A1").NumberFormat = "@"

    Thanks for helping me.
    Nutana

  4. #4
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    Hi,

    it's an alternative for cells. Range("A1") refers to cell A1 in excel.

    Try this:

     
    ...
    oXLSheet.Cells(i, 1).NumberFormat = "@"
    oXLSheet.Cells(i, 1).Value = Mid(kline, 1, 8) 'Kline is variable of string type.
    or

     
    ...
    oXLSheet.Cells(i, 1).Value = "'" & Mid(kline, 1, 8) 'Kline is variable of string type.
    Check what gives the best result for you.
    Hope this helps.

  5. #5
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    hi,

    it still not giving the correct ouput for me.
    strange



    Nutana

  6. #6
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    What text is in your variable kline?
    What output do you get and what output do you need?

  7. #7
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    hi,

    The Kline is string valriable ,and the below format its defined.
    Dim UIMFilename, kline, FileName, filexlspath As String


    original value :00000880
    value in .xls file:880 (i am getting)

    and one more value also i have tried.

    original value :30592E80
    value in .xls file:
    3.06E+84
    (i am getting)

    Nutana

  8. #8
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    Hi,

    my second solution should work, I've tested it. For me it's working:

     
    oXLSheet.Cells(i, 1).Value = "'" & kline 'Kline is variable of string type.
    Try it also without the mid function, your complete string should be in the cell.

  9. #9
    VBAX Newbie
    Joined
    May 2010
    Posts
    5
    Location
    Quote Originally Posted by Gollem
    Hi,

    my second solution should work, I've tested it. For me it's working:

     
    oXLSheet.Cells(i, 1).Value = "'" & kline 'Kline is variable of string type.
    Try it also without the mid function, your complete string should be in the cell.
    hi Gollem,

    Unfortunately this also not working in my system.
    Please help.

    Regards
    Nutana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •