PageOrientation and Page Breaks

[hockeyboi16]

How can i set the pageOrientation of my sheet to be landscape, instead of portraid, and how do i also move the page break further to the right so that my entire sheet can be printed width-wise on a landscape configuration?

to start my excel object i did:

dim excelSheet as object
set excelsheet = createobject("Excel.Sheet")

and through all my formatting, i've been referring to my sheet as:

excelsheet.application.cells(x,y).[property]

Any help is much appreciated! Oh, and based on the experience of my last posts, i did look at the macro recorder's code for setting page breaks and orientation, and it didn't work for me. =( perhaps i was doing something wrong, but i think it should have something to do with [sheet].pagesetup.orientation = [constant]

Thanks in advance!

--whit

 

[AnotherHiggins]

Turn on your macro recorder.

Tools > Macros > Record New Macro. Go through the steps you want to do. Stop recording and observe the code that was generated. ([Alt]+[F11] opens the VBEditor.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hockeyboi16]

I have already done what you said, but the code won't work for me when i use it in my VBA code.

 

[AnotherHiggins]

can you provide a snippet of what you're trying to use? Make sure to include the specific line that generates the error.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hockeyboi16]

well, i have tried many things. First, i used the macro recorder, and tried to past in the code there into my VBA code, and that didn't work:

With ActiveSheet.PageSetup
.Orientation = xlLandscape
end with
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1


Then i just tried setting the sheet's orientation:

excelsheet.pagesetup.orientation = 2 ' Orientation constant (landscape layout)

and that didn't work either.

 

[AnotherHiggins]

This:

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
End With

should work. It can be shortened to:

ActiveSheet.PageSetup.Orientation = xlLandscape

As for the PageBreaks, the recorder probably captured something like this:

Sub test()
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$10"
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$6"
ActiveWindow.View = xlNormalView
End Sub

If you are just using code, you can get rid of the view changes and just set the range you want. Something like this:

Sub test()
ActiveSheet.PageSetup.PrintArea = "$B$2:$E$15"
End Sub

So, put it all together and you have:

sub test
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.PrintArea = "$B$2:$E$15"
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hockeyboi16]

I thank you for your help. I am getting an "Object Required" error on the first line of the code you gave me (the put it all together bit) in your last post. This is what i have in my code:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.PrintArea = "$B$2:$E$15"

 

[fumei]

And indeed an object is required. If you are using the object ExcelSheet, should that not be:

ExcelSheet.PageSetup.Orientation = xlLandscape

Gerry
My paintings and sculpture

 

[hockeyboi16]

Yes, i tried that, and it says:

"Object doesn't support this method or property"

as an error message. Does this have something to do with me binding my excelsheet reference as an object and not an excel sheet?

 

[hockeyboi16]

Ah, okay, things are becoming clearer to me now.

Turns out i didn't have the Excel Object Library referenced, so i guess that would explain half of the errors i was getting!

I want to thank you guys for enduring these problems and helping me out!

I appreciate it.

--whit

 

[firefytr]

If you have Excel referenced via VBE | Tools | References, you can use some of these to set main excel objects...

    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet
    Set ExcelApp = CreateOjbect("Excel.Application")
    Set ExcelBook = ExcelApp.Workbooks.Add(xlWBATWorksheet)
    Set ExcelSheet = ExcelBook.Sheets(1)

This will create a new Excel instance and add a new workbook with only one sheet, setting that first sheet as an object variable so you can manipulate as needed. Then the sheet info is ...

    ExcelSheet.PageSetup.Orientation = xlLandscape

Note that if you need to manipulate the ActiveWindow for any reason you should make the ExcelApp Visible property to True.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[hockeyboi16]

I have everything working now, except for the Page Breaks.

I set the printArea range to be the entire sheet, but that doesn't seem to work. It always defaults to the default page break.

Any ideas on this?

--whit

 

[firefytr]

Post your code in it's entirety.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[hockeyboi16]

' Creates the Excel App and workbook, and adds the sheet
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.workbook
Dim xlWorksheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Add
Set xlWorksheet = xlWorkbook.Sheets.Add

' The row index which each portion of the report begins on
Dim intReportTitleRowIndex As Integer
Dim intReportStartIndex As Integer
Dim intReportHeaderIndex As Integer

