Page Setup In Excel Using AcitveX

[ajckson]

Hello,

I'm trying to change the page setup properties in an Excel worksheet using ActiveX controls.  I have recorded a macro in VB, then tried to duplicate it in LV, but something isnt quite working.  The VB code for the page format is:

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With

My LV code is attached.

Thanks,
Adam

[jpaul]

Hi Adam,

Please provide more information related to why it isn't quite working:

• Do you receive an error when you run the VI? If so, what is the error code and text?
• If you run your VI in highlight execution (to enable highlight execution click the lightbulb on the toolbar in the block diagram), which ActiveX method/property does the error originate from?

Also I would strongly recommend that you close out all of your ActiveX references at the end of your application by using the "Close Reference.vi" found in the ActiveX palette.

Cheers,

Jonah
Applications Engineer
National Instruments

[amcolosk]

A side note, you could use the Report Generation Toolkit's Excel Run Macro.vi to run the macro in the Excel file.  This would require that the file used is a template, but I have been able to use this in my programs. 

[ajckson]

Thanks for the replies.

@jpaul

I actually ended up removing the PageSetup active area section, but now, no, there are no errors from the vi or from the AcitveX nodes when I highlight execution order.  I say that it isnt working because the page setup properties are not changing in the Excel file.

Yes, I have been lazy about not closing ActiveX references, is there any disadvantage other than digging a memory hole?

@drewsky

I have alot of things coded and working, this is the only issue so far.  Its too late to turn back to templates 🙂


Message Edited by ajckson on 06-10-2008 04:01 PM

[jpaul]

Adam,

So it sounds like you are only receiving an error when you use the PageSetup ActiveX property node. Is this the case? Do you still need to use the PageSetup for your application, because in your last post it sounds like you removed it?

If you do still need that ActiveX node, I would recommend wiring constants to all of the parameters of the PageSetup (your screenshot showed that you only wired up the FitsToPageTall & FitsToPageWide) from the macro printout that was in your original post.

Let me know if this fix worked or if you do not require the PageSetup property node.

Thanks,

Jonah

Applications Engineer

National Instruments