Sign in to follow this  
Followers 0
strate

Excel - Zoom

14 posts in this topic

After excel is opened I need to zoom out until the whole page is viewable. I think I have tried everything with .zoom i can, but I keep getting errors. Is this possible?


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites



By "whole page," do you mean a whole printed page, or an area covering all of the actual data in your sheet?

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

By "whole page," do you mean a whole printed page, or an area covering all of the actual data in your sheet?

-S

A little more detail....

Assume you have a document that is larger then the window pane. When you open excel you will only be able to see a part of it. I need it so that after the program is open it will zoom out a set amount, allowing you to see more of it.


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

You're probably using the .Zoom() method, right? There is a .Zoom property, too. To zoom into a user-selected area, first select the area and then set the .Zoom property to True.

$oExcel.Range("A1:I24").Select
$oExcel.Activewindow.Zoom = True

That selects the area and zooms into it. The end result is the same whether the area is too big or too small to see at 100%.

-S

Edited by Locodarwin

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

You're probably using the .Zoom() method, right? There is a .Zoom property, too. To zoom into a user-selected area, first select the area and then set the .Zoom property to True.

$oExcel.Range("A1:I24").Select
$oExcel.Activewindow.Zoom = True

That selects the area and zooms into it. The end result is the same whether the area is too big or too small to see at 100%.

-S

Tried that too, heres my code. Maybe you'll see something I'm not.

#include <GuiConstants.au3>

Dim $GUI_ActiveX

GUICreate("TestGUI", @DesktopWidth, @DesktopHeight - 80, -1, -1, BitOR($WS_OVERLAPPEDWINDOW, $WS_VISIBLE, $WS_CLIPCHILDREN))

$Input_1 = GUICtrlCreateInput('G:\Packaging\Process Sheets\136041.xls', 10, 10, 250, 20)
$Button_2 = GUICtrlCreateButton("Load", 280, 10, 90, 20)

GUISetState()
While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $Button_2
            BlockInput(1)
            DllCall("H:\captdll.dll", "int", "CaptureScreen", "str", @TempDir & "\screen.jpg", "int", 85)
            GUICtrlDelete($GUI_ActiveX)
            $FileName = GUICtrlRead($Input_1)
            If Not FileExists($FileName) Then
                BlockInput(0)
                MsgBox(0, "Excel File Test", "Can't run this test, because it requires an Excel file in " & $FileName)
                BlockInput(1)
            Else
                SplashImageOn('', @TempDir & "\screen.jpg", @DesktopWidth, @DesktopHeight, -3, -29)
                $oExcelDoc = ObjGet($FileName)
                If IsObj($oExcelDoc) Then
                    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, @DesktopWidth / 2, @DesktopHeight / 2, @DesktopWidth / 2 - 20, 280)
                    With $oExcelDoc
                        .CommandBars ("Full Screen").Visible = False
                        .CommandBars ("Standard").Enabled = False
                        .CommandBars ("Formatting").Enabled = False
                        .CommandBars ("Worksheet Menu Bar").Enabled = False
                        .Application.DisplayFormulaBar = False
                        .ActivePane.View.Zoom.Percentage = 75
                        .Range("A1:I24").Select
                        .Activewindow.Zoom = True
                    EndWith
                EndIf
            EndIf
            SplashOff()
            BlockInput(0)
        Case Else
          ;;;
    EndSelect
WEnd
Exit

EDIT:

If you remove:

.Range("A1:I24").Select

.Activewindow.Zoom = True

it will work.

Edited by strate

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

I see what's happening now. You're shoe-horning the Excel object into an ActiveX control, and thus it looks like it's turning it into OWC. If that's the case, Zoom won't work, as OWC Spreadsheet doesn't support the Zoom property or method.

Is it absolutely necessary for your project to use an embedded sheet? What's the end goal? Would it be okay if we approached the problem from a different angle?

I'm willing to help you work on an alternative to the embedded sheet, if you like.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

I see what's happening now. You're shoe-horning the Excel object into an ActiveX control, and thus it looks like it's turning it into OWC. If that's the case, Zoom won't work, as OWC Spreadsheet doesn't support the Zoom property or method.

Is it absolutely necessary for your project to use an embedded sheet? What's the end goal? Would it be okay if we approached the problem from a different angle?

I'm willing to help you work on an alternative to the embedded sheet, if you like.

-S

I'm happy with any other option I just don't want anything to make it appear to the user to be excel.


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

Okay, that's fine.

So help me out - what's the goal of the script? So far what I'm seeing is a script that displays data as an Excel sheet. What's the screen capture for?

How do you want the data presented to the user? What about copying the data to another program, like Word or PowerPoint, and displaying it from there? Or copying the data into a series of arrays and displaying it in a GUI listview or something?

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Okay, that's fine.

So help me out - what's the goal of the script? So far what I'm seeing is a script that displays data as an Excel sheet. What's the screen capture for?

How do you want the data presented to the user? What about copying the data to another program, like Word or PowerPoint, and displaying it from there? Or copying the data into a series of arrays and displaying it in a GUI listview or something?

-S

The screen capture is to eliminate the blank gui between switching documents.

The data is to be used as a reference for the employees. It is best if it stays in excel. Each document has many different pictures and formatting is extremely important.

Edited by strate

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

How about this? Slight workaround, and it's still in Excel, but it actually looks pretty good. The file is opened read-only, so you don't have to worry about users messing it up.

Just change the range I have in there in the first .Range() object with the range of the data you want to show.

#include <GuiConstants.au3>

Dim $GUI_ActiveX

