Excel Interop Problems

[eBob.com]

I have a working app which creates an Excel spreadsheet via Interop. But
recently I've added a column of data, which, except for a header in row 1,
is all integers. All of the cells containing the integers have that little
green triangle saying I've put numeric data into a non-numeric column and
neither the column heading nor the integers are right justified.

My searches have found some hits which appear helpful but which do not work
for me. For example, based on one of the hits and some help from
Intellisense I came up with ...

Dim style As New Excel.Style
style.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

But Intellisense complains that New cannot be used on an Interface. If I
eliminate the New then, of course, I get a run time error.

Dim style as New Excel.CellFormat has the same problem.

The search hit which led me to the above conatined ...

Dim style as Microsoft.Office.Interop.Excel.Style

But Intellisense seems to know nothing about
"Microsoft.Office.Interop.Excel.Style". (And I don't understand how the
code gets away with no New!)

I also came up with ...

objSheet.Range("B:B").HorizontalAlignment = HorizontalAlignment.Right

which Intellisense liked and which caused no run time problem but which also
accomplished nothing.

So if anyone has any help or pointers for me I'd appreciate it. I'd guess
that my level of Excel matters. If so it's 2002 SP3. All I'm trying to do
is make some cells which contains integers numeric (to get rid of the green
triangle) and have the same cells right justified.

Thanks, Bob

 

[eBob.com]

I think this will work with any version of Excel, going to back to '97,
anyway. If you want a column to be defined as numeric data (the way you
would in the Excel app by setting the format), you want to set the
NumberFormat property of the entire column. For a integers, you can use
the value "0", or any numeric mask that's appropriate to your data
("##,##0.00" would be valid). If you want to force it to be any text, you
can use the "@" sign.

