3rd party apps from within our forms

It is now 1:30am and I still can't sleep again so let us make my time a bit useful.

Inspired by what Bernard Bout has shown here, I decided to see earlier today how it can be done on an excel instance.  Please read it (click on here word above) before proceeding below as I will try to break things apart for our better understanding.

The barest tools of the Trade



- a shape on our form
- SetParent
- WinExec
- FindWindow
- SetWindowPos

Bernard has given us a great starting point because the basic ingredients are already there.  And I agree and actually appreciated that Bernard has not shown all the tricks for if he has, I won't try to maybe understand some of those and simply will use the trick blindly; and I won't get a better understanding of those.

Let us start deconstruction and reconstruction:

WinExec the only way?

Nope.  He has given it to us as that is one of the simplest command to open up a 3rd party app.  But there are alternatives to it like RUN, ShellExecute(), scripting and some more.

The main goal and the first step, I've come to realize, is to open up the file or app in any of the ways possible and since my target here is Excel and I am into automation when it comes to excel, then automation it is.

Where is the Window?

The second step after opening up the 3rd party app is to get a handle of that app's window by trying to find it.  And that can be done via Winapi FindWindow() like this:

nHwnd = FindWindow(NULL, "Untitled - Notepad")

Where nHwnd is the Window handle of the 3rd party app we want.  The above is saying, in layman's term, find a newly opened and unsaved notepad instance among the open windows and get the handle of its window so we can work further on it.  That caption is what you will see as caption when you open up a notepad by itself.

If VFP won't be able to find the window handle, then nHwnd will return 0.


Attempt on an xlsx file format

As I was doing automation, then I do it like this instead, first attempt is done on an xlsx file:

Local loExcel As excel.Application, lcFile

loExcel = Createobject('excel.application')

lcFile = Getfile('xls,xlsx')

If !Empty(m.lcFile)

      loExcel.Workbooks.Open(m.lcFile)

      * Get a handle on its Window

      nHwnd = FindWindow('XLMain',Alltrim(Justfname(m.lcFile))+' - Microsoft Excel')

Endif

And it fails, nothing happened!  Belatedly I realize that despite of what is shown on screen in Excel 2007's title bar, internally it is still doing it the old way like this:

nHwnd = FindWindow('XLMain','Microsoft Excel - '+Alltrim(Justfname(m.lcFile)))

Needless to say, the above works.  So far so good!

Attempt on an xls file format

Then I tried opening up an xls file format and it fails again!  Darn!!!  I remembered that when you open up an xls file in Excel 2007, an additional caption of - [Compatibility Mode] will be added.  That is a way to visually remind us that we are currently working on an old file format.  Hmmm... piece of cake, I will do it then like this:

* Attempt to open without that compatibility mode caption

nHwnd = FindWindow(Null, "Microsoft Excel - "+Alltrim(Justfname(m.lcFile)))

If m.nHwnd = 0

      * Failed, so attempt to open with that added compatibility mode caption

      nHwnd = FindWindow('XLMain', 'Microsoft Excel - +Alltrim(Justfname(m.lcFile))+' [Compatibility Mode]')

Endif

And it does not open properly.  I mean it does open but it opens outside of my app on its own.  Drat!!!

I wasted my time trying to find the right combination on Excel's titlebar internal caption by applying the actual cases of the filename using FSO... still no luck (I realized in the end through repeated testing though that character case like proper, upper and lower do not affect it), jumbling and re-positioning words in caption.... again no luck.... and I was about to give up as I've already wasted almost 2 hours for just that stupid internal caption (hey I was frustrated, LOL!) and is about to shelve the whole project when a thought came by.  Heck!!!! I am doing automation so what is keeping me from doing this?

nHwnd = FindWindow('XLMain', loExcel.Caption)

And Viola!!!!  A very flexible way of ensuring to find the proper caption of an open excel file without minding whether it is on compatibility mode or not or on whatever version of Excel I am using!  Great!  Of course, said approach is not limited to just excel.


Shape of My Heart

On to other parts now.  What I've noticed as well is that the trick uses a shape.  And when I first read and saw the trick, my presumption is that excel or any 3rd party app magically appears on said shape morphing said shape into those 3rd party app.  But since I am now trying to break things apart, I come to realize that with or without that shape, we can open those 3rd party apps inside our app.

So what is that shape then for?  Said invisible/visible (your choice) shape on form is there for one reason only.  That is not to show the 3rd party app but to serve as an easy way to set the coordinates of those 3rd party app from within our form, as it is easier to resize a shape and instruct said 3rd party app to "follow" the coordinates of that shape than do it on codes on a repeated trial and error way.

Create a shape, size and position it in the form to your liking, hide it if you want, and instruct the 3rd party app to follow its coordinates.  Very ingenious by those who originally thought of the idea.

Excel not clickable, not editable and a lot of nuts!

To make things simpler, I realize that with VFP while "we" can see the objects, internally it can't be seen by VFP itself.  Just like when we add by codes some objects in the grid, we have to make it Visible otherwise you can see those but can't work properly on it.  So:

loExcel.Visible = .T.

So if you wanted to make this excel inside our form just for pure viewing purposes only, set Visible property to false:

loExcel.Visible = .F.

And that is it.  All the user can do is just scroll down and up to view its contents. :)