intReportTitleRowIndex = 3
intReportHeaderIndex = 5
intReportStartIndex = 7

' Sets the Page Orientation and where the page break for printing will be
xlWorksheet.PageSetup.Orientation = xlLandscape

Dim qryDef As QueryDef
Dim db As Database

Set db = CurrentDb

' Query to pull back the data (temporary - deleted after the records are pulled back)
Dim qryName As String
qryName = "qryPIFLogHighLevelExport"

' Creates the query
db.CreateQueryDef (qryName)
Set qryDef = db.QueryDefs(qryName)

qryDef.SQL = "SELECT [Performance Improvement].[PIF Closure Date], [Performance Improvement].[Submission Date]," & _
"[Performance Improvement].Initiator, [Performance Improvement].[Area Owner]," & _
"[Performance Improvement].[Results Summary], [Performance Improvement].Comment," & _
"[Performance Improvement].Number, [Performance Improvement].[Problem/Suggestion Summary]," & _
"[Performance Improvement].[PIF Type], [Performance Improvement].[PIF Owner]," & _
"[Performance Improvement].[PIF Closure Date], [Performance Improvement].[Web PIF Number]" & _
"From [Performance Improvement] WHERE " & getWhereClause & " ORDER BY [Performance Improvement].[Evaluation Due Date]"

' Pulls back the data using the query
Dim rs As Recordset
Set rs = db.OpenRecordset(qryName)

' Format and Label the Report date range
xlWorksheet.Range(xlWorksheet.Cells(1, 1), xlWorksheet.Cells(1, 2)).merge
xlWorksheet.Range(xlWorksheet.Cells(1, 3), xlWorksheet.Cells(1, 5)).merge
xlWorksheet.Cells(1, 3).horizontalalignment = -4131
xlWorksheet.Cells(1, 1).Value = "Report Period:"
xlWorksheet.Cells(1, 3).Value = " " & formatDateRange

' Format and label the Date of the Report
xlWorksheet.Range(xlWorksheet.Cells(2, 1), xlWorksheet.Cells(2, 2)).merge
xlWorksheet.Range(xlWorksheet.Cells(2, 3), xlWorksheet.Cells(2, 5)).merge
xlWorksheet.Cells(2, 3).horizontalalignment = -4131
xlWorksheet.Cells(2, 1).Value = "Report Date:"
xlWorksheet.Cells(2, 3).Value = CStr(" " & MonthName(Month(CDate(Now()))) & " " & Day(CDate(Now())) & ", " & Year(CDate(Now())))

' Format Title of Report
xlWorksheet.Range(xlWorksheet.Cells(intReportTitleRowIndex, 1), xlWorksheet.Cells(intReportTitleRowIndex, 8)).merge
xlWorksheet.rows(intReportTitleRowIndex).RowHeight = 20
xlWorksheet.Cells(intReportTitleRowIndex, 1).Value = "Performance Improvement Form (PIF) Log: High Level Summary of Submitted PIF's and Results"
xlWorksheet.Cells(intReportTitleRowIndex, 1).Font.bold = True
xlWorksheet.Cells(intReportTitleRowIndex, 1).Font.Size = 14
xlWorksheet.Cells(intReportTitleRowIndex, 1).verticalalignment = -4108
xlWorksheet.Cells(intReportTitleRowIndex, 1).horizontalalignment = -4108

' Column Header font-weight: Bold, size: 12
xlWorksheet.Range(xlWorksheet.Cells(intReportHeaderIndex, 1), xlWorksheet.Cells(intReportHeaderIndex, 8)).Font.bold = True
xlWorksheet.Range(xlWorksheet.Cells(intReportHeaderIndex, 1), xlWorksheet.Cells(intReportHeaderIndex, 8)).Font.Size = 12
' Column Header Vertical-Alignment: center and border style(top and bottom only): double line
xlWorksheet.Range(xlWorksheet.Cells(intReportHeaderIndex, 1), xlWorksheet.Cells(intReportHeaderIndex, 8)).verticalalignment = -4108
xlWorksheet.Range(xlWorksheet.Cells(intReportHeaderIndex, 1), xlWorksheet.Cells(intReportHeaderIndex, 8)).borders(8).linestyle = -4119
xlWorksheet.Range(xlWorksheet.Cells(intReportHeaderIndex, 1), xlWorksheet.Cells(intReportHeaderIndex, 8)).borders(9).linestyle = -4119

