I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?
--
John Brock
(E-Mail Removed)

The text number format is the at symbol...

..NumberFormat = "@"
--
HTH...

Jim Thomlinson


"John Brock" wrote:

> I am creating an Excel workbook using VB.NET, and have run into a
> problem. Excel at times insists on reformatting data that I enter
> into cells, e.g., converting "01234" to "1234", and this screws me
> up when I need to read the data back. When I run into this problem
> using Excel interactively I simply change the cell Number format
> from "General" to "Text", but I haven't been able to figure out
> how to do this using VB.NET. Here is a code sample:
>
> Dim wb as Microsoft.Office.Interop.Excel.Workbook
>
> [...workbook is created...]
>
> Dim style as Microsoft.Office.Interop.Excel.Style
>
> style = wb.Styles.Add("Style1")
> style.Font.Name = "Arial"
> style.Font.Bold = True
> style.Font.Size = 12
> style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
>
> I use more than one style, and once I've created a style with the
> features I want I apply it to the ranges where I am entering data.
> Styles also have a "NumberFormat" property, and I would think that
> this somehow could be used to set cell format to "Text". But if,
> for example, I try
>
> style.NumberFormat = "Text"
>
> all that happens is that I end up with a weird custom format.
> Nothing else I try seems to work either. Can anyone tell me how
> to do what I am trying to do?
> --
> John Brock
> (E-Mail Removed)
>
>

Here are examples from my own code:

wsStdDev = objXL.Worksheets.Add
With wsStdDev
.Cells(seriesName.GetUpperBound(0) + 7, 3).numberformat = "#.00"
.Cells(seriesName.GetUpperBound(0) + 7, 5).numberformat = "0"
End With

Don't worry about my cell identifiers; what's important here is
numberformat. In the first case, it truncates all fractional parts of the
numbers to 2 places with optional whole numbers to the left. If you want to
more tightly control the whole numbers, you would use combinations of 0 and
#, depending on if you want leading zeros or not. In the second case, all
numbers (regardless of number of digits) lose their fractional portions.
Apply this to any type of range object.

Look up info on numberformat in Excel's VBA help for more detail.

Randall Arnold


"John Brock" wrote:

> I am creating an Excel workbook using VB.NET, and have run into a
> problem. Excel at times insists on reformatting data that I enter
> into cells, e.g., converting "01234" to "1234", and this screws me
> up when I need to read the data back. When I run into this problem
> using Excel interactively I simply change the cell Number format
> from "General" to "Text", but I haven't been able to figure out
> how to do this using VB.NET. Here is a code sample:
>
> Dim wb as Microsoft.Office.Interop.Excel.Workbook
>
> [...workbook is created...]
>
> Dim style as Microsoft.Office.Interop.Excel.Style
>
> style = wb.Styles.Add("Style1")
> style.Font.Name = "Arial"
> style.Font.Bold = True
> style.Font.Size = 12
> style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
>
> I use more than one style, and once I've created a style with the
> features I want I apply it to the ranges where I am entering data.
> Styles also have a "NumberFormat" property, and I would think that
> this somehow could be used to set cell format to "Text". But if,
> for example, I try
>
> style.NumberFormat = "Text"
>
> all that happens is that I end up with a weird custom format.
> Nothing else I try seems to work either. Can anyone tell me how
> to do what I am trying to do?
> --
> John Brock
> (E-Mail Removed)
>
>

Try:
style.NumberFormat = "@"

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"John Brock" <(E-Mail Removed)> wrote in message
news:dc3i5c$j4t$(E-Mail Removed)...
>I am creating an Excel workbook using VB.NET, and have run into a
> problem. Excel at times insists on reformatting data that I enter
> into cells, e.g., converting "01234" to "1234", and this screws me
> up when I need to read the data back. When I run into this problem
> using Excel interactively I simply change the cell Number format
> from "General" to "Text", but I haven't been able to figure out
> how to do this using VB.NET. Here is a code sample:
>
> Dim wb as Microsoft.Office.Interop.Excel.Workbook
>
> [...workbook is created...]
>
> Dim style as Microsoft.Office.Interop.Excel.Style
>
> style = wb.Styles.Add("Style1")
> style.Font.Name = "Arial"
> style.Font.Bold = True
> style.Font.Size = 12
> style.Interior.Pattern =
> Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> style.HorizontalAlignment =
> Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
>
> I use more than one style, and once I've created a style with the
> features I want I apply it to the ranges where I am entering data.
> Styles also have a "NumberFormat" property, and I would think that
> this somehow could be used to set cell format to "Text". But if,
> for example, I try
>
> style.NumberFormat = "Text"
>
> all that happens is that I end up with a weird custom format.
> Nothing else I try seems to work either. Can anyone tell me how
> to do what I am trying to do?
> --
> John Brock
> (E-Mail Removed)
>

