Formatting a cell in Excel, using pure VB code

[J.E. McGimpsey]

This is one place where the Macro Recorder could have helped you.
When I recorded a macro setting a cell to Text format:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/02/2003 by J.E. McGimpsey
'

'
Selection.NumberFormat = "@"
End Sub

 

[Dick Kusleika]

Jean Claude

xlsSheet.Cells(1+I,3).NumberFormat = "@"

will format as text.

xlsSheet.Cells(1+I,3).Value = "'" & sPhoneNumber

will enter as a text string regardless of the formatting. That is
doublequote singlequote doublequote.

 

[Tom Ogilvy]

Another option

Dim sVal as String
sVal = "03555555"
xlsSheet.Cells(1 + I,3).Value = "'" & sVal

preceding the number with a single quote will cause it to be treated as a
string.

 

[Jean Claude]

Hi all,

I have a problem with excel cell formatting.
One of the columns should contain phone numbers (a number that starts with
zero ... sometimes)
The VB program puts a phone number lets say - "03555555"
When I open the file from Excel - I see the number without the zero.
I saw that the format of the cell is "General" however I think I should set
the format of the cell to "Text"
I tried several code lines but none helped
Like:
xlsSheet.Cells(1 + I, 3).NumberFormat="Text" -> This did not work
xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) -> With this ,
the cells contained ""
xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" -> In this case, when I put
numbers shorter than the number of 0 , example - "031234", the cell
contained "00031234"


Please someone show me the right way ...

Thank you
Frustrated Guy

 

[Jean Claude]

Thank you all

I was looking for this :
xlsSheet.Cells(1+I,3).NumberFormat = "@"

Its workinggggggggggggggg
Guy