One way to get to the property would be (assuming WS is of type
Excel.Worksheet and is set to the worksheet you're using):

WS.Cells(1,1).EntireColumn.NumberFormat = "0"

The 1,1 is row and column, so changing the column value there will give
you access to other columns. Cells() returns an Excel.Range object, so any
Range could be used to get at the column.

Thank you Jack, but no joy. It made no difference. I tried the statement
before and after providing the data, i.e. before and after looping through
this statement

objSheet.Range("A" & XLRow.ToString, "G" & XLRow.ToString).Value = astrRowV

but I still got the green triangles and the values were still not right
justified.

If you have any other thoughts I'd sure be interested.

Thanks again, Bob

 

[eBob.com]

Based on your naming convention, I'm guessing that astrRovV is of type
string/ I don't know how the internals of the Value property of the Range
object work, but it may be that to get it to behave like a number in a
number formatted column, you have to assign a number to it. I mostly use
Excel as a report output bucket, and I typically pre-format my columns and
write everything out as Text formatted columns, so I haven't tested it.

What's the specific code you're using to set the column format, either
before or after the assignment? I've only got Office 2007 here, but I
could try to test your code and see what happens on this end.

Hi Captian Jack,

Below is a simplified example of my code. In the designer I create a form
with a textbox named tbxFileName, a numericupdown named nudFNSuffix, and a
button named btnGo.

I have never found complete documentation on the Excel PIA. So I do what
seems to produce the result I want after researching the web and asking
questions here and in the excel programming group. IOW, if it looks like I
don't know what I am doing that's right.

Current goal is to get rid of the green triangles and get the columns right
justified. BTW I am moving to 2007 soon so a 2007 solution will be
relevant.

Thanks for any help you can give me.

Bob


'Option Strict On
Option Explicit On
Option Infer Off

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

End Sub

Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGo.Click
Dim objApp As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet

'Dim objrange As Excel.Range

Dim XLRow As Integer = 1

Dim astrRowV(20) As String
' Instantiate Excel and start a new workbook.
objApp = New Excel.Application ' This works
'objApp = CreateObject("Excel.Application") ' This does NOT work
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = DirectCast(objBook.ActiveSheet, Excel._Worksheet)
objSheet.Name = "Chicas"

'use "top" alignment for all cells
objSheet.Cells.Select()
'objSheet.Cells.VerticalAlignment = -4160
objSheet.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop

'specify "wrap" for all cells
objSheet.Cells.WrapText = True

objSheet.Cells(1, 1).entirecolumn.numberformat = "0" ' requires
Option Strict Off
objSheet.Cells(2, 2).entirecolumn.numberformat = "0"
objSheet.Cells(3, 3).entirecolumn.numberformat = "0"

' not sure about the Cells(#, #) notation; I'm used to seeing a
letter to identify
' a column and a number to identify a row

'write column headings

astrRowV(0) = "Col. 1 Head"
astrRowV(1) = "Col. 2 Head"
astrRowV(2) = "Col. 3 Head"

XLRow = 1

objSheet.Range("A" & XLRow.ToString, "C" & XLRow.ToString).Value =
astrRowV

For i As Integer = 0 To 3

XLRow += 1

astrRowV(0) = i.ToString 'A
astrRowV(1) = i * 2.ToString 'B
astrRowV(2) = i * 3.ToString 'C

objSheet.Range("A" & XLRow.ToString, "C" & XLRow.ToString).Value
= astrRowV

Next i

objBook.SaveAs(tbxFileName.Text & nudFNSuffix.Value.ToString)

'Return control of Excel to the user.
objApp.Visible = True
objApp.UserControl = True

objSheet = Nothing
objSheets = Nothing
objBook = Nothing
objBooks = Nothing
'objApp.Quit() 'Excel closes with this in
'objApp = Nothing 'Excel closes with this in

End Sub
End Class

 

[eBob.com]

Hi Captian Jack,

Below is a simplified example of my code. In the designer I create a
form with a textbox named tbxFileName, a numericupdown named nudFNSuffix,
and a button named btnGo.

[...]

Gotcha... okay, here's what I see:

You can use Option Strict if you want, but you have to cast the Range
object to it's proper type first. I'm not sure why the Excel object does
this with some classes and not others, but it does. The format calls would
like like this:

CType(objSheet.Cells(1, 1), Excel.Range).EntireColumn.NumberFormat = "0"
CType(objSheet.Cells(2, 2), Excel.Range).EntireColumn.NumberFormat = "0"
CType(objSheet.Cells(3, 3), Excel.Range).EntireColumn.NumberFormat = "0"

This tells VB that objSheet.Cells(1,1), et al., are of type Range, which
it both seems to know and not know. Not only can you use Option Strict,
but it you'll also be able to use Intellisense with the class that way.

I'm not sure why CreateObject isn't working... it may be that the COM fuly
qualified name is different of that version, or it can't be found in the
registry on that machine, I'm not sure. I've always thoguht it was
"Excel.Application", but maybe it's not.

The numbers aren't being formatted, because they're not going into the
cells as numbers, they're going in as strings. To be sure, they're numbers
that were converted to string using the ToString method, but since they go
in as string, that's what Excel thinks they are. One solution (not the
best, but it's quick to code) would be to change the data array to be of
type Object instead of string, so you can put both data types in it. That
line of code would become:

Dim astrRowV(20) As Object

Of course, you might want to change the name of the variable, too. Anyway,
when you go to assign your data to the array, change it to this:

astrRowV(0) = i 'A
astrRowV(1) = i * 2 'B
astrRowV(2) = i * 3 'C

Which just removes the "ToString" method call. When you do that, you
should get numbers in your output data.

If all of the data in a row is going to be of the same type, it'd be more
efficient to make an array of that type (Integer or whatever) to send the
data rows, and make a differet string array for the column headings. Or,
you can write the data one cell at a time, but that's slower.

There are multiple ways to index a row or column in the Excel object. One
being, of course, the column letter followed by row number which you
mention and is the way it's done in the Excel interface. You can also
reference cells by row number followed by column number (both 1 based)
which certainly can be confusing.

The documentation for the Excel object model is at the Microsoft Developer
Network web site. The English page of the library is
http://msdn.microsoft.com/en-us/library/default.aspx, and if you follow
the menu tree to MSDN Library -> Office Development -> 2007 Microsoft
Office System -> Excel 2007 -> Excel 2007 Developer Reference -> Excel
Object Model Reference, you'll see all of the class descriptions for the
2007 version of Excel. They've got other versions in the same tree, I'd
imagine the menu path is similar.

Hope some of that's helpful.

VERY helpful Captain! Following your guidance the numbers became numbers
and got right aligned without any further coaxing. The problem then was
getting the column headings right aligned. Never found anything in the doc
pointer you gave me which claimed to be PIA documentation. For 2007 I found
something which looked promising but it did not say, and I couldn't tell, it
if was VBA or VB.Net. But for 2002, which I am stuck with until I get back
home in March and can update to 2007, I found nothing which even looked like
it might be helpful. But ...

.... with the help of Intellisense I came up with

CType(objSheet.Cells(3, 3), Excel.Range).EntireColumn.HorizontalAlignment =
HorizontalAlignment.Right

.... which looks great, don't you think?, but does nothing. However, more
fumbling around with Intellisense and I came up with ...

CType(objSheet.Cells(2, 2), Excel.Range).EntireColumn.HorizontalAlignment =
Excel.XlHAlign.xlHAlignRight

.... and bingo! that did the trick.

I'll tell you, I was going down for the third time with this problem, so I
am most grateful for the lifesaver you threw me!

Thanks VERY MUCH, Bob