GetObject() - advice or hint

[stanl]

Never came across this before with GetObject():  Due to the merger, I have to process files across 2 domains with different login/pwds.  Working with the 2nd domain, I have access to a VDI which is slow and not useful for scripting.  Now, I can perform a runas() from a CMD prompt as administrator to open anExcel instance as my login to the second domain .

So the purpose is to open excel files with links to SQL Servers on that domain. Once the Excel file is opened  I can Update all links and Pivots then link the Excel to a Powerpoint which is then updated and saved as PDF with is emailed as report.

Anyway: in the test code below, I can attach to the excel instance opened as runas, but my Excel file does not recognize the domain [meaning when I attempt refresh of links it fails]. Have seen other users with similar problem on Google, but no real solution.

Not a WB problem as the script works - but looking for a hint or advice.

Code (WINBATCH) Select Expand


oXL=GetObject( "","Excel.Application")
Message("Excel Instance",oXL)

If oXL<>0
oXL.Visible          = 1
oXL.ScreenUpdating   = 1 
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
Endif
cXLS="[myExcel file]"

oXL.Workbooks.Open(cXLS)

[td]

Probably missing something obvious but why not just use "ObjectCreate" to create in Excel instance that runs with your alternative credentials?  This would avoid any inter-process communication security issues.

[stanl]

Doesn't appear to be that simple. (1) I don't have any special privileges on the network (2) runas accepts the domain\user but password has to be re-prompted (and I cannot use something like psexec to get around this).

So the instance has to be created as part of the runas command - hence need for getobject().  Even if I include a file name as part of runas and attach to the object, the linked queries will fail as the SQL Servers on the remote network are not recognized.

I can manually open the instance, and from that instance open an excel file on a SharePoint on the other domain and the linked queries work.  However, that involves multiple needs for user/password and the data still has to be moved back locally. Very time consuming.

As a workaround I am looking at a runas to open a cmd prompt for my credentials on the other domain. Then run a compiled script within that cmd window. The script then uses CreateObject() to open an excel file (from a menu).  That file has the data links removed and the sql saved as a .sql file locally. I found I could execute the SQL and use ADO to query the SQL Servers on the remote domain. Then just clear the range on the data tab in excel and use copyfromrecordset(), where opening powerpoint, updating and saving as PDF can be scripted. 

It's pretty slick and I'm putting it in my back pocket as it doesn't really save that much time from opening the VDI and processing manually [some of the queries take up to 50 minutes to complete]

[td]

Your description of the problem is missing some details but your solution sounds a lot like using CreateObject with alternate credentials.

[stanl]

Your description of the problem is missing some details but your solution sounds a lot like using CreateObject with alternate credentials.

No. And perhaps I thought I was being quite clear.  Using runas for Excel with credentials to the other domain while allowing me to open a workbook locally, does not respect the linked connections to servers in the other domain.  However, using runas as either a SQL Server connection or a cmd prompt does allow me to query the remote servers.  So, by removing the data links in the local workbooks I can populate the data that the links would by issuing the SQL Server query from ADO, loading the local workbook, and populating with CopyFromRecordset().  Of course, there would be no need for the kludge if I could VPN rather than using a VDI (which allows no transfer between the remote domain and my local or network folders). It's a big merger #2 and #4 Internet and Video providers nationwide.

[td]

Your third attempt at an explanation is much clear than the first two for this poor simple-minded folk.

[stanl]

poor simple-minded folk.

That's the way I feel every morning when I head to work :o

[ChuckC]

The "VDI" that your'e referring to is some sort of virtual desktop accessed via remote desktop, VNC or some other similar client application?

[stanl]

The "VDI" that your'e referring to is some sort of virtual desktop accessed via remote desktop, VNC or some other similar client application?

VMware Horizon Client

[ChuckC]

Ah, ok.

With the product development work that I've been doing for the past 10+ years, we encounter a lot of customers who are going thru integration of IT infrastructure due to mergers & acquisitions, with the occasional spin-off where the division or subsidiary is breaking away as an independent entity.  Anyway, for our products, it's all about managing storage and getting data moved, usually onto consolidated storage in a central data center.

In your situation, it sounds like the merger process hasn't gone along far enough for the separate AD forest environments to begin to mesh together.  If the IP connectivity that lets the VMware client connect back to the VMware server environment is also capable of providing all of the DNS services required for cross-forest connectivity, then your problem would most easily be solved by getting some trusts established to allow authentication & access control to work.  Ideally, fully bi-directional inter-forest trusts would be established and then it would all just work, provided that permissions are granted for logon rights & access to shares, file systems and databases.  Lacking the inter-forest type of trust, inter-domain trusts can be established directly between two child domains in different forests to achieve the same thing on a more direct and limited scale.

[td]

Nice.  A  solution based on failing to see the forest for the trees...