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 8  161579 
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
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
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
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?
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
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
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?
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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");...
|
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"...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
