need page break by asking the input file for page break to be done

Originally Posted by royUK

Why are you creating a new instance of Excel?

Option Explicit

Dim V_fileloc  As String
Dim oExcel     As Excel.Application
Dim oWB        As New Workbook
Sub addPageBreak()
    Dim rng    As Range
    Dim cl     As Range

    V_fileloc = ""

    V_fileloc = Application.GetOpenFilename("Page Break File (*.xls),*.xls", , "Page Break File")

    If (V_fileloc = "" Or V_fileloc = "False") Then
        MsgBox "Page Break File, Should be selected", vbApplicationModal, "Files"
        Exit Sub
    End If

    '    Set oExcel = CreateObject("excel.Application")
    Set oWB = Workbooks.Open(V_fileloc)
    With oWB.Worksheets("Sheet1")

        Set rng = .Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
        On Error Resume Next
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        For Each cl In rng
            If cl.Value <> cl.Offset(1, 0).Value Then
                oExcel.HPageBreaks.Add Before:=cl.Offset(1, 0)
            End If
        Next cl
    End With
'    oWB.Close
    Set oWB = Nothing
    On Error GoTo 0
End Sub

I thought that way i can solve my problem. if you have any other choice please let me know.