Late Binding: Excel Syntax Change 1

[kopy]

I am trying to implement late binding to make my users independent of their Excel Object Library. I've been successful at creating the spreadsheet, but I need alittle help wit the syntax changes for cell formatting. What would be that appropraite syntax change for xlPrintNoComments to make it independent of the Excel Library?

This is what I have so far:
===========================================================
Dim oApp As Object
Dim wr As Object ' Excel.Workbook
Dim sh As Object ' Excel.Worksheet

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'Export Report: Local
DoCmd.OutputTo acOutputQuery, "qryMDPHReport(MGHCampus)", "MicrosoftExcel(*.xls)", "c:\Impact Consulting\Partners\Occ Health\AnnualMDPH(MGH).xls", False, ""

Set sh = CreateObject("Excel.Application")
sh.Visible = True

'Open the Workbook: Local
Set wr = sh.Workbooks.Open("c:\Impact Consulting\Partners\Occ Health\AnnualMDPH(MGH).xls")

DoCmd.SetWarnings False

Set sh = wr.sheets(1)

'Sheet Settings
With sh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
sh.PageSetup.PrintArea = "$A$1:$O$8"
With sh.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
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 75
End With
===========================================================
As always, thanks for all the assistamce I receive, Kopy

 

[RoyVidar]

Use the literal value it represents, I often use the immediate pane (ctrl+g), then do

[tt]? xlPrintNoComments [/tt]

which should give -4142. Same information, you should be able to find through the object browser too (F2). This needs to be done on every xl<something> constants - or - you could declare them as constants on the top of the module.

Also, you have some stuff in you code needs changing, I'd suggest

[tt] 'Export Report: Local
DoCmd.OutputTo acOutputQuery, "qryMDPHReport(MGHCampus)", "MicrosoftExcel(*.xls)", "c:\Impact Consulting\Partners\Occ Health\AnnualMDPH(MGH).xls", False, ""

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'Open the Workbook: Local
Set wr = oApp.Workbooks.Open("c:\Impact Consulting\Partners\Occ Health\AnnualMDPH(MGH).xls")

DoCmd.SetWarnings False

Set sh = wr.sheets(1)[/tt]

Then, if you need the inchestopoints, use it like this:

...oApp.InchesToPoints(0.75)

Roy-Vidar