Force Page Breaks In Vba?

SpirituaLee · Mar 19th 2007

Hi, want to specify page breaks and set print areas when running a macro. Appears to work sometimes, but then breaks (hard and soft) pop up on their own).

Currently using something like this to specify a 2 page wide view...

Code

ActiveSheet.PageSetup.PrintArea = "$A:$Z"
    With ActiveSheet.PageSetup
        .FitToPagesWide = 2
        Set ActiveSheet.VPageBreaks(1).Location = Range("P1")
    End With

Can I force breaks to be more absolute (more accurately)?

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

For some reason in excel the FitToPageWide and tall doesn't work when put into a macro so there is not point in setting FitToPageWide = 2

try doing something along the lines of this

Code

With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .Orientation = xlLandscape
       
    End With
    
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.View = xlNormalView

SpirituaLee · Mar 19th 2007

Re: Force Page Breaks In Vba?

Excellent, thanks for the pointer.

Basically, I have 2 custom views (columns hidden in one, and not in the other).

I want to be able to specify that the view with colums hidden (activated by clicking a command button) be set to one page wide only, and that the view containing all columns, be set to 2 pages wide (but no more).

I want to be able to set the print areas to match also.

Your suggestions are most welcome!

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

Ok try this

Code

Sub SetPageBreaks()
        
                ' Change Columns to which ones are actually hidden
    If Columns("E:F").EntireColumn.Hidden = True Then
    
        ActiveWindow.View = xlPageBreakPreview
            Set ActiveSheet.VPageBreaks(1).Location = Range("I1")
        ActiveWindow.View = xlNormalView
        
    Else
        ActiveWindow.View = xlPageBreakPreview
            Set ActiveSheet.VPageBreaks(1).Location = Range("I1")
            Set ActiveSheet.VPageBreaks(2).Location = Range("P1")
        ActiveWindow.View = xlNormalView
    End If
    
End Sub

Display More

should help you get there

SpirituaLee · Mar 19th 2007

Re: Force Page Breaks In Vba?

Hmmm, Excel still seems to be inserting Vertical Page Breaks in wherever it sees fit, in addition to those I've specified.

Is there a foolproof (i.e. me!) way of specifying where a vertical page break should be, without it adding in its own too?

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

have you got a sample sheet?

SpirituaLee · Mar 19th 2007

Re: Force Page Breaks In Vba?

Unfortunately, no.

The data's being pulled in from Access, and there's a whole lot of info I'd need to censor. Would take an age!

Craig Ottley · Mar 19th 2007

Re: Force Page Breaks In Vba?

How are you 'Pulling' your data from Access. If you query the database via a SQL Statement you could set two seperate queries one to pull through all your desired columns and the other to import required fields only... it would look something like this

Code

Option Explicit


Public cnnACC As ADODB.Connection
Public Rs As ADODB.Recordset
Public LnCountfield As Long, LnCount As Long
Public strDB As String
Public strConn As String


Public Function AccessToExcel()
   
    Dim Ws As Worksheet
    Dim WsA As Worksheet
    Dim lrange As Range
    Dim stSQL1 As String
    
    Application.ScreenUpdating = False
    
    Set cnnACC = New ADODB.Connection
    Set Rs = New ADODB.Recordset
    
    Set Ws = Sheets("Main")


    
    strDB = "\\ntch05\database.mdb"
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & strDB & ";"
  
    cnnACC.Open strConn
          
    stSQL1 = "SELECT tbl_raw.[Product],[Bkref],[User],[Gross Profit %age], [Net Nargin %age incl VAT ],[Gross price]" _
            & " FROM tbl_raw" _
            & " WHERE [Gross Profit %age] <> [Net Nargin %age incl VAT ] ;"


With Rs
     .Open stSQL1, cnnACC, adOpenStatic, adLockOptimistic
    '
End With


        
LnCountfield = Rs.Fields.Count


Sheets.Add.Name = "Agent"


Set WsA = Sheets("Agent")


For LnCount = 0 To LnCountfield - 1


Cells(1, LnCount + 1).Value = Rs.Fields(LnCount).Name


Next LnCount


WsA.Cells(2, 1).CopyFromRecordset Rs


    
    Rs.Close
    Set Rs = Nothing
    cnnACC.Close
    Set cnnACC = Nothing
    
    Set Ws = Nothing
    Set WsA = Nothing


Application.ScreenUpdating = True


End Function

Display More

that could be a better way of doing it?

Force Page Breaks In Vba?

Participate now!

Share