Excel 2003 object model

[Guest]

Hello. I'm looking to alter some page setup properties (margins, orientation,
top row to repeat at top). It seems there are no such properties (workbook
and worksheet properties) to modify. Or am I incorrect. Any help is
appreciated. This pertains to an app where i open an existing excel file,
modify, save then leave.

Thanks,
Jim

 

[Doug Glancy]

Jim,

The Macro Recorder is good for figuring out things like this. In XL2003 I
turned on the recorded, changed the margins and got this wealth of info:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/8/2004 by Doug Glancy
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

hth,

Doug Glancy

 

[Guest]

Thanks for the response Doug. This is code inside excel right? meaning VBA
code. I'm actually not doing these modifications in excel/VBA. In an ASP
..NET, I am using FSO and the Excel 2003 reference to open an excel file and
then modify.

FYI, when I start coding:
Workbook.ActiveSheet, the intellisense is not picking up .PageSetup.
Therefore, Workbook.ActiveSheet.PageSetup does not exist.

 

[Tom Ogilvy]

did you create a reference to the Excel object library? You shouldn't get
intellisense for excel object unless you do. Then you would have to
explicitly type you variables

Dim wkbk as Excel.Workbook

as an example (or the NET equivalent).

then

wkbk.
should bring up intellisense.

 

[Guest]

Hi Tom. Thanks for the response. Yes, I instantiated the excel object.
Therefore, intellisense is picking up all excel method, properties, etc. So
according to intellisense (using your example code below),
wkbk.ActiveSheet.PageSetup is invalid. There are only five methods for
ActiveSheet (Equals, GetHashCode, GetType, ToString). It shows no properties.

 

[Doug Glancy]

Jim,

I'm rank beginner at VB.Net, but this works for me. (It's a console app,
but I think it would apply). PageSetup and PrintTitleRows both showed up
in IntelliSense.

Sub create_excel_workbook()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlSheet.PageSetup.PrintTitleRows = "$3:$3"
' Show the sheet
xlSheet.Application.Visible = True
End Sub

Otherwise, sounds like a post to microsoft.public.dotnet.languages.vb is in
order.

hth,

Doug

 

[Guest]

Doug/Tom, I found the PageSetup properties! I was under the Sheet object.
Thanks a bunch.

- Jim