' Delete the query as it is unneeded now
db.QueryDefs.Delete (qryName)

' Sets up the column headers (size and labels)
xlWorksheet.Cells(intReportHeaderIndex, 1).Value = "PIF #"
xlWorksheet.Columns("A:A").ColumnWidth = 7
xlWorksheet.Cells(intReportHeaderIndex, 2).Value = "Date Submitted"
xlWorksheet.Columns("B:B").ColumnWidth = 12
xlWorksheet.Cells(intReportHeaderIndex, 3).Value = "Submitter"
xlWorksheet.Columns("C:C").ColumnWidth = 16
xlWorksheet.Cells(intReportHeaderIndex, 4).Value = "Type"
xlWorksheet.Columns("D").ColumnWidth = 7
xlWorksheet.Cells(intReportHeaderIndex, 5).Value = "Issue Summary"
xlWorksheet.Columns("E:E").ColumnWidth = 35
xlWorksheet.Cells(intReportHeaderIndex, 6).Value = "Owner"
xlWorksheet.Columns("F:F").ColumnWidth = 25
xlWorksheet.Cells(intReportHeaderIndex, 7).Value = "Status/Results,Summary/Comments"
xlWorksheet.Columns("G:G").ColumnWidth = 40
xlWorksheet.Cells(intReportHeaderIndex, 8).Value = "Date Closed"
xlWorksheet.Columns("H:H").ColumnWidth = 12

xlWorksheet.Columns("A:H").wraptext = True

' Loop through the PIF records and add each to the excel sheet
Dim intActiveRow As Integer
intActiveRow = intReportStartIndex
While Not rs.EOF

' Sets solid line bottom border for the current row (divider between PIFs)
xlWorksheet.Range(xlWorksheet.Cells(intReportStartIndex, 1), xlWorksheet.Cells(intActiveRow, 8)).borders(9).weight = 2

' Sets cells to have center vertical alignment and left horizontal alignment
xlWorksheet.Range(xlWorksheet.Cells(intReportStartIndex, 1), xlWorksheet.Cells(intActiveRow, 8)).verticalalignment = -4160
xlWorksheet.Range(xlWorksheet.Cells(intReportStartIndex, 1), xlWorksheet.Cells(intActiveRow, 8)).horizontalalignment = -4131

' Sets font for cells
xlWorksheet.Range(xlWorksheet.Cells(intReportStartIndex, 1), xlWorksheet.Cells(intActiveRow, 8)).Font.Size = 10
xlWorksheet.Range(xlWorksheet.Cells(intReportStartIndex, 1), xlWorksheet.Cells(intActiveRow, 8)).Font.Name = "arial"

xlWorksheet.Cells(intActiveRow, 1).Value = rs.Fields("Number").Value

' Retrieves the date without the time
Dim intSpaceIndex As Integer
Dim strDateString As String
strDateString = CStr(rs.Fields("Submission Date").Value)
intSpaceIndex = InStr(strDateString, " ")
xlWorksheet.Cells(intActiveRow, 2).Value = Left(strDateString, intSpaceIndex)

xlWorksheet.Cells(intActiveRow, 3).Value = rs.Fields("Initiator").Value
xlWorksheet.Cells(intActiveRow, 4).Value = getAbbrevPIFType(rs.Fields("PIF Type").Value)
xlWorksheet.Cells(intActiveRow, 5).Value = rs.Fields("Problem/Suggestion Summary").Value & vbLf
xlWorksheet.Cells(intActiveRow, 6).Value = rs.Fields("PIF Owner").Value
xlWorksheet.Cells(intActiveRow, 7).Value = rs.Fields("Results Summary").Value
xlWorksheet.Cells(intActiveRow, 8).Value = rs.Fields("PIF Closure Date").Value

rs.MoveNext
intActiveRow = intActiveRow + 1
Wend

'ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
'MsgBox xlWorksheet.VPageBreaks.count
xlWorksheet.VPageBreaks(1).Location = xlWorksheet.Columns("i")

xlApp.Visible = True

Set xlWorkbook = Nothing
Set xlWorksheet = Nothing
Set xlApp = Nothing

 

[PHV]

' Sets the Page Orientation and where the page break for printing will be
With xlWorksheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
End with


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886