Excel Automation: Unable to set the Orientation property of the PageSetup class

I have a web application that generates various reports in Excel or in Word and then downloads them to the client where they can be printed.  Even though there is nothing being printed on the web server, I want to properly format the reports in terms of page orientation, etc.  Everything is working great with Word.

In Excel, whenever I try to change the page orientation to Landscape I get:

COMException (0x800a03ec): Unable to set the Orientation property of the PageSetup class

The single line of code that is causing this is:

mySheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;

If I comment out the line everything else works like a champ.  The code also works fine when I test it on my local computer (without the line commented out).

On the server, the web application runs with the username CCSUser.  I can log onto the server using that username, bring up Excel manually, create some sort of spreadsheet, and set the orientation of that spreadsheet to Landscape.  It works fine.  It just does not work when I try to do it from my C# code.

There is no physical printer attached to the server.  The default printer for the account used by the web application is Microsoft XPS Document Writer.  The security permissions on that printer allow me to Print, Manage the Printer, and Manage Documents.

We had the same problem before our recent upgrade from Windows Server 2000 to 2008.  I had hoped that it might magically go away when we upgraded.  It didn't.

What am I doing wrong?

Thanks.
TimAttawayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Can you manipulate other page setup properties such as paper size? If not then it would appear that the Excel instance is not recognizing that there is any printer set up at all.
TimAttawayAuthor Commented:
Other page properties fail as well, such as trying to turn on one page printing.

Again, the same application can set the property from Word.  I can log onto the server using the userid under which the web application runs, bring ip Excel, and set the property.  It only fails trying to do it programmatically from the web application.  I suspect your assumption is right, that Excel isn't recognizing the printer.  I just don't know why.
MWGainesJRCommented:
It Works fine for me.  This is my code: (in VB.Net)
 

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xl As Excel.Application = New Excel.Application
        Dim wb As Workbook = xl.Workbooks.Add(1)
        Dim sh As Worksheet = wb.Sheets(1)
        xl.Visible = True
        sh.PageSetup.Orientation = XlPageOrientation.xlLandscape

    End Sub
End Class

Open in new window

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Rory ArchibaldCommented:
I can't say why it isn't working, but Microsoft have long said that Office is not designed to be run on a server. Perhaps this is one instance of why.
Eric ZwiekhorstSAP Business ConsultantCommented:
Hi Tim,

can you send us your workbook? the one you can't alter page setup..
It might help if I can have a look at that.

Kind regards

Eric
TimAttawayAuthor Commented:
Absolutely.  I commented out the line that is trying to set landscape mode, rebuilt and reinstalled the website, and generated the attached file.  It generates on the server and then downloads to the client side.  (Before I actually saved it I changed the customer names to "xxx".)  The resulting file is attached.
DataTrendRatioExample.xlsx
MWGainesJRCommented:
i'm not having any issue setting the orientation for that file programmatically.  Did you have a look at the code I posted?  Specifically references and syntax?
TimAttawayAuthor Commented:
I also did one other thing to try to isolate the problem.  I wrote a small application that has a single button.  When you click it the application opens an Excel application, creates a workbook with one sheet, sets the orientation of the sheet to landscape, and then stuffs a single value in a single cell.  I added a deployment project, built it, and installed it on my server.  I logged in with the same username that is assigned to the application pool running my website.  I ran the application.  It worked fine.

The problem seems to be isolated to doing this as a web application.

I can set orientation to landscape from a web application driving Word.
I can set orientation to landscape running Excel manually when logged in with the same username that my web application uses.
I can set orientation to landscape from a Windows program driving Excel when logged in with the same username that my web application uses.
I cannot set orientation to landscape from a web applcation driving Excel.

