Unable Export Data to Excel on Win 2000 Server

[Vkarumbaiah]

Hi
I have a VB.Net 2003 Assembly in which I am
1 . executing a stored in a database and filling a dataset
2 . Populating the data retrieved in to an Excel Spread sheet
3 . Setting the PageSetup properties of the worksheet
4. saving the Excel File to disk

My code works fine on a windows XP machine with SP2 but fails at the
following line on Win 2000 With SP4


objXLWorksheet.PageSetup.PaperSize =
CType(Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLegal,
Microsoft.Office.Interop.Excel.XlPaperSize) 'Set Paper size to Legal

The exception Message is
Unable to set the PaperSize property of the PageSetup

My code is as below

Imports System.IO
Imports Office = Microsoft.Office.Core
Imports Microsoft.Office.Interop.Excel
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.Office.Interop.Excel.Constants
Imports System.Xml


Dim objExcelApp As New Microsoft.Office.Interop.Excel.Application
Dim objWB As Microsoft.Office.Interop.Excel.Workbook
Dim objSH As Microsoft.Office.Interop.Excel.Worksheet

'Open the file that was created Using the Excel Object model.
objExcelApp.Workbooks.Open("C:\temp\NewspaperReport.xls")

'Assign the Activeworksheet to a Worksheet object
objSH = objExcelApp.ActiveWorkbook.ActiveSheet

'Set properties for the Active Worksheet
With objSH
.Columns.ColumnWidth = 2.39
'Q1 Select
.Range("E1", "BD1").Interior.ColorIndex = 3
.Range("A1", "BD1").Rows(1).rowheight = 29.25
.Rows(1).Font.Name = "Arial"
.Rows(1).Font.ColorIndex = 2
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 12
.Rows(1).VerticalAlignment =
CType(Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter,
Microsoft.Office.Interop.Excel.XlVAlign)

.Name = "Local Newspaper"
.Columns.WrapText = False

.PageSetup.PaperSize =
CType(Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLegal,
Microsoft.Office.Interop.Excel.XlPaperSize)
.PageSetup.Orientation =
CType(Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape,
Microsoft.Office.Interop.Excel.XlPageOrientation)

.PageSetup.Zoom = 55

'Set Printing Margins
.PageSetup.HeaderMargin = objExcelApp.InchesToPoints(0.25)
.PageSetup.TopMargin = objExcelApp.InchesToPoints(0.5)
.PageSetup.LeftMargin = objExcelApp.InchesToPoints(0.25)
.PageSetup.RightMargin = objExcelApp.InchesToPoints(0.25)
.PageSetup.BottomMargin = objExcelApp.InchesToPoints(0.5)
.PageSetup.FooterMargin = objExcelApp.InchesToPoints(0.25)
.PageSetup.CenterHeader = "Local Newspaper"

.PageSetup.RightFooter = "Report generated on " & "&D page &P of &N"
.PageSetup.PrintTitleRows() = .Rows(1).Address
.PageSetup.PrintGridlines = True
.Range("E5", "E5").Select()
End With

 

[Jim Rech]

Just a guess - does the server have a printer installed? Page Setup will
fail if not I believe.

 

[vkarumbaiah]

hi Jim
The server is not directly connected to a printer however it can print to a
network printer that is accessed via print server on the network. Do you
think the printer 's drivers should be installed on the server where I am
trying to run the code?

Also the Win 2000 server I am using to run this code has terminal services
enabled, and is configured to run applications, do you know if this would
cause any problems?

Thank you for your reply
Vikram Karumbaiah

 

[Jim Rech]

Do you think the printer 's drivers should be installed on the server
If you can manually use File, Page Setup then you're okay. If you cannot do
it manually then a macro could not do it either. So it you can go into Page
Setup I'd go one step farther and try setting the paper size manually to
Legal. If you cannot do that (say the driver does not support that paper
size) it would explain the macro error. If it works okay I really don't
know why your code is failing.

