BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ScottBass
Rhodochrosite | Level 12

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    


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
DaveHorne
SAS Employee

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&#39;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

View solution in original post

13 REPLIES 13
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
DaveHorne
SAS Employee

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&#39;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

ScottBass
Rhodochrosite | Level 12

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


DCOM Config SAS Workspace Server.png

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
ScottBass
Rhodochrosite | Level 12

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!


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
DaveHorne
SAS Employee

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

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Iramesh
Calcite | Level 5

Hi All,

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

Regards

Ramesh

ScottBass
Rhodochrosite | Level 12

Try these, then ask follow up questions:

https://www.google.com.au/search?q=google&oq=google&aqs=chrome.0.0j69i65l2j0j69i65j0.1088j0&sourceid...

Windows PowerShell - Wikipedia, the free encyclopedia


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 29021 views
  • 6 likes
  • 5 in conversation