Formatting a cell in Excel, using pure VB code
 

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


In article ,
"Jean Claude" wrote:

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

Formatting a cell in Excel, using pure VB code
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jean Claude" wrote in message
...
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

Formatting a cell in Excel, using pure VB code
 

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.

--
Regards,
Tom Ogilvy


"Jean Claude" wrote in message
...
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

Formatting a cell in Excel, using pure VB code
 

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

Formatting a cell in Excel, using pure VB code
 

Thank you all

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

Its workinggggggggggggggg
Guy

??"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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jean Claude" wrote in message
...
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