Excel helper wrapper

[BasicScience]

@Zippy

Thanks for persisting. I am using Office 365 with Excel Version 16.0.4405.1000 64-bit.

I'll see if it's possible to force an update.

[OSchmidt]

I do not know - and haven't looked at the Excel-Helper-Module code - but from what I've seen in other FB<->COM-examples,
there's often no real care taken about the RefCounts (Release) of temporarily created "SubObjects"...

As e.g, when one will create a new XL-WorkBook per: WB = xlApp.WorkBooks.Add() -
it is important to relase the "intermediate" WorkBooks-instance, which was returned
by the xlApp.WorkBooks-Method (to be able to use its .Add-method)...

If that is not done properly (either per automatic destructors - or explicitely) - before calling the
xlApp.Quit-method, then there's often the already mentioned Zombies to admire (in the taskmanager).

In the Windows-SubForum I've recently posted an approach (currently for FB32-GAS only), which is
hosting the COM-instances in an "Ole-Variant-with-auto-freeing" (Typename: vbVariant).
You might look other examples up over there (in the thread titled "SimpleVariant.bi").

Here's an example, which basically does the same as the one from Zippy, using SimpleVariant.bi instead of the Excel-Helper...:

Code: Select all

#define UNICODE
#Include Once "SimpleVariant.bi"

'instantiate the first xlApp (xlApp1) - add and retrieve a new WorkBook - and show its name
Dim As vbVariant xlApp1 = CreateObject("Excel.Application")
                 xlApp1.Put("Visible", "b", True)
Dim As vbVariant xlWkb1 = xlApp1.Get("WorkBooks").Call("Add")
          MsgBox xlWkb1.Get("Name")

'instantiate the second xlApp (xlApp2) - add and retrieve a new WorkBook - and show its name
Dim As vbVariant xlApp2 = CreateObject("Excel.Application")
                 xlApp2.Put("Visible", "b", True)
Dim As vbVariant xlWkb2 = xlApp2.Get("WorkBooks").Call("Add")
          MsgBox xlWkb2.Get("Name")

Sleep 2000 'wait a moment before desctruction of the two Excel-instances (but not really necessary)

'note, that the vbVariant.Clear calls below aren't really necessary for this example to work, because
'any vbVariant will auto-clear itself anyways, when it goes out of scope (here the scope of the FB-Process)
'Though, calling the .Clear-method explicitely is sometimes useful, when a certain order needs to be maintained,
'or to make "the intent" more obvious to the reader of the source (in the sense of an "explicit-coding-style")

'xlWkb1.Clear 'release the ObjectReference of WorkBook1 (from inside the Variant-Variable)
xlApp1.Call("Quit") 'Quit xlApp1
'xlApp1.Clear 'release the ObjectReference of xlApp1 (from inside the Variant-Variable)

'xlWkb2.Clear 'release the ObjectReference of WorkBook2 (from inside the Variant-Variable)
xlApp2.Call("Quit") 'Quit xlApp2
'xlApp2.Clear 'release the ObjectReference of xlApp2 (from inside the Variant-Variable)

Edit1: Changed the above example, to use CreateObject in the Constructor of the Var-Declare/Initializer

In my tests (with Excel 2013) the instances die as they should (not leaving Zomby-Processes in the TaskManager)...

HTH

Olaf

[BasicScience]

@OSchmidt

I will be happy to test this with MSO 2016, but I couldn't get SimpleVarient.bi to compile with your code above.

In the BI file:
line Mssg
74 Variable not declared, CBool
268 Duplicated definition
269 Duplicated definition
270 Duplicated definition
382 Array not dimensioned, before '('
386 Array not dimensioned, before '('
391 Duplicated definition
394 Duplicated definition
398 Duplicated definition

[OSchmidt]

@OSchmidt

I will be happy to test this with MSO 2016, but I couldn't get SimpleVarient.bi to compile with your code above.

In the BI file:
line Mssg
74 Variable not declared, CBool
...

Just parsed a few preceding posts of yours in this thread - and it seems you're using
an already somewhat older version (1.03) of the FB32-compiler for Windows...

