I am supposed to export some large data ranges from Excel to Powerpoint, one page per slide, and of course I should treat the page breaks to avoid "orphan" rows or columns.
I am trying to check how many pages I would have, vertically and horizontally, with a given zoom, by reading HPageBreaks.Count and VPageBreaks.Count, and then manually define the position of each break. The idea is to have approximately the same width and height on each page.
When I debug my code step-by-step, it runs nicely, and the logic seems ok, but if I run it "freely", the page breaks are completely off. Adding some MsgBox instructions, I can see that when I read HPageBreaks.Count (or vertical) I get the wrong values. (I can check the correct ones if I do manually what the code should do).
Searching on many many forums, I see some ugly workarounds like forcing a reset of PaperSize (ws.PageSetup.PaperSize = ws.PageSetup.PaperSize). After trying some of them, what seemed to work a bit better was to turn off PrintCommunication before a change to PageSetup, and then turn it back on. This worked well on most of my sheets, but on the really large ones (~750 rows x 80 columns, almost all cells with formulas), it simply doesn't.
Here an extract of the code:
'Reset page breaks
.ResetAllPageBreaks
'Set minimum acceptable zoom factor
Application.PrintCommunication = False 'This is the ugly workaround
.PageSetup.Zoom = 60
Application.PrintCommunication = True
MsgBox "Zoom = " & .PageSetup.Zoom 'Only for debugging
'Calculate the number of pages in width
Application.PrintCommunication = False
NPagesWide = .VPageBreaks.Count + 1
Application.PrintCommunication = True
MsgBox "NPagesWide = " & NPagesWide
'Find the higher zoom factor that can fit that number of pages
Application.PrintCommunication = False
.PageSetup.Zoom = 100
Application.PrintCommunication = True
Do While .VPageBreaks.Count > NPagesWide - 1
Application.PrintCommunication = False
.PageSetup.Zoom = .PageSetup.Zoom - 5
Application.PrintCommunication = True
Loop
MsgBox "Zoom = " & .PageSetup.Zoom
'Set average width per page and initialize column pointer
If HasTitleColumns Then 'Defined earlier
PageWidth = (PrintArea.Width + TitleColumns.Width * (NPagesWide - 1)) / NPagesWide
j = TitleColumns.Columns(TitleColumns.Columns.Count).Column + 1
Else
PageWidth = PrintArea.Width / NPagesWide
j = 1
End If
'Cycle vertical page breaks
For i = 1 To NPagesWide - 1
'Set width of TitleColumns
If HasTitleColumns Then
CumulWidth = TitleColumns.Width
Else
CumulWidth = 0
End If
'Cumulate columns width until the available page width
Do While CumulWidth + .Columns(j).Width <= PageWidth
CumulWidth = CumulWidth + .Columns(j).Width
j = j + 1
Loop
'Add the break
.VPageBreaks.Add .Columns(j + 1)
Next i
Any ideas why this happens, and how can I solve it?
Thanks,
I propose general advice to the issue when VBA code works fine while hitting F8 in the debug mode, but it doesn't not work after hitting F5 to run the whole macro.
Hint 1. Use ThisWorkbook.ActiveSheet
instead of ActiveWorkbook.ActiveSheet
whenever possible to reference the proper sheet. Use ThisWorkbook.Application
instead of just Application.
It could be that you have another Addin program working on in background, switching ActiveSheet
to something else that you may not be aware of. Check other macros enabled and get rid of anything that you do not use. So before anything important in your code, try to get the focus for your sheet with ThisWorkbook.Activate
. See the graph on that page: http://analystcave.com/vba-tip-day-activeworkbook-vs-thisworkbook/
Hint 2. Force Excel to wait in different way then DoEvents
.
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'Place this line of code at the very top of module
Sleep 1 'This will pause execution of your program for 1 ms
https://stackoverflow.com/a/3891017/1903793 https://www.fmsinc.com/microsoftaccess/modules/examples/AvoidDoEvents.asp
Or alternatively:
Application.Calculate
If Not Application.CalculationState = xlDone Then
DoEvents
End If
https://stackoverflow.com/a/11277152/1903793
Hint 3. If the above still does not work for refreshing use:
ThisWorkbook.Connections("ConectionName").Refresh
ThisWorkbook.Application.CalculateUntilAsyncQueriesDone
https://stackoverflow.com/a/26780134/1903793
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With