--
Jim Rech
Excel MVP
| hi Jim
| The server is not directly connected to a printer however it can print to
a
| network printer that is accessed via print server on the network. Do you
| think the printer 's drivers should be installed on the server where I am
| trying to run the code?
|
| Also the Win 2000 server I am using to run this code has terminal services
| enabled, and is configured to run applications, do you know if this would
| cause any problems?
|
| Thank you for your reply
| Vikram Karumbaiah
|
|
| "Jim Rech" wrote:
|
| > Just a guess - does the server have a printer installed? Page Setup
will
| > fail if not I believe.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > > Hi
| > > I have a VB.Net 2003 Assembly in which I am
| > > 1 . executing a stored in a database and filling a dataset
| > > 2 . Populating the data retrieved in to an Excel Spread sheet
| > > 3 . Setting the PageSetup properties of the worksheet
| > > 4. saving the Excel File to disk
| > >
| > > My code works fine on a windows XP machine with SP2 but fails at the
| > > following line on Win 2000 With SP4
| > >
| > >
| > > objXLWorksheet.PageSetup.PaperSize =
| > > CType(Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLegal,
| > > Microsoft.Office.Interop.Excel.XlPaperSize) 'Set Paper size to Legal
| > >
| > > The exception Message is
| > > Unable to set the PaperSize property of the PageSetup
| > >
| > > My code is as below
| > >
| > > Imports System.IO
| > > Imports Office = Microsoft.Office.Core
| > > Imports Microsoft.Office.Interop.Excel
| > > Imports System.Data.SqlClient
| > > Imports System.Configuration
| > > Imports Microsoft.Office.Interop.Excel.Constants
| > > Imports System.Xml
| > >
| > >
| > > Dim objExcelApp As New Microsoft.Office.Interop.Excel.Application
| > > Dim objWB As Microsoft.Office.Interop.Excel.Workbook
| > > Dim objSH As Microsoft.Office.Interop.Excel.Worksheet
| > >
| > > 'Open the file that was created Using the Excel Object model.
| > > objExcelApp.Workbooks.Open("C:\temp\NewspaperReport.xls")
| > >
| > > 'Assign the Activeworksheet to a Worksheet object
| > > objSH = objExcelApp.ActiveWorkbook.ActiveSheet
| > >
| > > 'Set properties for the Active Worksheet
| > > With objSH
| > > .Columns.ColumnWidth = 2.39
| > > 'Q1 Select
| > > .Range("E1", "BD1").Interior.ColorIndex = 3
| > > .Range("A1", "BD1").Rows(1).rowheight = 29.25
| > > .Rows(1).Font.Name = "Arial"
| > > .Rows(1).Font.ColorIndex = 2
| > > .Rows(1).Font.Bold = True
| > > .Rows(1).Font.Size = 12
| > > .Rows(1).VerticalAlignment =
| > > CType(Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter,
| > > Microsoft.Office.Interop.Excel.XlVAlign)
| > >
| > > .Name = "Local Newspaper"
| > > .Columns.WrapText = False
| > >
| > > .PageSetup.PaperSize =
| > > CType(Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLegal,
| > > Microsoft.Office.Interop.Excel.XlPaperSize)
| > > .PageSetup.Orientation =
| > > CType(Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape,
| > > Microsoft.Office.Interop.Excel.XlPageOrientation)
| > >
| > > .PageSetup.Zoom = 55
| > >
| > > 'Set Printing Margins
| > > .PageSetup.HeaderMargin = objExcelApp.InchesToPoints(0.25)
| > > .PageSetup.TopMargin = objExcelApp.InchesToPoints(0.5)
| > > .PageSetup.LeftMargin = objExcelApp.InchesToPoints(0.25)
| > > .PageSetup.RightMargin = objExcelApp.InchesToPoints(0.25)
| > > .PageSetup.BottomMargin = objExcelApp.InchesToPoints(0.5)
| > > .PageSetup.FooterMargin = objExcelApp.InchesToPoints(0.25)
| > > .PageSetup.CenterHeader = "Local Newspaper"
| > >
| > > .PageSetup.RightFooter = "Report generated on " & "&D page &P of &N"
| > > .PageSetup.PrintTitleRows() = .Rows(1).Address
| > > .PageSetup.PrintGridlines = True
| > > .Range("E5", "E5").Select()
| > > End With
| >
| >
| >