topic Re: Powershell script calling Excel COM object fails in a stored process in Administration and Deployment

Powershell script calling Excel COM object fails in a stored process

ScottBass — Fri, 23 Aug 2013 00:37:46 GMT

Hi,

This post is related to https://communities.sas.com/thread/48769.

I've created a Powershell script to shrink Excel XML files created by Excel tagsets. Since the script is short, here it is:

[CmdletBinding(SupportsShouldProcess=$true)]

param(

# full path to the original Excel file (usually in XML format)

[Parameter(

Position=0,

Mandatory=$true

)]

[Alias("Path")]

[ValidateScript({Test-Path $_})]

[System.IO.FileInfo]$xlFile

# ouptput format

[Parameter(

Position=1

)]

[Alias("Format")]

[ValidateSet("xlsx","xlsm","xlsb","xls")]

[String]$xlFormat="xlsb"

)

$ErrorActionPreference="Stop"

$xlPath=$xlFile.DirectoryName

$xlBasename=$xlFile.Basename

switch ($xlFormat) {

"xlsx" {$xlFormatNum=51; break}

"xlsm" {$xlFormatNum=52; break}

"xlsb" {$xlFormatNum=50; break}

"xls" {$xlFormatNum=56; break}

}

$Excel = New-Object -ComObject excel.application

$Excel.visible = $False

$Excel.displayalerts=$False

$WorkBook = $Excel.Workbooks.Open($xlFile)

$WorkBook.SaveAs((Join-Path $xlPath "$xlBasename.$xlFormat"), $xlFormatNum)

$Excel.quit()

The script works fine in these scenarios:

1) Invoked directly from a cmd or powershell window.

2) Invoked from a Base SAS session, i.e. DMS

3) Invoked from EG if I use a domain profile, i.e. <domain>\<myuserid>, <password>

The script fails in these scenarios:

1) Invoked from a stored process running on a stored process server, which is running as sassrv.

2) Invoked from EG if I use an Integrated Windows Authentication profile.

3) Invoked from EG if I use a local machine account profile, i.e. <machinename>\sasdemo, <password>

I've tried numerous ways of invoking it: X, system(), systask, WinExec (see SASCBTBL Routine Statements for use with SAS ModuleN() routine - by Richard A. DeVenezia), filename pipe. filename pipe gives the best error messaging:

%let excel_xml=R:\Temp\Book1.xml;

filename in pipe

"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe

-noprofile

-noninteractive

-executionpolicy unrestricted

-windowstyle hidden

-command ""E:\Powershell\Scripts\ShrinkExcel.ps1 -xlfile '&excel_xml' -xlformat xlsb"" "

;

data _null_;

infile in;

input;

putlog _infile_;

run;

filename in;

Here is the log output:

NOTE: The infile IN is:

Unnamed Pipe Access Device,

PROCESS=C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -noprofile -noninteractive -executionpolicy unrestricted

-windowstyle hidden -command "E:\Powershell\Scripts\ShrinkExcel.ps1 -xlfile 'R:\Temp\Book1.xml' -xlformat xlsb",

RECFM=V,LRECL=256

Stderr output:

E:\Powershell\Scripts\ShrinkExcel.ps1 : Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the

file 'R:\Temp\Book1.xml'. There are several possible reasons:

_ The file name or path does not exist.

_ The file is being used by another program.

_ The workbook you are trying to save has the same name as a currently open workbook."

At line:1 char:1

+ E:\Powershell\Scripts\ShrinkExcel.ps1 -xlfile 'R:\Temp\Book1.xml' -xlformat xlsb

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [ShrinkExcel.ps1], MethodInvocationException

+ FullyQualifiedErrorId : ComMethodTargetInvocation,ShrinkExcel.ps1

The SAS process is running on a workspace server, where R: and E: are local drives to that server (i.e. not mapped). R: has Everyone full access, so it isn't a permissions issue. The workbook is not open.

If I change nothing, except my EG profile to my domain account, the code runs fine.

I note these blog posts by : IWA - platformadmin.com and wonder if these could be the issue?

