asked on

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

fritzfrancis

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.

Jimbo99999

ASKER

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

fritzfrancis

Is there a NumberAsText property? If so set that to False.

http://msdn.microsoft.com/en-us/library/office/aa224871(v=office.11)

Jimbo99999

ASKER

I do not have that option.

fritzfrancis

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.

Jimbo99999

ASKER

I will not get the preceeding zeroes. for the "numerical" code data.

Nasir Razzaq

See if suggestion here works for you

http://stackoverflow.com/questions/9229644/how-to-remove-warnings-in-excel-generated-by-poi

Shahan Ayyub

Have you tried to change the order of assignment ??
like:

                        .NumberFormat = "@"
                        .Value = Code

Open in new window

Jimbo99999

ASKER

Yes and I still receive same result.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

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