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.Orientat
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.
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.Orientat
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.
Rory Archibald
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.
ASKER
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.
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.
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
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.
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
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
ASKER
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
DataTrendRatioExample.xlsx
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?
ASKER
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
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";
}
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I still have no definitive answer, but I am convinced that this is right.
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.
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.
ASKER
I suspect that would be a viable workaround. Thanks for the suggestion. I'm not working the problem any more.
Thanks again.
Thanks again.
No problem, I really don't know why I didn't suggest it in the first place.
It seems pretty obvious.
It seems pretty obvious.