Reference of Excel functions and methods needed, such as changing the layout to Landscape

HCL Domino Domino Forum
1

hi,I am using an agent I got online that exports a view to an Excel spreadsheet. I have modified the Lotusscript to make the spreadsheet more readable and to print within one page. Here are some example methods that the code uses:

xlSheet.Range( xlSheet.Cells(2,1), xlsheet.Cells(RowCount , MaxExportColumnCount) ).Columns.AutoFit

xlSheet.Range(xlSheet.Cells(1,1), xlsheet.Cells(1 , MaxExportColumnCount) ).Borders.LineStyle = 7

'With xlSheet.Range( "A1:"+Columns(MaxExportColumnCount)+"1"  ).Font  ' Font size, colour, style.

'Backwards compatible to Excel 2000

With xlSheet.Range( xlSheet.Cells(1,1), xlsheet.Cells(2 , MaxExportColumnCount) ).Font  ' Font size, colour, style.

	.Size = 6

	.Bold = True

	.ColorIndex = 0

.

If Column >= SubjectColumnNumber Then

	xlSheet.Cells( Row , Column ).Columns.Columnwidth = 36		

	xlSheet.Cells( Row , Column ).Columns.WrapText = True

I would like to know the method to change the layout from Portrait to Landscape in Excel. Can anyone provide a link to a an Excel reference that has all of the available methods for use in Lotusscript? Is it based on VB?

thanks,

Won

2

Subject: Example Code

Hi Won. Here’s some example code I have that configures an area to be printed. The orientation (Portrait / Landscape) is part of the PageSetup object that belongs to the Sheet.

With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = ""

.RightHeader = ""

.LeftMargin = Application.InchesToPoints(0.5)

.RightMargin = Application.InchesToPoints(0.5)

.TopMargin = Application.InchesToPoints(0.5)

.BottomMargin = Application.InchesToPoints(0.5)

.Orientation = xlPortrait

.PrintHeadings = False

.Zoom = False

.FitToPagesWide = 1

.FitToPagesTall = 200

End With

ActiveSheet.PageSetup.PrintArea = PrintAreaName

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

The Object Browser in Excel can be used for finding the values of the constants … xlProtrait has a value of 1 , so you could also use

.Orientation = 1
3

Subject: Thanks.

thanks, Graham. I got the landscape orientation to work by using xlSheet.PageSetup.Orientation = 2. I looked up your reference to the Excel Object Browser and got this info from http://www.visualbasicscript.com/m_32770/tm.htm :

With a standard install of office, go to C:\program files\Microsoft Office\OFFICE11\1033 and open VBAXL10.chm. That is the best source that I have found for the Excel object model.

This is just for all of us who are novices at this.

4

Subject: That’s a neat tip to a usful file … even for us not-so-novices … thanks :slight_smile: