Store and retrieve Excel PageSetup

265 Views Asked by At

I'm programming an Excel VBA macro to automate a print task. What I want to do is to store the PageSetup object of a workbook, do my things, and then restore the initial parameters. I tried this :

Sub test()

Dim TempPageSetup As PageSetup

    Set TempPageSetup = ActiveSheet.PageSetup

    'Modify the ActiveSheet.PageSetup 
    'Print the worksheet

    Set ActiveSheet.PageSetup = TempPageSetup


End Sub 

But I get "Object variable or With block variable not set" error when I try to put back the initial pagesetup object. Is a worksheet pagesetup object readonly?

I also tried to put it back property by property in a With activeSheet.PageSetup statement. That seems to work but at some point I got an "Automation error" and Excel just shut down. I run the code step by step and it does not always happen on the same property. Any explanation? I'm still investigating what is going on.

0

There are 0 best solutions below