Opening & formatting Excel spreadsheet from VBA (1 Viewer)

[J_Orrell]

Struggling with something that should be obvious. I'm trying to open, format and save an Excel spreadsheet in the background from Access. Everything's working fine other than the conditional formatting lines. They don't return an error but they simply do not work for no reason I can figure :banghead:

What am I doing wrong?

Dim Excelsheet As Object

Set Excelsheet = CreateObject("Excel.Application")
With Excelsheet
    .Visible = False
    .Workbooks.Open FileName:= "C:\My_Workbook.xls"
    .Columns("B:D").NumberFormat = "£#,##0.00" ' Currency format columns B-D
    .Range("A1:D1").Font.Bold = True ' Bold A1-D1 (title bar)
    .Range("A1:D1").Interior.Color = 65535 ' Yellow title bar.
    'Conditional format column B: this is the bit that is just simply being ignored!
  [B]  .Columns("B:B").FormatConditions.Add Type:=1, Operator:=4, Formula1:="=0"  ' xlCellValue, xlNotEqual, 0
    .Columns("B:B").FormatConditions(1).Interior.Color = 5296274 ' Green for non-zero values in column B
    [/B]'The rest works fine
    .Range("B1:D1").HorizontalAlignment = -4152 ' Right Justify B1-D1
    .Cells.Select
    .Cells.EntireColumn.AutoFit ' Autofit entire sheet
    .Range("A1").Select ' Select top left cell
    ' Save workbook
    .ActiveWorkbook.Save
    .Workbooks.Close
End With
Excelsheet.Application.Quit

The same two lines work fine if pasted into a module in the actual spreadsheet, but that is not what I want to do, for various reasons.

Thanks

 

[Ranman256]

excel may recognize the parameters as string assigments:
.FormatConditions.Add Type:=1, Operator:=4, Formula1:="=0"

but not Access. You must put the params in the correct order WITHOUT string assignments:
(xlCellValue, xlGreater, "=$a$1")
FormatConditions.Add(1, 4, "=$a$1")

 

[J_Orrell]

all that returns is a syntax error.

 

[arnelgp]

Dim xlApp As Object     'Excel.Application
Dim xlWB As Object      'Excel.Workbook
Dim xlSh As Object      'Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\My_Workbook.xls")
Set xlSh = xlWB.Sheets(1)

xlApp.Visible = True

With xlSh
    .Range("B:D").NumberFormat = "£#,##0.00"
    With .Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = 65535
    End With
    .Range("B:B").Select
    With xlApp.Selection
        .FormatConditions.Add Type:=1, Operator:=4, Formula1:="=0"
        .FormatConditions(1).Interior.Color = 5296274
    End With
    .Range("B1:D1").HorizontalAlignment = -4152
    .Cells.Select
    .Cells.EntireColumn.AutoFit
    .Range("A1").Select
End With
xlWB.Close True
xlApp.Quit
Set xlApp = Nothing

 

[J_Orrell]

@arnelgp thanks for that, I have tried it. Although your code is neater than mine the two conditional formatting lines are just as much ignored in your code as they are in mine.

 

[J_Orrell]

Fixed it!!

I am a blithering idiot. I didn't even think of mentioning that this was part of an ancient Access database that creates said spreadsheet using the 'TransferSpreadsheet' method from a query, and it was using the 'acSpreadsheetTypeExcel5' argument, and of course 'acSpreadsheetTypeExcel5' is pre-Excel 95 which doesn't support conditional formatting!

What a clown. I should have spotted that argument in the legacy code WAY before raising the problem with you guys.

Thanks for your help.

 

[arnelgp]

maybe you should first remove all FormatConditions
previously saved to the worksheet.

Dim xlApp As Object	'Excel.Application
Dim xlWB As Object	'Excel.Workbook
Dim xlSh As Object	'Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\My_Workbook.xls")
Set xlSh = xlWB.Sheets(1)

xlApp.Visible = True

' remove all FormatConditions first
With xlSh
    .Range("B:D").NumberFormat = "£#,##0.00"
    With .Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = 65535
    End With
    .Range("B:B").Select
    With xlApp.Selection
        ' delete all formatconditions first on this range
        .FormatConditions.Delete
        
        .FormatConditions.Add Type:=1, Operator:=4, Formula1:="=0"
        .FormatConditions(1).Interior.Color = 5296274
    End With
    .Range("B1:D1").HorizontalAlignment = -4152
    .Cells.Select
    .Cells.EntireColumn.AutoFit
    .Range("A1").Select
End With
xlWB.Close True
xlApp.Quit
Set xlApp = Nothing

 

[J_Orrell]

@arnelgp thanks for your help, but fixed as per my post prior to your most recent.