viewpoint-particle

Author Topic: More interesting, but a little annoying  (Read 170 times)

stanl

  • Pundit
  • *****
  • Posts: 612
More interesting, but a little annoying
« on: June 06, 2017, 12:15:41 pm »
Easily testable

I open any Excel file on my laptop.  Run a simple function in Access
Code: [Select]
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
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

  • Pundit
  • *****
  • Posts: 721
    • Data & Stuff Inc.
Re: More interesting, but a little annoying
« Reply #1 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

stanl

  • Pundit
  • *****
  • Posts: 612
Re: More interesting, but a little annoying
« Reply #2 on: June 07, 2017, 03:47:47 am »
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
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

  • Tech Support
  • *****
  • Posts: 2055
    • WinBatch
Re: More interesting, but a little annoying
« Reply #3 on: June 07, 2017, 08:39:18 am »
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.
"DON'T PANIC”
The Hitchhiker's Guide to the Galaxy, Douglas Adams


stanl

  • Pundit
  • *****
  • Posts: 612
Re: More interesting, but a little annoying
« Reply #4 on: June 07, 2017, 12:34:09 pm »
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

  • Tech Support
  • *****
  • Posts: 2055
    • WinBatch
Re: More interesting, but a little annoying
« Reply #5 on: June 07, 2017, 12:53:23 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.
"DON'T PANIC”
The Hitchhiker's Guide to the Galaxy, Douglas Adams


stanl

  • Pundit
  • *****
  • Posts: 612
Re: More interesting, but a little annoying
« Reply #6 on: June 08, 2017, 03:21:11 am »
Thanks. I am running a Win7 laptop (with admin rights)

I set up this test:
Code: Winbatch
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

  • Tech Support
  • *****
  • Posts: 2055
    • WinBatch
Re: More interesting, but a little annoying
« Reply #7 on: June 08, 2017, 07:19:43 am »
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.
"DON'T PANIC”
The Hitchhiker's Guide to the Galaxy, Douglas Adams


stanl

  • Pundit
  • *****
  • Posts: 612
Re: More interesting, but a little annoying
« Reply #8 on: June 08, 2017, 10:19:42 am »
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  >:(