Formatting Excel From Access

[kopy]

I'm trying to export an Access report to Excel and then add some labels and formulas that do not export. My first roadblock is that Access VBA doesn't recognize the value in the formatting command:

.Underline = xlUnderlineStyleNone

My current error message is:

"Unable to set the Underline property of the Font Class"

Any help with this issue will be greatly appreciated.
Thanks, Mike
-----------------------------------------------------------
Dim oApp As Object

DoCmd.OutputTo acReport, "qryCurrentQtrStats", "MicrosoftExcel(*.xls)", "c:\hvma\FY2002_Turnover_Dashboard\CurrentQtrStats.xls", False, ""

Set oApp = CreateObject("Excel.Application&quot
oApp.Visible = True
'Open the Workbook
oApp.Workbooks.Open "C:\hvma\FY2002_Turnover_Dashboard\CurrentQtrStats.xls"
oApp.Rows("1:1&quot.Select
oApp.Selection.Insert Shift:=1
oApp.ActiveCell.FormulaR1C1 = "FY2002 Quarter"
oApp.Range("A1&quot.Select
oApp.Selection.Font.Bold = True
With oApp.Selection.Font
.Name = "MS Sans Serif"
.Size = 24
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

 

[sfvb]

If you want to use Excel classes, just add a reference to the Excel object library. For example, if you are using Office 2000:

[ol][li]Open the Access Project[/li]
[li]Go to the Visual Basic Editor (shortcut is Alt+F11)[/li]
[li]From the menu, select Tools-->References...[/li]
[li]Scroll down the list from the popup window until you find Microsoft Excel 9.0 Object Library[/li]
[li]Put a checkmark next to it and select OK[/li][/ol]After adding the reference, it should run without error.

Alternately, you could also change the line
.Underline = xlUnderlineStyleNone
to
.Underline = False

 

[kopy]

Worked great!

Thanks,
Mike