473,836 Members | 1,519 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m

Nov 21 '05 #1
8 161755
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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m

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

HTH,
--
George Nicholson

Remove 'Junk' from return address.
"John Brock" <jb****@panix.c om> wrote in message
news:dc******** **@reader2.pani x.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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern =
Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment =
Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m

Nov 21 '05 #3
Here are examples from my own code:

wsStdDev = objXL.Worksheet s.Add
With wsStdDev
.Cells(seriesNa me.GetUpperBoun d(0) + 7, 3).numberformat = "#.00"
.Cells(seriesNa me.GetUpperBoun d(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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m

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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.Worksheet s.Add
With wsStdDev
.Cells(seriesNa me.GetUpperBoun d(0) + 7, 3).numberformat = "#.00"
.Cells(seriesNa me.GetUpperBoun d(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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m

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

In article <ez************ **@TK2MSFTNGP09 .phx.gbl>,
George Nicholson <Ju*********@ms n.com> wrote:
Try:
style.NumberFo rmat = "@"

HTH,
--
George Nicholson

Remove 'Junk' from return address.
"John Brock" <jb****@panix.c om> wrote in message
news:dc******* ***@reader2.pan ix.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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern =
Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment =
Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.co m


--
John Brock
jb****@panix.co m

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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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.mic rosoft.com> wrote in
message news:EE******** *************** ***********@mic rosoft.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.Offic e.Interop.Excel .Workbook

[...workbook is created...]

Dim style as Microsoft.Offic e.Interop.Excel .Style

style = wb.Styles.Add(" Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior. Pattern = Microsoft.Offic e.Interop.Excel .XlPattern.xlPa tternSolid
style.Horizonta lAlignment = Microsoft.Offic e.Interop.Excel .XlHAlign.xlHAl ighLeft

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 "NumberForm at" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFor mat = "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
17601
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 change the name of the "DoB" textbox to something like "Telephone" or "TelephoneBirthdate", the textbox changes to sunken, and yellow. I have tried changing the name to "DBirth", "BirthDate", "Birthday", "cusBirth", "DOBirth", etc. but, the...
3
3274
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 not support onClick event (or maybe I am wrong?) I use onClick events of table cells. And it works as required in IE 6. I click on the input object and the onClick event of the cell is raised. But it does not work in Firefox (I have not yet checked...
5
2195
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 textbox are sent from the SQL database to the textbox, then the user edits it, and then the text inside the textbox is sent back to the SQL database. I did it the same way as if i had used a VarChar instead of a type "text" variable in the SQL...
2
3802
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"); var option; for(var i=0; i<arr.length; i++){ option=document.createElement("option"); option.value=arr.code;
3
3300
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" in design mode. However, when I go to save the new settings of my table, the error message "Record is too large" pops up and won't allow me to save the table. What am I doing wrong? I work with Access 97 in Windows XP. Thanks for your help!
2
2273
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 it goes 1 character past 15 characters (15 characters only allowed), in this case I would like the contents of the input to be cleared out (automatically), and reset the input and its value back to nothing. a) Can this be done, and will someone...
1
2312
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 it's driving me crazy!! Thanks, Tim Matthews
16
5191
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 Page.FindControl("tdInput").Controls.Add(txtTest) This successfully creates a textbox called "txtLeft1" in the table
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10854
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10600
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9387
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4022
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.