I'd say, all these errors are caused (directly or indirectly) by the missing Boolean-support
(which was introduced with FBC 1.04: http://freebasic.net/forum/viewtopic.php?f=1&t=23986)

So far I've tested SimpleVariant.bi with FB32-1.05 and a "nightly build 1.06" - all working
flawlessly (on XP as well as on Win8.1).

Sorry if that's a showstopper (in case you have to use the older FB-version for some reason)...

Olaf

[Zippy]

@BasicScience

I did have the thought (!) that you might not have permissions to update Office on your test system, before I suggested this. But I hope that you can and have updated and the Zombies have been defeated.

If not... I have test code that will eliminate the "Global" factor, and demonstrate how to get the process id for the Zombie excel.exe processes in order to spike them in the head. The code creates the 2 workbooks like the previous test code (which I didn't rem is MINE) but without the obfuscation of "xlhelper.bas".

Please try this with the "#define NoGlobal" line commented and uncommented.

Code: Select all

#include once "windows.bi"
#define UNICODE
#include once "disphelper\disphelper.bi"
#undef UNICODE
' 
' ===== uncomment the #define below to test without the Global
'
'#define NoGlobal
'
'
dim as HWND xlApp1HWND,xlApp2HWND
dim as HRESULT res
declare function ZombieCheck(xlHWND as HWND,appname as string ="") as HRESULT
'
dhInitialize(TRUE)
dhToggleExceptions(TRUE)
'
dim xlApp1 as IDispatch Ptr
dim xlApp2 as IDispatch Ptr
'
#ifdef NoGlobal
   dim wbooks1 as IDispatch Ptr
   dim wbooks2 as IDispatch Ptr
#endif
'
'=======================================
'
print "Starting Book1"                     
dhCreateObject("Excel.Application",NULL,@xlApp1)
dhPutValue(xlApp1,".DisplayFullScreen = %b",FALSE)
#ifdef NoGlobal
   print "  NoGlobal .Add"
   res=dhGetValue("%o",@wbooks1,xlApp1,".Workbooks")
   res=dhCallMethod(wbooks1,".Add")
#else
   print "  Global .Add"
   dhCallMethod(xlApp1,".Workbooks.Add")
#endif
dhPutValue(xlApp1,".Visible = %b",TRUE)
res=dhgetvalue("%d",@xlApp1HWND,xlApp1,".hwnd")
'
'=======================================
'
print "Starting Book2"
dhCreateObject("Excel.Application",NULL,@xlApp2)
dhPutValue(xlApp2,".DisplayFullScreen = %b",FALSE)
#ifdef NoGlobal
   print "  NoGlobal .Add"
   res=dhGetValue("%o",@wbooks2,xlApp2,".Workbooks")
   res=dhCallMethod(wbooks2,".Add")
#else
   print "  Global .Add"
   dhCallMethod(xlApp2,".Workbooks.Add")
#endif
dhPutValue(xlApp2,".Visible = %b",TRUE)
res=dhgetvalue("%d",@xlApp2HWND,xlApp2,".hwnd")
'
'=======================================
'
print
print "<Enter> to 'Quit' the xlApps";
sleep
print:print
'
#ifdef NoGlobal
   SAFE_RELEASE(wbooks1)
   SAFE_RELEASE(wbooks2)
#endif
'
res=dhcallmethod(xlApp1,"Quit")
if res=0 then
   print "sent xlApp1 'Quit', call reports success"
else
   print "sent xlApp1 'Quit', call reports failure"
end if
'
dhcallmethod(xlApp2,"Quit")
if res=0 then
   print "sent xlApp2 'Quit', call reports success"
else
   print "sent xlApp2 'Quit', call reports failure"
end if
'
SAFE_RELEASE(xlApp1)
SAFE_RELEASE(xlApp2)
'
dhUninitialize(TRUE)
'
print
print "xlApps were 'Quit', checking for Zombies now"
print
'
res=ZombieCheck(xlApp1HWND,"xlApp1")
res=ZombieCheck(xlApp2HWND,"xlApp2")
'
print:print "Sleeping to exit..."
sleep
'
function ZombieCheck(xlHWND as HWND,appname as string) as HRESULT 'unused return
'
   dim as long procid,res,gerr
   dim as HANDLE kproc
   '
   res=GetWindowThreadProcessId(xlHWND,@procid)
   '
   if procid=0 then
      print appname;" process appears to have 'Quit' sucessfully"
   else
      print appname;" PID: ";procid;"  still active, <Enter> to force termination"
      sleep
      '
      SetLastError(0)
      kproc=_
         OpenProcess(SYNCHRONIZE or PROCESS_TERMINATE,FALSE,procid)
      gerr=GetLasterror
      '   
      if kproc>0 then
         SetLastError(0)
         res=TerminateProcess(kproc,0)
         gerr=GetLastError()
         CloseHandle(kproc)
         print appname;" was forcibly terminated"
      else
         print "OpenProcess() & forcible termination failed, err: ";gerr
      end if
   end if
   '
   return 0
'
end function

I need to know the status of your Office update. Then if that didn't resolve the Zombie issue I need the results of the code above. As a last resort I might attempt to modify phishguy's code but will not accept responsibility for the project OR the modified code. I don't have time to deal with this.

Note, ALL of you, that I can't replicate the Zombie issue using either Excel 2013 (never, could I, on either Vista or Win10) or 2016 (and now I'm not certain that the one instance on 2016 wasn't caused by some other action or inaction on my part).
.

[BasicScience]

@Zippy,
Zombies still present after quit, but your kill PID routine worked perfectly.

@OSchmidt,
I'm away from the university now (at Big Sky Montana :-) ) so upgrades to excel might be behind the latest available. When I get time, I will upgrade to FB 1.05 and try our SimpleVarient code.

[BasicScience]

@Zippy,
I should have provided more details. It doesn't matter whether Add.Global is on or off. Quit always reports "success" and yet the process remains active. ZombieCheck finds the PID and successfully kills the process.

[Zippy]

@BasicScience

Good feedback, thanks. In your experience, at least, the "Global" issue with Office 2016 isn't. Would be good if you will test again if you update Office, and report the outcome.

When I say that:
res=dhcallmethod(xlApp,"Quit")
"reports success"; this means only that the call is syntactically correct, that xlApp is a pre-existing valid object, and "Quit" is a valid method for that object. We know it doesn't mean that the process has terminated.

Meanwhile... Here's a self-contained sub() version of the Zombie killer that you can add to your xlheper.bas and call in lieu of xlquit(). [code attached to the original test code, for testing...]:

Code: Select all

'xlhelper.bas test, multiple instances
'' Does excel.exe remain after xlquit() & xlrelease()
''  Compile as console prog.
''  Tested Win10 64bit, Excel 2013/16, FB 1.05.0 (01-31-2016), built for win32 (32bit) 
'
#include "xlhelper.bas"
declare Sub xlquitZombies()
'
'create 2 placeholder structures, 1 for each
'' book we are opening or creating
dim as IDispatch ptr book1,book2
'
'create 1st book
xlstart()
print "start book1 xlapp = ";xlapp
book1=xlapp 'store book1 pointer
'
sleep 1000
'
'create 2nd book
xlstart()
print "start book2 xlapp = ";xlapp
book2=xlapp 'store book2 pointer
'
sleep 1000
'
xlapp=book1 'set internal pointer to book1
'xlquit()
xlquitZombies()
xlrelease()
print "End book1 xlapp = ";xlapp
'
xlapp=book2 'set internal pointer to book2
'xlquit()
xlquitZombies()
xlrelease()
print "End book2 xlapp = ";xlapp
'
print
print "Exit in 5 seconds....."
sleep 5000
'
'
'add this to xlhelper.bas, use in lieu of xlquit():
sub xlquitZombies()
'
   dim as long procid,res,gerr
   dim as HWND xlHWND
   dim as HANDLE kproc
   '
   res=dhgetvalue("%d",@xlHWND,xlApp,".hwnd")
   if xlHWND<1 then return 'excel.exe window not present
   '
   xlcm(xlapp,"quit")
   '
   res=GetWindowThreadProcessId(xlHWND,@procid)
   if procid=0 then return 'excel.exe process not present  
   '
   SetLastError(0)
   kproc=_
      OpenProcess(SYNCHRONIZE or PROCESS_TERMINATE,FALSE,procid)
   gerr=GetLasterror 'failure if not ZERO, or kproc = ZERO
   '   
   if kproc>0 then
      SetLastError(0)
      res=TerminateProcess(kproc,0)
      gerr=GetLastError() 'failure if not ZERO
   end if
'
end sub

Note: the obfuscating xlhandle() function in xlhelper.bas can be used to return window handle.
.

[BasicScience]

@Zippy,
Thanks. xlquitZombies() works perfectly for my needs in xlhelper.

[Pim Scheffers]

Hi,

I'm having problems when switching sheets.
Both values in the test code below end up in the last selected sheet.
The sheet switching works (visually) however the values are written last so they end up in the last sheet.

does somebody have a solution for this peculiar problem?

Thanks, Pim

Code: Select all

screen 13

'<-----Excel help file
#include "xlhelper_full.bi"

'<-----try to open the Excel file
xl_open("Q:\Software\Working_dir\DMLS\DMLS_CSV_Input\TEST.xlsx", true)

'<-----select first sheet
xl_sheet_select("Mal")

xl_put_value(1, 1, "lalalalal")

'<-----select second sheet
xl_sheet_select("Part_Programs")

'<-----select the correct sheet
xl_put_value(2, 1, "olololololol")

xl_save()
xl_saved()
xl_quit_zombies()
xl_release()

[Aethelstan]

I'm having problems when switching sheets.

xlPutValue function works with FB object named "xlSheet", but the function xlSheetSelect does not currently actualize this object after selecting another sheet in Excel (only xlApp object is actualized, so xlGetValue should work fine, because it works with application object directly). As you can probably observe in your wrapper code, there was such a line in function xlSheetSelect prepared, but is commented out now, so the easiest way to make it run is to uncomment the line:

Code: Select all

Sub xlSheetSelect(s As String)
    xlcm(xlApp, "worksheets(%s).Select", s )
    'xlgv("%o",@xlSheet,xlApp,"ActiveSheet")
End Sub

Alternatively, you can write your own function xlGetSheet and run it before making changes to the active sheet content:

Code: Select all

Sub xlGetSheet()
   xlgv("%o",@xlSheet,xlApp,"ActiveSheet")
End Sub

[Pim Scheffers]

This indeed fixes the issue.

Thanks!