I have about a hundred Excel files and I need to remove PageBreaks in all of them.
I found a few VBA suggestions and my current setup is this:
- Page_Breaks_Remover.xlsm
- Files (folder with Excel files)
In the Page Breaks Remover.xlsm I created a Module and have the following script
This code doesn't work however, although it iterates over files correctly.
The reason I try to switch View to Normal is because when I go and manually try to disable page breaks this option is disabled in the File > Options > Advanced menu in every file until I switch to Normal view.
Any help is appreciated.
I found a few VBA suggestions and my current setup is this:
- Page_Breaks_Remover.xlsm
- Files (folder with Excel files)
In the Page Breaks Remover.xlsm I created a Module and have the following script
VBA Code:
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ActiveWorkbook.Path & "\Files\"
Filename = Dir(Pathname & "*.xls")
Do While Filename <> ""
MsgBox (Filename)
Set wb = Workbooks.Open(Pathname & Filename)
DoWork wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
End Sub
Sub DoWork(wb As Workbook)
With wb
For Each ws In wb.Worksheets
ws.Activate
ActiveWindow.View = xlNormalView
ws.DisplayPageBreaks = False
Next ws
End With
End Sub
This code doesn't work however, although it iterates over files correctly.
The reason I try to switch View to Normal is because when I go and manually try to disable page breaks this option is disabled in the File > Options > Advanced menu in every file until I switch to Normal view.
Any help is appreciated.