Binaryworld - How to set print layout of Excel file programatically using VB code ... [ VB -> Excel ]

This code will show you
- How to make column width to auto fit
- How to set column width
- How to set papersize
- How to set page orientation

Click here to copy the following block

Sub SetXLSPrintLayout(XLSFilePath As String)
  On Error GoTo errHandler
  'Dim objExcel As New Excel.Application
  Dim ps As Excel.PageSetup
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  objExcel.Workbooks.Open XLSFilePath
  objExcel.DisplayAlerts = False

  objExcel.ActiveSheet.PageSetup.Orientation = xlLandscape  'Set Orientation to landscape
  objExcel.ActiveSheet.PageSetup.PaperSize = xlPaperLegal  'Set Papersize to legal
  objExcel.ActiveSheet.PageSetup.LeftMargin = objExcel.Application.InchesToPoints(0.5)
  objExcel.ActiveSheet.PageSetup.RightMargin = objExcel.Application.InchesToPoints(0.5)
  objExcel.ActiveSheet.PageSetup.TopMargin = objExcel.Application.InchesToPoints(0.5)
  objExcel.ActiveSheet.PageSetup.BottomMargin = objExcel.Application.InchesToPoints(0.5)
  objExcel.Columns("A:Z").EntireColumn.AutoFit  'To adjust the column's width.

  'objExcel.Columns(1).Width = 1 'To adjust the column's width.

  objExcel.ActiveWorkbook.Save
  objExcel.Workbooks.Close
  objExcel.DisplayAlerts = True
  objExcel.Quit
  Exit Sub
errHandler:
  Err.Raise Err.Number, Err.Source, Err.Description
End Sub