;GUICreate("TestGUI", @DesktopWidth, @DesktopHeight - 80, -1, -1, BitOR($WS_OVERLAPPEDWINDOW, $WS_VISIBLE, $WS_CLIPCHILDREN))
GUICreate("TestGUI", 380, 100)
$Input_1 = GUICtrlCreateInput('G:\Packaging\Process Sheets\136041.xls', 10, 10, 250, 20)
$Button_2 = GUICtrlCreateButton("Load", 280, 10, 90, 20)

GUISetState()
While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $Button_2
            BlockInput(1)
            DllCall("H:\captdll.dll", "int", "CaptureScreen", "str", @TempDir & "\screen.jpg", "int", 85)
            GUICtrlDelete($GUI_ActiveX)
            $FileName = GUICtrlRead($Input_1)
            If Not FileExists($FileName) Then
                BlockInput(0)
                MsgBox(0, "Excel File Test", "Can't run this test, because it requires an Excel file in " & $FileName)
                BlockInput(1)
            Else
                SplashImageOn('', @TempDir & "\screen.jpg", @DesktopWidth, @DesktopHeight, -3, -29)
                $oExcel = ObjCreate("Excel.Application")
                If IsObj($oExcel) Then
                    With $oExcel
                        .Visible = True
                        .WorkBooks.Open($FileName, Default, True)   ; open the document read-only
                        .CommandBars ("Full Screen").Visible = False
                        .CommandBars ("Standard").Enabled = False
                        .CommandBars ("Formatting").Enabled = False
                        .CommandBars ("Worksheet Menu Bar").Enabled = False
                        .Application.DisplayFormulaBar = False
                        .Range("A1:I24").Select
                        .Activewindow.Zoom = True
                        .Range("A1").Select
                    EndWith
                EndIf
            EndIf
            SplashOff()
            BlockInput(0)
        Case Else
           ;;;
    EndSelect
WEnd
Exit

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

How about this? Slight workaround, and it's still in Excel, but it actually looks pretty good. The file is opened read-only, so you don't have to worry about users messing it up.

Just change the range I have in there in the first .Range() object with the range of the data you want to show.

#include <GuiConstants.au3>

Dim $GUI_ActiveX

;GUICreate("TestGUI", @DesktopWidth, @DesktopHeight - 80, -1, -1, BitOR($WS_OVERLAPPEDWINDOW, $WS_VISIBLE, $WS_CLIPCHILDREN))
GUICreate("TestGUI", 380, 100)
$Input_1 = GUICtrlCreateInput('G:\Packaging\Process Sheets\136041.xls', 10, 10, 250, 20)
$Button_2 = GUICtrlCreateButton("Load", 280, 10, 90, 20)

GUISetState()
While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $Button_2
            BlockInput(1)
            DllCall("H:\captdll.dll", "int", "CaptureScreen", "str", @TempDir & "\screen.jpg", "int", 85)
            GUICtrlDelete($GUI_ActiveX)
            $FileName = GUICtrlRead($Input_1)
            If Not FileExists($FileName) Then
                BlockInput(0)
                MsgBox(0, "Excel File Test", "Can't run this test, because it requires an Excel file in " & $FileName)
                BlockInput(1)
            Else
                SplashImageOn('', @TempDir & "\screen.jpg", @DesktopWidth, @DesktopHeight, -3, -29)
                $oExcel = ObjCreate("Excel.Application")
                If IsObj($oExcel) Then
                    With $oExcel
                        .Visible = True
                        .WorkBooks.Open($FileName, Default, True)   ; open the document read-only
                        .CommandBars ("Full Screen").Visible = False
                        .CommandBars ("Standard").Enabled = False
                        .CommandBars ("Formatting").Enabled = False
                        .CommandBars ("Worksheet Menu Bar").Enabled = False
                        .Application.DisplayFormulaBar = False
                        .Range("A1:I24").Select
                        .Activewindow.Zoom = True
                        .Range("A1").Select
                    EndWith
                EndIf
            EndIf
            SplashOff()
            BlockInput(0)
        Case Else
           ;;;
    EndSelect
WEnd
Exit

-S

Hnmmm.... I need the excel window inside of another GUI that has order information in it. Is it possible to convert the file that is created if the user "prints to file" to a image?

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

Hnmmm.... I need the excel window inside of another GUI that has order information in it. Is it possible to convert the file that is created if the user "prints to file" to a image?

I see. Yes, that's possible, but it doesn't seem optimal.

How about performing another screen capture, then squeezing the captured image into a GUICtrlCreatePic()? The image will be automatically scaled to fit the picture control.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

I see. Yes, that's possible, but it doesn't seem optimal.

How about performing another screen capture, then squeezing the captured image into a GUICtrlCreatePic()? The image will be automatically scaled to fit the picture control.

-S

Is it possible to take a screen shot of a window that you can not see? I don't want a excel window to pop up.


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

If somebody could assist in converting this I think it might be my solution. It it from this page: http://www.xtremevbtalk.com/t238494.html

Private Const WM_PAINT = &HF
   Private Const WM_PRINT = &H317
   Private Const PRF_CLIENT = &H4&  ' Draw the window's client area.
   Private Const PRF_CHILDREN = &H10& ' Draw all visible child windows.
   Private Const PRF_OWNED = &H20&  ' Draw all owned windows.

   Private Declare Function SendMessage Lib "user32" Alias _
      "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
      ByVal wParam As Long, ByVal lParam As Long) As Long

   Dim retval As Long 'Return Value
   Form1.SetFocus
   Form2.AutoRedraw = True
   retval = SendMessage(Form1.hwnd, WM_PAINT, Form2.hDC, 0)
   retval = SendMessage(Form1.hwnd, WM_PRINT, Form2.hDC, _
   PRF_CHILDREN + PRF_CLIENT + PRF_OWNED)
   Form2.Picture = Form2.Image
   Form2.AutoRedraw = False
Edited by strate

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0