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 8  161714 
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
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
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
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?
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
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
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?
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? 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 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...
|
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...
|
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...
|
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;
|
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!
| | |
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...
|
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
|
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
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
| | |
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...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
