CODEWITHSUNDEEP
excelvbscript
ataylor
ataylor

Reputation: 35

Using VBScript to Change Excel View to Page Layout

I need to use VBScript to change all of the sheets in an excel workbook to Page Layout View instead of the default view. However, I cannot figure out how to do that in VBS. With VBA, the code I've been using (with a while loop to go over each sheet) is 

With ActiveWindow
    .View = xlPageLayoutView
End With

which serves my purposes fine. But I need to do this in VBS. I think it has something to do with the Application object, though I'm not sure. Any help would be appreciated.

Edit: here's a sample of the code I've written with declarations and things. It's basically iterating over a number of sheets in a workbook and setting them all (or trying to) to Page Layout view. Missing from this segment is the sub where I populate the workbook with new sheets matching the entries from Names().

Dim destFile, objWorkbook

Set destFile = CreateObject("Excel.Application")

Set objWorkbook = destFile.Workbooks.Add()
objWorkBook.SaveAs(strPath)

Sub OverNames()
    For i = 1 to 9
        SetPagelayout(i)
    Next
End Sub

Sub SetPageLayout(hNum)
    Dim houseSheet, sheetName

    'retrieves sheet name from array Names()
    sheetName = Names(hNum, 0)

    Set houseSheet = destFile.Worksheets(sheetName)

    houseSheet.Window.View = xlPageLayoutView
End Sub

Upvotes: 3

Views: 5171

Answers (1)

Damienknight
Damienknight

Reputation: 1896

VBA already has excel and the workbook loaded. With VBS, you need to create an excel object and open your workbook with it. Also, VBA has static variables defined for excel settings, which you will have to define yourself in VBS.

Dim objExcel
Dim excelPath
Dim xlPageLayoutView=3  ' https://msdn.microsoft.com/en-us/library/office/ff838200.aspx

excelPath = "C:\scripts\servers.xlsx"
objExcel.DisplayAlerts = 0 

Set objExcel = CreateObject("Excel.Application")

In order to change the state of a window, you have to access the window object. In excel there are Workbooks, which contain collections of Worksheets and Windows. The application also contains a collection of all windows in all worksheets. In the workbook window collection, the active window is always accessed through index 1.

Set currentWorkBook = objExcel.ActiveWorkbook
Set currentWorkSheet = currentWorkBook.Worksheets("Sheet Name Here")

currentWorkSheet.Activate

Set currentWindow = currentWorkBook.Windows(1)

currentWindow.View = xlPageLayoutView

Upvotes: 1

Related Questions