What's on the menu?

But once you've made Excel visible, then the whole excel file will be inside our form with all its glory like ribbon, formula bar, worksheet tabs, etc.  Well some of you guys may want it that way but I don't.  So I have to hide those.  Just check the codes later on how to do those.

Size me up!

Not surprisingly, resizing the form will leave the 3rd party app on its original coordinates.  But this is easy, check the codes.

Zooming, saving, detecting and some more

Just check the codes.... I am starting to get sleepy, finally.

Summary:

There are two or more things I am after but not able to find a solution yet and which I am not comfortable as well leaving those matters unresolved.  And basic of those wants are:

- Hiding the titlebar of Excel
- Disallowing drag and drop

Even playing with SetWindowPos' uFlags did not gave me the expected results.  I was able to utilize a good flag though when we are trying to open up a new excel file so excel will not slowly destroy its objects in front of our eyes when we close it.

I will continue playing with this and if I find ways, I will update this.  Or since the plan as usual is to post this inside foxite forum which is among the friendliest developers' forums I have seen, hopefully someone who may have toyed with this before ahead of me (sorry I am always late) can share to us how to work around those issues; then I will edit this post to include their codes as well as the contributor.

The next day:

Found the missing link, LOL!  The trick on hiding excel's title bar is instead of looking for a hidden properties of excel to hide those is to manipulate it directly using these two WinAPIs, i.e., GetWindowLong and SetWindowLong.  Those are possible because we already have a handle on its window.  Check on the codes below how it is done!


Codes:

loTest = Createobject("Form1")
loTest.Show(1)
 