The problem seems to be specific to the excel.application COM object. If I change my Powershell script to run something simple, like copy the XML file to a new name, it runs fine.

I'd really like to be able to run this script on our stored process server. We have a number of remote users that are experiencing performance issues in downloading the huge XML files to their location. Changing the stored process to run under the workspace server is not an option, as it needs to stream output to the user's browser.

Thanks for any help you can provide.

Regards,

Scott

Re: Powershell script calling Excel COM object fails in a stored process

ChrisHemedinger — Fri, 23 Aug 2013 12:48:39 GMT

Scott,

It could be a couple of things.

One might be that with the local accounts (sassrv and sasdemo), the R: drives are not mapped in the login profile, so you might have to reference the UNC paths instead (\\myserver\path).

Or, it might be that you have to enable the server machine as Trusted for Delegation so that it can access these network resources on behalf of the account.

Or it might be both of these issues.

Chris

Re: Powershell script calling Excel COM object fails in a stored process

ScottBass — Fri, 23 Aug 2013 14:03:49 GMT

Thanks Chris.

C:, D:, E:, R:, S:, T: are all local drives on the server (VMWare image). IOW, if I go into Disk Manager, I see all these drives, they are not mapped by a login script.

I'll investigate the Trusted for Delegation link. But it is interesting that it only happens for the Excel COM object, Open method.

Regards,

Scott

Re: Powershell script calling Excel COM object fails in a stored process

ChrisHemedinger — Fri, 23 Aug 2013 14:26:37 GMT

I wonder if you can log into the server directly, and attempt to run your script via a runas command using the local accounts (sassrv or sasdemo to test). That might tell you at least whether those local accounts can run the COM commands in that script.

