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

[Nutana]

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

[Gollem]

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.

[Nutana]

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

[Gollem]

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.

[Nutana]

hi,

it still not giving the correct ouput for me.
strange

:(

Nutana

[Gollem]

What text is in your variable kline?
What output do you get and what output do you need?

[Nutana]

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

[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.

[Nutana]

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