Solved: Setting worksheet Font.Bold in Access VBA

[morgen]

I'm using an Access 2002 database to build an Excel worksheet. When I try to set the Font.Bold property of a specific cell, it behaves as if it's read-only, and gives me the error message "Run-time error '1004': Unable to set the Bold property of the Font class". A highly simplified code snippet:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "MyQuery", MyFile

Set xlBook = xlApp.Workbooks.Open(MyFile)
With xlBook.Worksheets(1)
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Value = MyValue
End With
xlBook.Save
xlBook.Close

Thanks in advance for any assistance.:friends:

[xCav8r]

Morgen,

Welcome to VBAX! :hi:

I know that you're working from within Access, but this question could be about automating Excel from any VBA application. The problem you're having is with Excel, not Access, so in the future you might consider posting it in the Excel forum when you've got a similar question. You might get more of what you're looking for there. (That said, a lot of Excel folks to read the Access forums.) :)

I'm no Excel expert, but I'm not sure that version 3 of Excel supports bold. Do you really need to export into such an old format? When I export with the default setting, I have no problem making it bold, which I guess does make it an Access issue. :)

Sub GetOut()
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim MyFile As String
Dim MyValue As String
MyValue = "not too much"
MyFile = "C:\temp\morgen.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "MyQuery", MyFile
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(MyFile)
With xlBook.Worksheets("MyQuery")
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Value = MyValue
End With
Stop
xlBook.Save
xlBook.Close
xlApp.Quit
End Sub

HTH! http://vbaexpress.com/forum/images/smilies/whistle.gif

[morgen]

Thank you!