A simple way to force Excel to accept anything as text is to prepend an
apostrophe to the text.

e.g. to write an integer 123 as 00000123 just write:
ActiveCell = "'" & Format(123, "00000000")

Note that this cell is now definitively text - this precludes the use of
formulae such as SUM on these cells, or further number formatting. If
this is an issue consider setting the numberformat as described in other
posts.

HTH,
Gareth


John Brock wrote:
> I am creating an Excel workbook using VB.NET, and have run into a
> problem. Excel at times insists on reformatting data that I enter
> into cells, e.g., converting "01234" to "1234", and this screws me
> up when I need to read the data back. When I run into this problem
> using Excel interactively I simply change the cell Number format
> from "General" to "Text", but I haven't been able to figure out
> how to do this using VB.NET. Here is a code sample:
>
> Dim wb as Microsoft.Office.Interop.Excel.Workbook
>
> [...workbook is created...]
>
> Dim style as Microsoft.Office.Interop.Excel.Style
>
> style = wb.Styles.Add("Style1")
> style.Font.Name = "Arial"
> style.Font.Bold = True
> style.Font.Size = 12
> style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
>
> I use more than one style, and once I've created a style with the
> features I want I apply it to the ranges where I am entering data.
> Styles also have a "NumberFormat" property, and I would think that
> this somehow could be used to set cell format to "Text". But if,
> for example, I try
>
> style.NumberFormat = "Text"
>
> all that happens is that I end up with a weird custom format.
> Nothing else I try seems to work either. Can anyone tell me how
> to do what I am trying to do?

My goof, I should have limited my response to styles. My example is
generally relevant but not specific to your question. Sorry.

Randall Arnold

"Randall Arnold" wrote:

> Here are examples from my own code:
>
> wsStdDev = objXL.Worksheets.Add
> With wsStdDev
> .Cells(seriesName.GetUpperBound(0) + 7, 3).numberformat = "#.00"
> .Cells(seriesName.GetUpperBound(0) + 7, 5).numberformat = "0"
> End With
>
> Don't worry about my cell identifiers; what's important here is
> numberformat. In the first case, it truncates all fractional parts of the
> numbers to 2 places with optional whole numbers to the left. If you want to
> more tightly control the whole numbers, you would use combinations of 0 and
> #, depending on if you want leading zeros or not. In the second case, all
> numbers (regardless of number of digits) lose their fractional portions.
> Apply this to any type of range object.
>
> Look up info on numberformat in Excel's VBA help for more detail.
>
> Randall Arnold
>
>
> "John Brock" wrote:
>
> > I am creating an Excel workbook using VB.NET, and have run into a
> > problem. Excel at times insists on reformatting data that I enter
> > into cells, e.g., converting "01234" to "1234", and this screws me
> > up when I need to read the data back. When I run into this problem
> > using Excel interactively I simply change the cell Number format
> > from "General" to "Text", but I haven't been able to figure out
> > how to do this using VB.NET. Here is a code sample:
> >
> > Dim wb as Microsoft.Office.Interop.Excel.Workbook
> >
> > [...workbook is created...]
> >
> > Dim style as Microsoft.Office.Interop.Excel.Style
> >
> > style = wb.Styles.Add("Style1")
> > style.Font.Name = "Arial"
> > style.Font.Bold = True
> > style.Font.Size = 12
> > style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> > style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
> >
> > I use more than one style, and once I've created a style with the
> > features I want I apply it to the ranges where I am entering data.
> > Styles also have a "NumberFormat" property, and I would think that
> > this somehow could be used to set cell format to "Text". But if,
> > for example, I try
> >
> > style.NumberFormat = "Text"
> >
> > all that happens is that I end up with a weird custom format.
> > Nothing else I try seems to work either. Can anyone tell me how
> > to do what I am trying to do?
> > --
> > John Brock
> > (E-Mail Removed)
> >
> >

Thank you, that does the trick.

In article <(E-Mail Removed)>,
George Nicholson <(E-Mail Removed)> wrote:
>Try:
>style.NumberFormat = "@"
>
>HTH,
>--
>George Nicholson
>
>Remove 'Junk' from return address.
>
>
>"John Brock" <(E-Mail Removed)> wrote in message
>news:dc3i5c$j4t$(E-Mail Removed)...
>>I am creating an Excel workbook using VB.NET, and have run into a
>> problem. Excel at times insists on reformatting data that I enter
>> into cells, e.g., converting "01234" to "1234", and this screws me
>> up when I need to read the data back. When I run into this problem
>> using Excel interactively I simply change the cell Number format
>> from "General" to "Text", but I haven't been able to figure out
>> how to do this using VB.NET. Here is a code sample:
>>
>> Dim wb as Microsoft.Office.Interop.Excel.Workbook
>>
>> [...workbook is created...]
>>
>> Dim style as Microsoft.Office.Interop.Excel.Style
>>
>> style = wb.Styles.Add("Style1")
>> style.Font.Name = "Arial"
>> style.Font.Bold = True
>> style.Font.Size = 12
>> style.Interior.Pattern =
>> Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
>> style.HorizontalAlignment =
>> Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
>>
>> I use more than one style, and once I've created a style with the
>> features I want I apply it to the ranges where I am entering data.
>> Styles also have a "NumberFormat" property, and I would think that
>> this somehow could be used to set cell format to "Text". But if,
>> for example, I try
>>
>> style.NumberFormat = "Text"
>>
>> all that happens is that I end up with a weird custom format.
>> Nothing else I try seems to work either. Can anyone tell me how
>> to do what I am trying to do?
>> --
>> John Brock
>> (E-Mail Removed)
>>
>
>