Define Class Form1 As Form
      AutoCenter= .T.
      Height = 493
      Width = 955
      Caption = 'Excel within our Form'
      _nHwnd = .F.
      _oExcel = .F.
      _oWindow = .F.
      _oWBook = .F.
 
      Add Object Shape1 As Shape With ;
            Top = 36, Left = 6, Height= 445, Width = 936,;
            BackColor = Rgb(255,255,255), BorderColor = Rgb(0,128,192)
 
      Add Object label1 As Label With ;
            Top = 15, Left = 6, Caption = 'Preview', FontBold = .T.,;
            FontName = 'Calibri', FontSize = 12, AutoSize = .T.
 
      Add Object label2 As Label With ;
            Top = 12, Left = 836, Caption = 'Zoom', FontBold = .T.,;
            FontName = 'Calibri', FontSize = 12, AutoSize = .T.,;
            Anchor = 9
 
      Add Object cmdOpen As CommandButton With ;
            Top = 8, Left = 312, Caption = '\<Open', Width = 84, Height = 24
 
      Add Object cmdSave As CommandButton With ;
            Top = 8, Left = 399, Caption = '\<Save', Width = 84, Height = 24
 
      Add Object cmdClose As CommandButton With ;
            Top = 8, Left = 486, Caption = '\<Close', Width = 84, Height = 24
 
      Add Object chkShowTabs As Checkbox With ;
            Top = 12, Left = 732, Caption = 'Show \<Tabs', AutoSize = .T.,;
            Anchor = 9
 
      Add Object SpinZoom As Spinner With ;
            Top = 10, Left = 882, KeyboardLowValue = 10, SpinnerLowValue = 10,;
            Value = 100, Anchor = 9, Width = 60
 
      Procedure Load
            Declare Integer SetParent In user32;
                  INTEGER hWndChild,;
                  INTEGER hWndNewParent
 
            Declare Integer FindWindow In user32;
                  STRING lpClassName, String lpWindowName
 
            Declare Integer SetWindowPos In user32;
                  INTEGER HWnd,;
                  INTEGER hWndInsertAfter,;
                  INTEGER x,;
                  INTEGER Y,;
                  INTEGER cx,;
                  INTEGER cy,;
                  INTEGER uFlags
 
            Declare Integer GetWindowLong In User32;
                  Integer HWnd, Integer nIndex
 
            Declare Integer SetWindowLong In user32 ;
                  Integer HWnd,;
                  INTEGER nIndex,;
                  Integer dwNewLong
      Endproc
 
      Procedure Resize
            Thisform._SetCoord()
      Endproc
 
      Procedure Destroy
            If Type('thisform._oexcel') = 'O'
                  This._Clear()
            Endif
      Endproc
 
      Procedure cmdOpen.Click
            If Type('thisform._oexcel') = 'O'
                  Thisform._Clear()
            Endif
            Thisform._linkapp()
      Endproc
 
      Procedure cmdSave.Click
            If Type('thisform._oexcel') = 'O'
                  Thisform._oExcel.activeworkbook.Save()
                  Messagebox('Changes made are saved!',64,'Save')
            Else
                  Messagebox('Nothing to save yet!',64,'Opppppssss!')
            Endif
      Endproc
 
      Procedure cmdClose.Click
            If Type('thisform._oexcel') = 'O'
                  Thisform._Clear()
            Endif
      Endproc
 
      Procedure SpinZoom.InteractiveChange
            Thisform._oWindow.Zoom=Thisform.SpinZoom.Value
      Endproc
 
      Procedure chkShowTabs.Click
            Thisform._oWindow.DisplayWorkbookTabs=This.Value
      Endproc
 
      Procedure _Clear
            With This
                  With  .Shape1
                        * Show shape
                        .Visible = .T.
                        * Hide window via uFlags
                        SetWindowPos(This._nHwnd, 1, .Left, .Top,;

                                     .Width, .Height,0x0080)
                  Endwith
 
                  With ._oWindow
                        .DisplayWorkbookTabs=.T.
                        .DisplayHeadings=.T.
                  Endwith
 
                  With ._oExcel
                        * Restore those we have hidden
                        .DisplayFormulaBar = .T.
                        .DisplayStatusBar = .T.
 
                        Thisform._oWBook.Close()
                        .Visible = .F.
                        .Quit
                  Endwith
                  ._oExcel = .F.
            Endwith
      Endproc
 
      Procedure _HideRibbon
            Local loRibbon
            loRibbon =This._oExcel.CommandBars.Item("Ribbon")
            If m.loRibbon.Height > 0
                  This._oExcel.ExecuteExcel4Macro('Show.Toolbar("Ribbon",False)')
            Endif
      Endproc
 
      Procedure _linkapp
            Local loExcel As excel.Application, lcFile
            loExcel = Createobject('excel.application')
            lcFile = Getfile('xls,xlsx')
            If !Empty(m.lcFile)
                  loExcel.Workbooks.Open(m.lcFile)
 
                  * This is so we can tap into it on other methods/events
                  This._oExcel = loExcel
                  This._oWindow = m.loExcel.ActiveWindow
                  This._oWBook = m.loExcel.activeworkbook
 
 
                  With loExcel
                        .Visible = .T.
                        .DisplayAlerts = .F.
                        .Application.ShowWindowsInTaskbar=.F.
 
                        .DisplayFormulaBar = .F.
                        .DisplayDocumentActionTaskPane=.F.
                        .DisplayStatusBar = .F.
 
                        * Ensure scroll bars are shown
                        .ActiveWindow.DisplayVerticalScrollBar=.T.
                        .ActiveWindow.DisplayHorizontalScrollBar=.T.
 
                        * Hide Workbook Tabs
                        .ActiveWindow.DisplayWorkbookTabs=;

                            Thisform.chkShowTabs.Value
                        .ActiveWindow.DisplayHeadings=.F.
                        .ActiveWindow.WindowState = -4137  && xlMaximized
                        .ActiveWindow.Zoom=Thisform.SpinZoom.Value
 
                        * Get a handle on Window
                        nHwnd = FindWindow('XLMain', .Caption)
                  Endwith
 
                  * Add this so we can work on other methods
                  This._nHwnd = m.nHwnd
 
                  * Hide Ribbon
                  Thisform._HideRibbon()
 
 
                  * Hide the title bar, disallow drag and drop of the excel window
                  Local lnStyle
 
                  * Get the current style of the window
                  lnStyle = GetWindowLong(nHwnd, -6)
 
                  * Set the new style for the window
                  SetWindowLong(nHwnd, -16, Bitxor(lnStyle, 0x00400000))
 
                  * force it inside our form
                  SetParent(nHwnd,Thisform.HWnd)
 
                  * Size it
                  Thisform._SetCoord()
 
                  * Hide shape
                  Thisform.Shape1.Visible = .F.
            Endif
      Endproc
 
      Procedure _SetCoord
            * size it based on Invisible shape
            With This.Shape1
                  SetWindowPos(This._nHwnd, 0, .Left, .Top, .Width, .Height, 2)
            Endwith
      Endproc
 
Enddefine

 

Parting Words:

First of all, many Thanks to Bernard Bout whose post serves as my inspiration for this approach.  Special thanks to Yousfi Benameur who shared to us as well before the codes to hide the ribbon of Excel on Office 2007.

Whenever I am doing this style of write up, I am aiming for readers who are new to these to try to understand the purpose of each steps/objects/codes.  What is the purpose of WinExec here, why we need knowing the caption, what is the purpose of the shape on form, etc?.

Another is my readers are coming from different parts of the world where English is not the native language and I guess my write up approach is one of the reasons why my blog still gets new page views now and then.  Though I hope I am not boring you this way.  :)

Time to sleep....