Access 2003: Export to Excel and Set Margins 3

[mgrabows]

I'm using Bob Larson's Send2Excel module to export query results to Excel spreadsheets. I've made an alteration to set the margins, but it seems like Excel is interpreting my margins perhaps in another measuring system.

This is my code addition:
'Set Margins
ApXL.ActiveSheet.PageSetup.LeftMargin = (0.25)
ApXL.ActiveSheet.PageSetup.LeftMargin = (0.25)
ApXL.ActiveSheet.PageSetup.RightMargin = (0.25)
ApXL.ActiveSheet.PageSetup.TopMargin = (0.75)
ApXL.ActiveSheet.PageSetup.BottomMargin = (0.75)
ApXL.ActiveSheet.PageSetup.HeaderMargin = (0.5)
ApXL.ActiveSheet.PageSetup.FooterMargin = (0.5)

The results are top and bottom margins of 0.0104166666666667, right and left 0.00347222222222222, and header/footers as 0.0104166666666667.

The macro I based my code off of (below) references Application.InchesToPoints. I can't figure out how to get this reference into my code.

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
End With

Has successfully altered inch margins in an Excel export?

 

[randy700]

Here's the method I use to get the exact code Excel needs.
1. Perform the needed steps directly in Excel while recording a macro.
2. Copy/paste the code into Access.
3. Delete the Excel macro.


Randy

 

[PHV]

Try this:

With ApXL.ActiveSheet.PageSetup
        .LeftMargin = ApXL.InchesToPoints(0.25)
        .RightMargin = ApXL.InchesToPoints(0.25)
        .TopMargin = ApXL.InchesToPoints(0.75)
        .BottomMargin = ApXL.InchesToPoints(0.75)
        .HeaderMargin = ApXL.InchesToPoints(0.5)
        .FooterMargin = ApXL.InchesToPoints(0.5)
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mgrabows]

PHV,

Thank you! That works.

Just a quick question: Some formatting from Laron's code is enclosed in With/End With statements. Then sometimes you just have things without End/End With, like this: ApXL.ActiveSheet.PageSetup.LeftFooter = "Page &P of &N"

What's the reason for that? I read up a bit on With and see that it's used for executing several statements. But if I can set the LeftFooter in PageSetup without using a With statement, why do I need to put the margins in a With statement?

Just trying to understand a bit better. Thanks!

 

[Golom]

With" and "End With" are programming constructs that allow you to specify an object in the "With" statement and then any variable appearing within the scope of the "With" -- "End With" that begin with periods (".") or the bang operater ("!") are assumed to refer to the object in the "With" statement.

That's a bit abstract so an example is

With datLocations.Recordset
    If ![Field1] = SomeValue Then
        [blue]... Some Code ...[/blue]
    Else
        If Not .EOF And Not .BOF Then
            [blue]... Some other Code ...[/blue]
        End If
    End If
End With

Is equivalent to

If datLocations.Recordset![Field1] = SomeValue Then
    [blue]... Some Code ...[/blue]
Else
    If Not datLocations.Recordset.EOF And Not datLocations.Recordset.BOF Then
        [blue]... Some other Code ...[/blue]
    End If
End If

The difference (aside from typing) is that the compiler doesn't need to spend time resolving the meaning of "datLocations.Recordset" inside the "With -- End With" because it has already determined that at the beginning. That can be important when you have an object that is composed of several period-delimited sub objects such as

With myObject.FirstSubObject.SecondSubObject.ThirdSubobject
[blue]... many references to the WITH object ...[/blue]
End With

The reference needs to be resolved only once instead of every time it appears.