Run-time error 450 in formatting Excel sheet from Access

[cvaccess]

Hi,

Disclaimer:I am novice to Access development. I recently was challenged to making enhancements to a db. I am trying to first get through the first hump of getting the header row bolded. I have gotten far enough of being able to display the sheet after it goes through multi queries that outputs to this sheet(which I excluded since they were lengthy and had no impact to this piece). I tried so many different ways to try (I looked at help topics, google search on samples) and bold the header and I can't. I really need help. I get the run time 450 error on the line xlObj.Rows(1).Range.Font.Bold = True. What am I doing wrong? After I get help on how to fix this I want to be able to highlight rows that have the value 00 in column F. I tried this too and could not but I thought at least I could do the header bold code since it seemed more simple. Anyway, obviously I need help. Anyone, please help! Thanks in advance!!!!

Private Sub cmdView_Click()
On Error GoTo errHandle
     
     Dim strReportName As String
     Dim strReportFormat As String
     Dim strContent As String
     Dim strMSG As String
     Dim strActType As String
     Dim strDate As String
     Dim strFileName As String
     Dim strPath As String
     Dim fsO As Object
     Dim stDocName As String
     Dim f_Path As String
     Dim xlObj As Excel.Application


If f_isEmptyRecordSet("select * from qryActionByCode") Then
                         MsgBox strMSG
Exit Sub
Else
                         
DoCmd.SetWarnings False
 
stDocName = "qryActionByCode"
strDate = Format(Date, "mm") & Format(Date, "dd") & Format(Date, "yy")
f_Path = "C:\Documents and Settings\cvalde3\My Documents\EDI\jetscrub\ediData"
Set fsO = CreateObject("Scripting.FileSystemObject")
                       
strFileName = strDate & Forms![frmDialogActionItems]![txtTPinfo] & "IEDI" & "_" & strActType & "test.xls"
strPath = f_Path & "\" & strFileName
                         
If fsO.FileExists(strPath) = False Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName, strPath, True
Set xlObj = CreateObject("Excel.Application")

xlObj.Workbooks.Open (strPath)
xlObj.Visible = True
xlObj.Rows(1).Range.Font.Bold = True
xlObj.SaveWorkspace
xlObj.Workbooks.Close

MsgBox "Successfully exported to the Vendor Exchange File Share!"

Else

MsgBox strFileName & vbCrLf & "You have already this file to the Vendor Exchange File Share!"

End If
                         
DoCmd.SetWarnings True
                         
End If

End Select     ' end format selection (report or datasheet)

               

Exit Sub
errHandle:
          MsgBox Err.Description, vbInformation, "cmdView"
End Sub

 

[cvaccess]

I fixed the first problem which I can't believe fixed it. I just had to remove range and it bolded the header (to this: xlObj.Rows(1).Font.Bold = True). So now, what I need help doing is figuring how to lookup column f for the value 00 and highlighting that row. If anyone has insight on this please help.

Thanks!!

 

[PHV]

Use the macrorecorder when using conditional formatting manually.

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

 

[cvaccess]

Thank you PH. I didn't think I could translate what I did in Excel macros to my Access VBA code. I just had to change it to my object and that worked. So this is what I added:
Columns("F:F").Select
With xlObj.ReplaceFormat.Font
.FontStyle = "Bold"
End With

Selection.Replace What:="00", Replacement:="00", LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

I'd like to have it highlight the whole row when it finds the value 00 in column F but I could do it manually either in the macro. Any ideas anyone?