473,246 Members | 1,447 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

How do I set Excel cell format to "Text" from VB.NET?

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?
--
John Brock
jb****@panix.com

Nov 21 '05 #1
8 161513
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.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?
--
John Brock
jb****@panix.com

Nov 21 '05 #2
Try:
style.NumberFormat = "@"

HTH,
--
George Nicholson

Remove 'Junk' from return address.
"John Brock" <jb****@panix.com> wrote in message
news:dc**********@reader2.panix.com...
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?
--
John Brock
jb****@panix.com

Nov 21 '05 #3
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.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?
--
John Brock
jb****@panix.com

Nov 21 '05 #4
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?

Nov 21 '05 #5
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.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?
--
John Brock
jb****@panix.com

Nov 21 '05 #6
Thank you, that does the trick.

In article <ez**************@TK2MSFTNGP09.phx.gbl>,
George Nicholson <Ju*********@msn.com> wrote:
Try:
style.NumberFormat = "@"

HTH,
--
George Nicholson

Remove 'Junk' from return address.
"John Brock" <jb****@panix.com> wrote in message
news:dc**********@reader2.panix.com...
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?
--
John Brock
jb****@panix.com


--
John Brock
jb****@panix.com

Nov 21 '05 #7
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?

Nov 21 '05 #8

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" <Ra***********@discussions.microsoft.com> wrote in
message news:EE**********************************@microsof t.com:
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?


Nov 21 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: j.t.w | last post by:
Hi All. I'm having a problem with my Date of Birth textbox. When I open the ..htm file, the "DoB" textbox is flat with a border. All of my other textboxes are sunken and are yellow. When I...
3
by: Silmar | last post by:
Hi! In my form I have table which cells contain input objects of type="text" which initially are disabled. I would like to activate them by clicking on them. However because input object does...
5
by: Kivak Wolf | last post by:
Hey everyone, I have a textbox in my web page that is going to be used to enter an E-mail into (just plain text, no HTML). Now, this will interact with a SQL database where the contents of the...
2
by: gabon | last post by:
I'm creating a select entirely through JavaScript and very strangely IE doesn't show the text in the option elements. Here part of the code: this.form_country=document.createElement("select");...
3
by: Mejmeyster | last post by:
Hi Everyone, I have a table in which one of the text fields has become too small (since it only holds 255 characters). To remedy that, I'm trying to change the data type of that field to "memo"...
2
by: alxasa | last post by:
Hello, I am hoping someone can help me with this. I need a javascript function, which sits inside a <input type="text" name="firstname"> line of code. Now, if someone starts typing fine, but when...
1
by: TimmyNZER | last post by:
Hi, I'm trying to set the format of a "text box" to accept text, I have tried setting the format in the property sheet, but there are only numerical options. I'm sure it must be pretty simple, but...
16
by: mj.redfox.mj | last post by:
Can anyone help? I have a textbox which I'm programatically adding by using the following code: txtTest = New TextBox txtTest.ID = "txtLeft" + cntCount.ToString...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.