Formatting Excel from Access

[jmcg]

Am trying to export some data to excel and then format the excel spreadsheets.
I have looked thru various threads and tried the following coede, when I run it says the object doesn't support this property or method.
I really do not know much about this area so any help would be appreciated.

Dim oXL As Object 'Excel.Application
Dim oWb As Object 'Excel.Workbook
Dim oSh As Object 'Excel.Worksheet
Dim X As Integer
Dim currentRow, currentColumn, currentQuarter As String, categoryChanged As Boolean, currentCategoryID As Integer


Set oXL = CreateObject("Excel.Application")
Set oWb = oXL.Workbooks.Open(jmDir & "ScriptsLeads\ScriptsLeads_" & jmChan & "_" & jmSM & "_" & jmAM & ".xls")


With oWb.activesheet
    .Sheets(Array("Broadband", "Connectivity", "LAN", "Switch", "Mobile", "CLSComp", _
        "CLSInb")).Select
    .Sheets("Broadband").Activate
    .Columns("A:F").Select
    .Columns("A:F").EntireColumn.AutoFit
    .Range("A1:F1").Select
    With Selection.Interior
        .ColorIndex = 16
        .Pattern = xlSolid
    End With
    .Selection.Font.ColorIndex = 2
    .Selection.Font.Bold = True
    .Columns("A:F").Select
    .Columns("A:F").EntireColumn.AutoFit
    .Columns("C:C").Select
    .Selection.FormatConditions.Delete
    .Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""Done"""
    .Selection.FormatConditions(1).Font.ColorIndex = 2
    .Selection.FormatConditions(1).Interior.ColorIndex = 10
    .Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""req"""
    .Selection.FormatConditions(2).Font.ColorIndex = 2
    .Selection.FormatConditions(2).Interior.ColorIndex = 3
    .Columns("D:F").Select
    .Selection.FormatConditions.Delete
    .Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$D2=""No Prospect"""
    .Selection.FormatConditions(1).Interior.ColorIndex = 6
    .Sheets("Broadband").Select
    .Range("A1").Select
    .ActiveWorkbook.Save
End With

oWb.Close

 

[PHV]

I guess the highlighted line of code when in debug at the time of the error is the following ?
.ActiveWorkbook.Save
Replace it it with this:
oWb.Save

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmcg]

No, it is the first line that is highlighted.
Have tried comenting it out but it just moves to the next line

 

[CompuSolve]

Try this instead:

Set xlApp = New Excel.Application
xlApp.Visible = False

Set xlBook = xlApp.Workbooks.Open(fileIn)

Set xlSheet = xlBook.Sheets(sheetIn)

Where fileIn and sheetIn are the file and path - you can see all the code at

http://www.tek-tips.com/viewthread.cfm?qid=1201057

My post is at the bottom - it is a good example of how to automate Excel from Access

 

[CompuSolve]

Oh yeah, the variable declarations are:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

If the variable declarations aren't working, you need to make sure you have a reference to Microsoft Excel Object Library under Tools/References