--
John Brock
(E-Mail Removed)

Ah, the most simple and effective solution! I forgot all about it. ; )

Randall Arnold

"Gareth" wrote:

> A simple way to force Excel to accept anything as text is to prepend an
> apostrophe to the text.
>
> e.g. to write an integer 123 as 00000123 just write:
> ActiveCell = "'" & Format(123, "00000000")
>
> Note that this cell is now definitively text - this precludes the use of
> formulae such as SUM on these cells, or further number formatting. If
> this is an issue consider setting the numberformat as described in other
> posts.
>
> HTH,
> Gareth
>
>
> John Brock wrote:
> > I am creating an Excel workbook using VB.NET, and have run into a
> > problem. Excel at times insists on reformatting data that I enter
> > into cells, e.g., converting "01234" to "1234", and this screws me
> > up when I need to read the data back. When I run into this problem
> > using Excel interactively I simply change the cell Number format
> > from "General" to "Text", but I haven't been able to figure out
> > how to do this using VB.NET. Here is a code sample:
> >
> > Dim wb as Microsoft.Office.Interop.Excel.Workbook
> >
> > [...workbook is created...]
> >
> > Dim style as Microsoft.Office.Interop.Excel.Style
> >
> > style = wb.Styles.Add("Style1")
> > style.Font.Name = "Arial"
> > style.Font.Bold = True
> > style.Font.Size = 12
> > style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> > style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
> >
> > I use more than one style, and once I've created a style with the
> > features I want I apply it to the ranges where I am entering data.
> > Styles also have a "NumberFormat" property, and I would think that
> > this somehow could be used to set cell format to "Text". But if,
> > for example, I try
> >
> > style.NumberFormat = "Text"
> >
> > all that happens is that I end up with a weird custom format.
> > Nothing else I try seems to work either. Can anyone tell me how
> > to do what I am trying to do?
>

I tried that trick with my boss and it flew for a little while....till
he changed his mind and wanted the data type changed back. I was a
little red faced.

"Randall Arnold" <(E-Mail Removed)> wrote in
message news:EEAF0284-7865-4C9C-92CB-(E-Mail Removed):

> Ah, the most simple and effective solution! I forgot all about it. ; )
>
> Randall Arnold
>
> "Gareth" wrote:
>
> > A simple way to force Excel to accept anything as text is to prepend an
> > apostrophe to the text.
> >
> > e.g. to write an integer 123 as 00000123 just write:
> > ActiveCell = "'" & Format(123, "00000000")
> >
> > Note that this cell is now definitively text - this precludes the use of
> > formulae such as SUM on these cells, or further number formatting. If
> > this is an issue consider setting the numberformat as described in other
> > posts.
> >
> > HTH,
> > Gareth
> >
> >
> > John Brock wrote:
> > > I am creating an Excel workbook using VB.NET, and have run into a
> > > problem. Excel at times insists on reformatting data that I enter
> > > into cells, e.g., converting "01234" to "1234", and this screws me
> > > up when I need to read the data back. When I run into this problem
> > > using Excel interactively I simply change the cell Number format
> > > from "General" to "Text", but I haven't been able to figure out
> > > how to do this using VB.NET. Here is a code sample:
> > >
> > > Dim wb as Microsoft.Office.Interop.Excel.Workbook
> > >
> > > [...workbook is created...]
> > >
> > > Dim style as Microsoft.Office.Interop.Excel.Style
> > >
> > > style = wb.Styles.Add("Style1")
> > > style.Font.Name = "Arial"
> > > style.Font.Bold = True
> > > style.Font.Size = 12
> > > style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
> > > style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft
> > >
> > > I use more than one style, and once I've created a style with the
> > > features I want I apply it to the ranges where I am entering data.
> > > Styles also have a "NumberFormat" property, and I would think that
> > > this somehow could be used to set cell format to "Text". But if,
> > > for example, I try
> > >
> > > style.NumberFormat = "Text"
> > >
> > > all that happens is that I end up with a weird custom format.
> > > Nothing else I try seems to work either. Can anyone tell me how
> > > to do what I am trying to do?
> >