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" wrote in
message :

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.xlPattern Solid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLe ft

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?