Open Excel at 90% zoom

[Stevecb]

Hello
I was able to create/open an Excel ss from vb.net but how do I open it at 90% something like...
ActiveWindow.Zoom = 90

but I get "Activewindow is not declared." I'm really new at this, please help

[Stewart Ross]

Although the Activewindow property can be referred to on its own from within Excel, where the Application object is implied, you can't do this outside of Excel. You must fully-qualify the Excel application server object instead.

Using VBA as an illustration

Dim objExcel as Excel.Application
Set objExcel as New Excel.Application
.
.
objExcel.Activewindow.Zoom = 90

-Stewart

[Stevecb]

Hello - thanks for the reply. I tried your suggestion and
I'm getting "Object reference not set to an instance of an object."

I have this within a procedure that creates the ss

 Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim objRange As Excel.Range = Nothing
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        objExcel = New Excel.Application
        objExcel.ActiveWindow.Zoom = 90
 

I thought it would reference the open Excel window, or so I thought

[Stewart Ross]

I was using objExcel as an example of an object variable name. You have in effect declared a second Excel application object, which has no connection at all to the object called xlApp you have already instantiated. Even worse, until you open a workbook in the objExcel instance there is no active window to refer to.

Now that I know what objects you have declared there are several choices, as the Application object is available from a Worksheet object as well as directly. Perhaps simplest in your case is to replace lines 8 and 9 above with

xlWorkSheet.Application.ActiveWindow.Zoom = 90

-Stewart

[Stevecb]

I changed it to this and it doesn't error but it doesn't work either...

Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim objRange As Excel.Range = Nothing
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        objExcel = New Excel.Application
        'objExcel.ActiveWindow.Zoom = 90
        xlApp.ActiveWindow.Zoom = 90
 

[Stevecb]

Sorry, getting cross threaded. It works on sheet1 could I bother you to explain how to get it applied to all sheets not just the first. I tried...

xlWorkSheet.Application.ActiveWorkbook.zoom = 90

but it didn't like it.

[Stewart Ross]

I've adapted what you provided to my VBA environment. I notice that the editor is not happy with variable names xlWorksheet, for example, as these appear to clash with system constants. I have renamed all such instances below.

I have tested the code below running from MS Access in VBA, with the application set Visible to cross check, and it does indeed zoom the active worksheet window (sheet 1) to 90%.

Public Sub Test()
    Dim xlApp As New Excel.Application
    Dim objxlWorkbook As Excel.Workbook
    Dim objxlWorksheet As Excel.Worksheet
    xlApp.Visible = True
    Set objxlWorkbook = xlApp.Workbooks.Add()
    Set objxlWorksheet = objxlWorkbook.Sheets("Sheet1")
    objxlWorksheet.Application.ActiveWindow.Zoom = 90
End Sub

I removed the name of the variable misValue from the Workbooks.Add method - I cannot see where it is declared or passed to the sub, and without knowing what its value is I do not know what template it is referring to. For test purposes I'm simply adding a new workbook.

-Stewart

[Stevecb]

Thanks for your patience it has been very helpful. I was hoping to open all the sheets at 90% and tried to loop through with this...

With xlWorkBook
For intCount = 1 To 7
xlApp.ActiveWindow.Zoom = 90 ' open sheet at 90%
Next
End With

but I think it just loops through numbers 1 - 7 and doesn't zoom each sheet

[Stewart Ross]

You need to loop through the worksheets collection of the application object and activate each sheet before it can be zoomed. Using your counter approach, this would be done like this:

With xlApp
    For intCount = 1 To .Worksheets.Count
        .Worksheets(intCount).Activate
        .ActiveWindow.Zoom = 90
    Next
End With
 

Note the change to the application object from the workbook for the With, as the Workbook object was not being referred to in the With you showed.

There is a similar approach which does not use a counter as such, but a worksheet object within a FOR..EACH loop:

Dim objExcelWS As Excel.Worksheet
With xlApp
    For Each objExcelWS In .Worksheets
        objExcelWS.Activate
        .ActiveWindow.Zoom = 90
    Next
End With

These are general approaches which zoom all the worksheets in a workbook, regardless of how many or how few are in the current workbook. Using a constant such as 7 as the limit of the sheet count invites errors when opening workbooks that do not have 7 worksheets.

-Stewart

[Stevecb]

Thank you very much Stewart for your patience and expertise. It was a huge help!

[Stevecb]

I think I spoke too soon. It appears that the first 3 sheets are indeed zoomed to 90% but all the rest are not.
I wonder if they need to be activated before the zoom can occur. I see the code has...

.ActiveWindow.Zoom = 90

but maybe since the first 3 are added by default or whenever a ss is opened they are affected to 90%. I'm grasping at straws here

[Stewart Ross]

It isn't an issue with the code; it is likely to be about precisely when you are using it. If you are using FOR intCount = 1 to xlApp.Worksheets.Count or the FOR..EACH method then the sub will zoom all the sheets that are present at the time. I've tried it with a default of 3 and a default of 7 with results exactly as expected.

If you subsequently add worksheets to the workbook you will need to activate each one then set its zoom setting accordingly.

You could separate out the zooming of all worksheets into a single subroutine and call that whenever you need to, or devise a general subroutine which will zoom any named sheet in a workbook:

Public Sub Zoom (xlApp as Excel.Application, ByVal WSName as string)
Dim strActiveSheetName as String
With xlApp
  strActiveSheetName = .ActiveSheet.Name
  .Worksheets(WSName).Activate
  .ActiveWindow.Zoom = 90
  .Worksheets(strActiveSheetName).Activate
End With
End Sub

This is called when required like this:

Zoom xlApp, "Sheet2"

-Stewart