Programmaticlly changing Excel advanced options

[JScannell]

I know that I can navigate to the display options from within an Excel file, but how do you access the advanced options programmatically?

I need to be able to clear the "Show a zero in cells that have zero value" property programmatically. But, I don't know where the Options | Advanced | Display options for this worksheet is located in the DOM.

Thanks in advance,
Jerry

Jerry Scannell

 

[Andrzejek]

In Excel I created 2 Macros with the steps you described:
one - un-checking Display options for this worksheet, and
two - Checking Display options for this worksheet

Option Explicit
Sub Macro1()
    ActiveWindow.DisplayZeros = False
End Sub
Sub Macro2()
    ActiveWindow.DisplayZeros = True
End Sub

Would that help?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[JScannell]

Andy,

Thanks for that. In VBA you can use ActiveWindow. But where is the DisplayZeros property when you are accessing Excel objects from with in VB.net application?

Jeerry

Jerry Scannell

 

[Andrzejek]

Some code from here, and:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim appXL As Excel.Application
        Dim wbXl As Excel.Workbook
        Dim shXL As Excel.Worksheet
        Dim raXL As Excel.Range[green]
        ' Start Excel and get Application object.[/green]
        appXL = CreateObject("Excel.Application")
        appXL.Visible = True[green]
        ' Add a new workbook.[/green]
        wbXl = appXL.Workbooks.Add
[blue]
        appXL.ActiveWindow.DisplayZeros = False[/blue]
    End Sub
End Class

Hmmm, you are asking VB.NET question in C# Forum...

Have fun.

---- Andy

There is a great need for a sarcasm font.