• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

VB.net - Outputting to Excel Formatting

Good Day Experts!

I have data that is defined in the SQL table as varchar that I am outputting to Excel.  The data contains alpha code like RFDG as well as numerical code like 002.  Since I need to keep the preceeding zeroes, I set the NumberFormat to the cell as "@".  Now I get that little triangle in the corner of the cells that says "The Number in this cell is formatted as text..."

Is there anyway I can programatically get this to not show? The output is just for display purposes and there will be no totalling or manipulation by the Users.

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
1 Solution
 
fritzfrancisCommented:
I believe in Excel, you can select "Format Cells" and change it from a number to text. If you want to do it from the VB side so that it goes in as text, then you can use a String.Format or String.PadfLeft/PadRight method.
0
 
Jimbo99999Author Commented:
Hey There:

Here is what I have...how do I implement your response?

                    With oSheet.Range("A" + CStr(ExcelRow))
                        .Font.Size = 10
                        .Font.Name = "Segoe UI"
                        .Value = Code
                        .NumberFormat = "@"
                        .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                    End With

Thanks,
jimbo99999
0
 
fritzfrancisCommented:
Is there a NumberAsText property? If so set that to False.

http://msdn.microsoft.com/en-us/library/office/aa224871(v=office.11)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jimbo99999Author Commented:
I do not have that option.
0
 
fritzfrancisCommented:
What happens if you don't set the NumberFormat property at all? May if you don't set it to anything, Excel won't think of it as a number.
0
 
Jimbo99999Author Commented:
I will not get the preceeding zeroes.  for the "numerical" code data.
0
 
CodeCruiserCommented:
0
 
Shahan AyyubSenior Software Engineer - iOSCommented:
Have you tried to change the order of assignment ??
like:

                        .NumberFormat = "@"
                        .Value = Code

Open in new window

0
 
Jimbo99999Author Commented:
Yes and I still receive same result.
0
 
NorieCommented:
To get the little green triangle to not show, manually:

Goto Options..., select Formulas from the list, and uncheck 'Numbers formatted as text or preceded by an apostrophe' in the Error checking rules.

To do it in VBA you would use this.
Application.ErrorCheckingOptions.NumberAsText =False

Open in new window

In your code replace Application with the application object you are using.
0
 
Jimbo99999Author Commented:
Good Day!

Using oXL.ErrorCheckingOptions.NumberAsText = False I have no more little green triangles.

At first I thought I was in trouble since the intellisense did not show .ErrorCheckingOptions for oXL.

jimbo99999
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now