More interesting, but a little annoying

Started by stanl, June 06, 2017, 12:15:41 PM

Previous topic - Next topic

stanl

Easily testable

I open any Excel file on my laptop.  Run a simple function in Access

Function xltest()
Dim appXL As Object
Set appXL = GetObject(, "Excel.Application")
MsgBox (appXL.activeworkbook.Name)
End Function



works perfectly.  Now from WB Studio run

Code (WINBATCH) Select

oXL=GetObject(,"Excel.Application")

message("Excel",oXL.ActiveWorkBook.Name)


And I get OLEInitiate Fail....  and I have multiple scripts using CreateObject with Excel.  Might just be my laptop, but I have used GetObject() before.

JTaylor

Same happens with me.   Don't think I have ever used Excel in this fashion so can't say more than that.

Jim

stanl

Quote from: JTaylor on June 06, 2017, 12:41:39 PM
Same happens with me.   Don't think I have ever used Excel in this fashion so can't say more than that.

Jim

my bad, for WB this works:
Code (WINBATCH) Select

oXL=GetObject("","Excel.Application")
message("Excel Object",oXL)


The issue I am having is to automate a huge Excel template on a remote SharePoint protected by 2 layers of permissions. What I am trying to test is if the workbook is opened manually and checked out can I reference it through GetObject() which so far has proved fruitless.


td

GetObject("","Excel.Application") does not access a running version of Excel.  It creates a new object.   You need to run the script with the same integrity level used to start Excel to access a running object in the ROT.  On Windows 10 with the usual configuration, this means you need to change the file extension of the script from .wbt to wbt_if or manifest a compiled script to the equivalent.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: td on June 07, 2017, 08:39:18 AM
GetObject("","Excel.Application") does not access a running version of Excel.  It creates a new object.   

I know that - however GetObject("c:\temp\test.xlsx") will allow you to control a workbook that is already opened by another instance (or at least in vb/vba). This is what my intention was since I cannot access the sharepoint template with CreateObject() and Open().  Of course, in looking over a script I wrote years ago, I think a Findwindow("XLMAIN",0) is what is needed to detect a running instance, then after GetObject you have to address the application object. 


td

Quote from: stanl on June 07, 2017, 12:34:09 PM
I know that - however GetObject("c:\temp\test.xlsx") will allow you to control a workbook that is already opened by another instance (or at least in vb/vba). This is what my intention was since I cannot access the sharepoint template with CreateObject() and Open().  Of course, in looking over a script I wrote years ago, I think a Findwindow("XLMAIN",0) is what is needed to detect a running instance, then after GetObject you have to address the application object.

You can use either ObjectGet("c:\temp\test.xlsx") or ObjectGet(,'Excel.Application') to access a running instance with WinBatch but  as previously mentioned, you must be running at the same integrity level as the existing Excel instance in either case.   The integrity level requirement is simply how MSFT setup  ROT security and is not specific to WinBatch.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Thanks. I am running a Win7 laptop (with admin rights)

I set up this test:
Code (WINBATCH) Select

WM_USER = 1024
win=Findwindow("XLMAIN")
If win<>""
  hWnd=DllHwnd(win)
  SendMessageA( hWnd, WM_USER+18, 0, "")
  Message("Excel",win:" in Running Object Table")
  oXL=GetObject(win)
  oXL.Application.Visible = @True
  oXL.Parent.Windows(1).Visible = @True
Else
  Message("Excel","Not Currently Running")
Endif
Exit


will report if Excel Not running, and will get to the message if it is. However GetObject() gives and OLEInitiate error (from studio). Probably because the 'win' variable does not include the path, but would you consider this a correct procedure.

td

Windows 7 and Windows 10 have the same ROT security rules.  Running as an admin on Windows 7 will not automatically give you access to an object in the ROT.  The WinBatch process must be at the same integrity level as the targeted process's object in order for access to be granted - just like on Windows 10.  If Excel is running from a restricted access token process and the script is running from a full admin token process - as would happen when running a script with the .wbt extension from within WinBatch Studio with UAC enabled, the script will not be able to access the ROT object.

The register an object in the ROT message should guarantee that Excel is registered but generally Excel will registers itself unless it never gets the input focus.  You are correct in that you need to use a full path.  If you don't use a full path but a file name with an Excel associated extension, the Excel object initialization will attempt to find the specified file after starting a new instance of Excel.  Of course, this will lead to an error if no such file exists in any of  the standard Windows search locations.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Once again the annoyance was me. I have admin rights but the company controls the UAC. It required turning it off and a reboot and the GetObject() worked. And it was important to remember to reference the .Application Object when actually manipulating data. And, of course, once I had it figured out it is no longer needed  >:(