Lately I meet such a problem, on which I was struggling for a week and couldn't find any solution.

Lady want to edit an excel file, to add some emails there and after clicking on a magical button those contacts should be imported to an Exchange distribution list.

My solution was to make a website written in PHP, that with use of might "shell_exec" will run Powershell script, which will:

1. Connect and read the *.xlsx file

2. remove all mail contacts that are mentioned in clicked distribution list,

3. Add mail contacts to exchange and associate them to appropriate distribution list.

4. Report the status on the operation

I was testing the script locally, I mean, I was simply running it from Powershell console, dealing with error handling and other stuff, the user interface has been left for the last stage of the project. As the script started to work properly the time has come and I have executed the query from PHP - just as I used to do.

The result was unexpected, because I have received.. zero, null, nothing, not even a single error! Just imagine how dramatic that moment has been! :)

So I have done what real detective should done - I was started to debug the script line by line (those main problematic lines are below), and what have appeared, was that line 3 - displaying my object - was correct, because from Powershell I was receiving:

 

And from PHP:

1. $ExcelDocumentPath = "C:\wamp\www\mail_contact_base.xlsx"

2. $ExcelDocument = New-Object -ComObject "Excel.Application"

3. $ExcelDocument <- here was still ok :)

4. $Workbook = $ExcelDocument.workbooks.open($ExcelDocumentPath) <- here was starting shit :)

5. $Workbook

6. $WorkSheets = $Workbook.Worksheets

Buuuut! The line no. 5 gave me...NULL! Not even something like "0" simply there was black page. So it has appeared that with no doubt I have problem with opening the excel object :/

I have redirected the line to 2>&1 to get something more, and what appeared was:

Exception calling "Open" with "1" argument(s): "Brak aktywnego okna widoku chro nionego." At C:\wamp\www\ps.ps1:4 char:42 + $Workbook = $ExcelDocument.workbooks.open <<<< ($ExcelDocumentPath) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

It is in mine language, because I couldn't find zero of that error in English, it says about lack of "ProtectedViewWindow Object", my first thought was that I am in big shit...

I have posted that on http://wss.pl forum and one guru gave me link-> http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757 saying that it would be as hard as I thought or harder :|

I have clicked from side to side trust options in office on that server, nothing helped. So I thought that I will reinstall MS Office application, the server was x64 and the Office was x32, so I thought this is the issue.

After reinstalling I have received another error - about wrong locale - from Powershell and from PHP:

 "Exception calling "Open" with "1" argument(s): "Old format or invalid type....blah blah stuff and other shit here"

But the languages were ok, so I was kind of missing my old error, and I have reinstalled MS Office once again for the same as it was - x32.

After that operation - reinstalling MS Office - the script was running perfectly from Powershell - just as it was before, but in PHP I was receiving..the lack of privileges - no access to the file- deeeeeep shiiit.

I was near becoming insane, it was the last day for finishing that project in, let's say, first work version, and I had nothing. So I started to ask uncle Google, what to do, I still wanted to have my old "lack of ProtectedViewWindow Object" error.

Surprisingly I have found this:

http://stackoverflow.com/questions/5516221/excel-application-not-working-in-Powershell-using-sql-job-but-works-from-comman

The guy is saying that I should do this:

    1. Add Directory: C:\Temp
    2. Add Directory: C:\Windows\SysWOW64\config\systemprofile\Desktop

That should fix the problem.

Indeed there was no “temp” directory on a folder neither “desktop”. I have added them, and run the script, and I have received.. NULL. But wait a minute! I have redirected the errors to standard output, so if there is no error on the page.. is it possible!?!?!

Yup, the guy saved my life :) there was no error about "ProtectedViewWindow" more, and the mail contact from Excel file, has showed up in the Exchange and later on the appropriate distribution list J