如何“强制”Excel正确计算分页符？

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.

我应该将一些大数据范围从Excel导出到Powerpoint，每张幻灯片一页，当然我应该处理分页符以避免“孤立”行或列。

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.

我试图通过读取HPageBreaks.Count和VPageBreaks.Count来检查具有给定缩放的垂直和水平页面数，然后手动定义每个中断的位置。我们的想法是在每个页面上具有大致相同的宽度和高度。

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).

当我逐步调试我的代码时，它运行良好，逻辑似乎没问题，但如果我“*”运行，则分页符完全关闭。添加一些MsgBox指令，我可以看到当我读取HPageBreaks.Count（或垂直）时，我得到了错误的值。（如果我手动执行代码应该做什么，我可以检查正确的）。

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.

在许多论坛上搜索，我看到一些丑陋的解决方法，比如强制重置PaperSize（ws.PageSetup.PaperSize = ws.PageSetup.PaperSize）。在尝试了其中一些之后，似乎工作得更好的是在更改为PageSetup之前关闭PrintCommunication，然后将其重新打开。这在我的大部分床单上运作良好，但在非常大的（约750行×80列，几乎所有带公式的单元格）上，它根本没有。

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,

谢谢，

2 个解决方案

#1

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.

当VBA代码在调试模式下按F8时工作正常时，我建议对该问题提出一般性建议，但是在按F5运行整个宏之后它不起作用。

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/

提示1.尽可能使用ThisWorkbook.ActiveSheet而不是ActiveWorkbook.ActiveSheet来引用正确的工作表。使用ThisWorkbook.Application而不仅仅是Application。可能是你有另一个Addin程序在后台工作，将ActiveSheet切换到你可能不知道的其他东西。检查启用的其他宏并删除任何不使用的宏。因此，在代码中的任何重要事项之前，请尝试使用ThisWorkbook.Activate获取工作表的焦点。请参阅该页面上的图表：http：//analystcave.com/vba-tip-day-activeworkbook-vs-thisworkbook/

Hint 2. Force Excel to wait in different way then DoEvents.

提示2.强制Excel以不同的方式等待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://*.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://*.com/a/11277152/1903793

Hint 3. If the above still does not work for refreshing use:

提示3.如果上述内容仍不适用于刷新使用：

ThisWorkbook.Connections("ConectionName").Refresh
ThisWorkbook.Application.CalculateUntilAsyncQueriesDone

https://*.com/a/26780134/1903793

#2

Another workaround for me was to set the PrintArea new (without .PrintCommunication = false)

我的另一个解决方法是将PrintArea设置为new（没有.PrintCommunication = false）

wks.PageSetup.PrintArea = wks.PageSetup.PrintArea

This workaround does not affect any pagesetup settings (unlike the .Zoom property)

此解决方法不会影响任何页面设置设置（与.Zoom属性不同）

The problem only occurs if the PageView is set to xlPageLayoutView and there is data "outside" the printarea (UsedRange is larger than PrintArea) when running the macro.

如果将PageView设置为xlPageLayoutView并且在运行宏时“printarea”（UsedRange大于PrintArea）之外存在数据，则只会出现此问题。

So you can check if the sheet is in PageLayoutView before you do the workaround. If the sheet is in any other view the pagebreaks.count always works fine here.

因此，在执行变通方法之前，您可以检查工作表是否在PageLayoutView中。如果工作表在任何其他视图中，pagebreaks.count始终可以正常工作。

#1