Here is the Windows code(which works) that follows the same few steps that my web application (which doesn't work) follows


private void button1_Click(object sender, EventArgs e)
        {
            Excel._Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
            xlsApp.Visible = true;
            xlsApp.SheetsInNewWorkbook = 1;
            Excel.Workbook newBook = xlsApp.Workbooks.Add(Type.Missing);
            Excel.Worksheet newSheet = (Excel.Worksheet)newBook.Sheets[1];
            newSheet.Name = "TestSheet";
            newSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
            Microsoft.Office.Interop.Excel.Range theCell = null;
            theCell = (Microsoft.Office.Interop.Excel.Range)newSheet.Cells[1, 1];
            theCell.Value2 = "here I am";
        }

Open in new window

TimAttawayAuthor Commented:
To MWGainesJR,

Thanks.  There is no substantial difference in your code and the code that I submitted above and they both work fine.  The only real differences that I see with the code that is giving me a problem is that (a) it runs with Visible set to "false" (since it is off running on a server somewhere, but I don't think that is the issue) and (b) it is running as part of a web application rather than a Windows application.  I suspect that the error has to do with the latter, but I just don't know what.  It works as a "web" application running on my local machine under the Visual Studio ASP.NET Development Server.

Thanks for the suggestion.
NorieAnalyst Assistant Commented:
You say the server has no printer setup?
Perhaps, if this is a web application, Excel is trying to access a printer on the server, and since there isn't one it errors.
I'm not 100% sure how page setup and printers work but one of the most common questions you'll see is, why
is page setup taking so long.
The usual answer is that it's something to do wtih printer drivers.
Why not try setting the ActivePrinter of the instance of Excel to a printer you know is installed and available?
You could do that before the page setup code and reset it afterwards.
TimAttawayAuthor Commented:
To imnorie,

There is no printer physically connected to the server.  The default printer defined for this account is Microsoft XPS Document Writer.  I can log in manually (using the same username that is assigned to the application pool assigned to the web application), bring Excel up manually, and change the page orientation.  I can run the C# Windows application above under the same account and it can change the page orientation.  This only fails when i try to change the page orientation from a Web application.
NorieAnalyst Assistant Commented:
Have you tried my suggestion?
I could be totally wrong, but perhaps it's worth a shot.
I've had trouble in the past when trying to do things in Web applications that you can do in Windows application
and one of the problems has been that things are be running on the Server rather than the Client.
Perhaps you could try a simple outputting of the name of the active printer, just to check..
Just an idea really.
TimAttawayAuthor Commented:
I've put some code in to check for the existence of a default printer (it logs what it finds to an event log) and it looks like it is not finding a default printer when running as a web application on the server.  Do web applications come up with a default printer assigned?  I am also trying to assign a default printer from the web application and then checking to see if it "took" and it looks like it is not working.
NorieAnalyst Assistant Commented:
Have you tried looking at the ActivePrinter property of the Excel instance?
Perhaps take a look at that in both the web and windows application, maybe that'll give some insight.
Another thing I was thinking off was it could be something to do with the launching and activating permissions.
I've seen other people trying to Automate Excel server-side not even getting as far as creating an instance of Excel.:)
One of the fixes for that is apparently to add NETWORK SERVICES to the permissions, though that might raise
security issues.
TimAttawayAuthor Commented:
After digging deeply into this and writing lots of code that generates lots of event logs tracing exactly what is happening on the server, I have concluded that I simply cannot do what I am trying to do.  I still have no definitive answer, but I have convinced myself that it is not going to work.  As best I can tell, when I log onto the server as a Windows user I have a default printer assigned to me.  When IIS logs the same userid on as a web application, there is not a default printer assigned.  As a windows user I can assign a default printer.  As a web user I cannot (I don't get an error when I try, but nothing really happens either).  The Excel Application provides an ActivePrinter property with both {get} and {set} interfaces, but if you try to set it you get an exception.  As far as I can tell from what little documentation I can find, the "set" interface is not intended to be used by user applications.

Thanks for everyone who tried to point me in a direction, but I think that the answer is simply that it cannot be done.  My end user will simply have to set the page oriientation manually before he prints.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimAttawayAuthor Commented:
I still have no definitive answer, but I am convinced that this is right.
NorieAnalyst Assistant Commented:
I don't know if it's been suggested, and I apologize if it has, but why not create a template file that has lanscape set already.
If that works you could also make it a one worksheet file and add any other formatting you might need.
All you would need to do, he said with fingers crossed, is replace Type.Missing in the Add method.
Not 100% sure what you would use exactly, in Excel it would just be the file name.
So, in theory, you wouldn't need to use code to set the orientation - it's already done.
TimAttawayAuthor Commented:
I suspect that would be a viable workaround.  Thanks for the suggestion.  I'm not working the problem any more.

Thanks again.
NorieAnalyst Assistant Commented:
No problem, I really don't know why I didn't suggest it in the first place.
It seems pretty obvious.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.