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?