If that fails (and I'm stretching my knowledge here), you might need to tweak the DCOM configurations (dcomcnfg tool). See this topic for a hint (relates to a scheduled task).

Chris

Re: Powershell script calling Excel COM object fails in a stored process

DaveHorne — Sun, 25 Aug 2013 15:55:04 GMT

Hey Scott,

We use the ODS Excel tagset extensively and have a very similar PowerSherll script to convert the XML file to .xlsb (and apply a bit of formatting). This has been working fine until we recently upgraded to a new server and I installed Office 2013 (previously had Office 2010). We started to get the same errors and the solution I found was to update the credentials on the DCOM object (found via this link: powershell - Why can't Excel open a file when run from task scheduler? - Super User). I'm not sure if this will apply to your scenario but it took care of the issue we were running into.

Cheers,
Dave

Re: Powershell script calling Excel COM object fails in a stored process

ScottBass — Thu, 29 Aug 2013 04:47:37 GMT

Hi,

Thanks Chris and Dave.

Our SAS Workspace Server machine is Windows 2008 R2 Enterprise x64. The Office version on that machine is Excel 2007 SP3. I think it's the 32bit version; Excel Help --> About doesn't say, but the Excel executable is in C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE.

I've invoked the DCOM Configuration Manager on that machine in accordance with the link provided. However, I don't see a Microsoft Excel Application entry. I've attached a screenshot.

Any ideas?

Scott

Re: Powershell script calling Excel COM object fails in a stored process

ScottBass — Thu, 29 Aug 2013 12:01:05 GMT

Hi,

More Googling revealed this Microsoft Excel Application entry missing in DCOMCNFG . I followed tibb's recommendation, found Microsoft Excel Application, and made the changes as recommended by Dave, i.e. changed the Identity tab to use <machinename>\sassrv for DCOM.

It's working now, thanks so much for your help!!!

Scott

P.S.: Dave, if your PS script doesn't contain private IP, I'd love to get a copy of it. I'm always looking for more PS examples!

Re: Powershell script calling Excel COM object fails in a stored process

DaveHorne — Thu, 29 Aug 2013 17:40:23 GMT

Hi Scott,

I trimmed down the script we use to just the Excel basics (the original script has a lot of error checks specific to our directory structures along with emails). While we're processing the file, we added a step to call autofit in Excel to clean up the appearance (cell spacing) in the final output (script file attached). In our SAS job, we write the ODS file out to the work directory and then call the PS script with the system command.

Like you, I'm always interested in scripting with PowerShell. Chris has several great 'PowerShell with SAS' related posts on his blog: PowerShell - The SAS Dummy and an SGF13 paper: http://support.sas.com/resources/papers/proceedings13/003-2013.pdf

All the best,

Dave

Re: Powershell script calling Excel COM object fails in a stored process

Quentin — Fri, 30 Aug 2013 11:18:28 GMT

Hi,

I'm going to try jumping in with a related question.

So the general idea here is that you are writing an ODS output file to the work directory, post-processing it, and then streaming it back to _webout.

I've been doing something similar, not for editing the file, but for delaying delivering the report until I have scanned the log:

http://www.bi-notes.com/2012/07/stored-process-ignore-log-your-own-peril

I have an ugly macro that I use to stream the file back to the user. Which is basically a data _null_ step writing to file _webout. But it several options for different file types (html/rtf/pdf/xml), to set both stpsrv_header and other options. For pdf, the data _null_ step does a binary copy using this approach: http://support.sas.com/kb/6/588.html

For html/rtf/xml it's mostly just PUT _INFILE_ , but it gets tripped up by LRECL every once in a while (or I just set LRECL to be insanely long).

Was wondering if someone has a generic %StreamToWebOut(file=) macro that they like and want to share, where the macro would stream any file sitting on the server back to _webout.

Was thinking maybe I should just use the binary copy method for every file type....

Thx,

-Q.

Re: Powershell script calling Excel COM object fails in a stored process

ChrisHemedinger — Fri, 30 Aug 2013 12:43:35 GMT

Quentin,

I've used an approach like this (which I shared in this article😞

filename in "myFileName.xlsx";

/******************************************************/

/* With Excel file created, now to stream it back to */

/* client with _webout and the "attachment" directive */

/* For use with SAS Stored Process Web Application */

/******************************************************/

/* Specify HTTP header records */

data _null_;

rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

rc = stpsrv_header('Content-disposition',"attachment; filename=myFileName.xlsx");

run;

/* stream content byte-by-byte to _WEBOUT */

data _null_;

length filein 8 fileid 8;

filein = fopen('in','I',1,'B');

fileid = fopen('_webout','O',1,'B');

rec = '20'x;

do while(fread(filein)=0);

rc = fget(filein,rec,1);

rc = fput(fileid, rec);

rc =fwrite(fileid);

end;

rc = fclose(filein);

run;

filename in clear;

Chris

Re: Powershell script calling Excel COM object fails in a stored process

Quentin — Sat, 31 Aug 2013 22:07:51 GMT

Thanks much Chris,

That looks promising as the core of a %StreamToWeb(), or maybe more generically, just %BinaryCopy(in=,out=) macro.

Must have missed that post from your archives. Thanks for sharing the link as well.

--Q.

Re: Powershell script calling Excel COM object fails in a stored process

ChrisHemedinger — Sun, 01 Sep 2013 13:50:14 GMT

Glad it's helpful. Actually, has developed a more robust "binary copy" program and it's on my to-do list to share it on the blog. My version might be good enough for your purposes, but I'll promote Bruno's version soon (given this prompt....)

Chris

Re: Powershell script calling Excel COM object fails in a stored process

Iramesh — Tue, 17 Sep 2013 07:00:01 GMT

Hi All,

Can any one explains about "Power shell Scripting". Is it works in Windows 32-bit O.S.

Regards

Ramesh

Re: Powershell script calling Excel COM object fails in a stored process

ScottBass — Tue, 17 Sep 2013 07:29:59 GMT

Try these, then ask follow up questions:

https://www.google.com.au/search?q=google&oq=google&aqs=chrome.0.0j69i65l2j0j69i65j0.1088j0&sourceid=chrome&ie=UTF-8#q=what+is+powershell

Windows PowerShell - Wikipedia, the free encyclopedia