Scripting: Setting print area and default Page Break Preview in Excel

Question

**Updating subject... (Former: Powershell - Insert page breaks in Excel)

Here are my goals:

1. Insert vertical and horizontal page breaks in an Excel sheet depending on the range of data extracted.
2. Set 'Page Break Preview' as default page layout of the Excel sheet.

Here is what I have so far:

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$wbpath = 'c:\path\to\excel.xls'
$wb = $Excel.Workbooks.Open($wbpath)
$ws = $wb.Sheets.Item(1)
$rows=$ws.UsedRange.Rows.Count
$rowcnt = $Excel.WorksheetFunction.CountIf($ws.Range("A1:A"+$rows), "<>")
$RowRange = $ws.Range("A1:A"+$rowcnt)
$RowRange = $ws.Range("A1:A"+$rowcnt)
$ColumnRange = #Set column range

##Set $Rowrange as VRagebreak
##Set $ColumnRange as HPageBreak
##Set Page Break view as default Page Layout view

$Workbook.Save()
$Workbook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)|Out-Null
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null

Thanks in advance!

Answer 1

@Bacon Bits Thanks for sharing the page (web archive link). That definitely provides the answer for my first problem.

But after hours of searching, it came to me that instead of Powershell, a more preferrable language to solve my problems would be VBScript . Hope this would not raise concerns with me deviating on the topic but I would just like to share on how I attained my goals using VBscript. Just trying to help those who might bump to the same problem.

Here it goes. The VBScript below would:

1. Fetch all excel files from the directory passed by a calling script (e.g. batch,powershell etc)
2. Sets proper print area (page breaks) to the current excel sheet in the pipe.
3. Set current worksheet view to Page Break Preview

WScript.echo "**Formatting extracts... -- " & Now


'Fetch current directory


Set args = WScript.Arguments

    CurDir = args.Item(0)
    wscript.echo "Current Directory: " & CurDir

'Page break constants
    const xlPageBreakManual = -4135
    const xlPageBreakNone = -4142 
    const xlPageBreakPreview  = &H2 
    const xlNormalView = &H1 

'Initiate Excel variables    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(CurDir)
    Set colFiles = objFolder.Files

For Each objFile in colFiles
    If UCase(objFSO.GetExtensionName(objFile.name)) = "XLS" Then
        AbsolutePathName = objFSO.GetAbsolutePathName(objFile)
        ExtractName = objFSO.GetFileName(objFile)
        'Wscript.Echo "File path and Name: " & AbsolutePathName
        'Wscript.Echo "Extract Name: " & extractName
            
        'Open Excel
        Set xlApp = CreateObject("Excel.Application")
        set xlBook = xlApp.WorkBooks.Open(AbsolutePathName)
        set xlSht = xlApp.Worksheets.Item(1)

        'Set print area
        rows = xlSht.UsedRange.Rows.count
        rowcnt = xlApp.WorksheetFunction.CountIf(xlSht.Range("A1:A"&rows), "<>")
        if InStr(extractName,"DCIS") = 1 then
            wscript.echo extractName & ": Setting print area..."
            xlSht.PageSetup.PrintArea = "$A$1:$N$" & rowcnt
        elseif InStr(extractName,"WCIS") = 1 then
            wscript.echo extractName & ": Setting print area..."
            xlSht.PageSetup.PrintArea = "$A$1:$O$" & rowcnt
        end if

        'Set current worksheet view to Page Break Preview
        Wscript.Echo extractName & ": Setting default view to Page break view..."     
        xlApp.ActiveWindow.View = xlPageBreakPreview
        
        'Save formatted excel
        xlBook.Save
        xlBook.Close False
        xlApp.Quit

        'Deallocate after use
        Set xlSht = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing 
    End If
Next

WScript.echo "**Finished formatting extracts. -- " & Now