Re: Conditional format problem

Oops, just noticed the mistake in my last post. It's ACCESS that outputs to
an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
"Ian" <me@xxxxxx> wrote in message
news:Kilyh.379$Fg4.375@xxxxxxxxxxxxxxxxxxxxxxx
Hi Bob

I already had some code modifying cells using activecell, so I modded your
suggested code to suit (to try to keep some consistency). I've since
figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to an
Excel 5.0/95 format spread***. Do you know if there's a way to force the
saving in Excel 2000 format?

--
Ian
--
"Bob Phillips" <bob.NGs@xxxxxxxx> wrote in message
news:%23amDPuqSHHA.4956@xxxxxxxxxxxxxxxxxxxxxxx
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

Dim strRange As String
Dim strCondition1 As String
Dim x, i As Long
For x = 2 To 7
strRange = "L" & x
Debug.Print strRange
strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)"
Debug.Print strCondition1
With oWB.Worksheets("Time sheet for submission").Range(strRange)
For i = 1 To .FormatConditions.Count
.FormatConditions(i).Delete
Next i
With .FormatConditions.Add(xlExpression, , strCondition1)
.Font.ColorIndex = 3
End With
End With
Next

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ian" <me@xxxxxx> wrote in message
news:U5iyh.337$Fg4.40@xxxxxxxxxxxxxxxxxxxxxxx
Bob & Doug

Thanks for your combined effort. I can get the code to work perfectly as
a standalone macro in Excel. Unfortunately I'm trying to use it as part
of an Access macro to add formulae and formatting to an Excle ***.
Most of the other code I've used has easily transferred, but this isn't
working out.

If I use:
objExcel.activecell.FormatConditions.Add(xlExpression,,strCondition1)
I immediately get a VB compile error saying "Expected: =" when trying
to leave the line.
If I use:
objExcel.activecell.FormatConditions.Add Type:= xlExpression, Formula1:=
strCondition1
the code appears to compile correctly, but when I run it I get
"Run-time error '5' Invalid procedure call or argument"

I'm using strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" and I've also tried
"=DAY(A2)=DAY(A3)"

The earlier code produces the spread*** so there are no pre-existing
conditions applied, but I've added the line
objExcel.activecell.FormatConditions.Delete before the line above and
this runs fine.

Any ideas?
--
Ian
--
"Bob Phillips" <bob.NGs@xxxxxxxx> wrote in message
news:OFDDSakSHHA.3440@xxxxxxxxxxxxxxxxxxxxxxx
No good reason Doug, that is a better idea. I was trying it out on
2007, and I got bogged down thinking about more than 3 conditions. Your
suggestion works just as well in 2007 also.

Bob



"Doug Glancy" <nobodyhere@xxxxxxxxxxxxxxxx> wrote in message
news:%23kWUPHkSHHA.3428@xxxxxxxxxxxxxxxxxxxxxxx
Bob,

Is there a reason not to just use:

.FormatConditionsDelete

I tried it on a few tests, including ranges with different numbers of
FormatConditions, and it worked. But knowing you, there's a good
reason for the way you did it.

thanks,

Doug

"Bob Phillips" <bob.NGs@xxxxxxxx> wrote in message
news:usfFZ4jSHHA.488@xxxxxxxxxxxxxxxxxxxxxxx
Forgot to mention, best to clear any exsiting conditions down, just
in case

Sub test()
Dim strRange As String
Dim strCondition1 As String
Dim x, i As Long
For x = 2 To 7
strRange = "L" & x
Debug.Print strRange
strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)"
Debug.Print strCondition1
With Worksheets("Time sheet for submission").Range(strRange)
For i = 1 To .FormatConditions.Count
.FormatConditions(i).Delete
Next i
With .FormatConditions.Add(xlExpression, , strCondition1)
.Font.ColorIndex = 3
End With
End With
Next
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob Phillips" <bob.NGs@xxxxxxxx> wrote in message
news:uHIxxyjSHHA.5068@xxxxxxxxxxxxxxxxxxxxxxx
Use RC notation

Sub test()
Dim strRange As String
Dim strCondition1 As String
Dim x
For x = 2 To 7
strRange = "L" & x
Debug.Print strRange
strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)"
Debug.Print strCondition1
With Worksheets("Time sheet for
submission").Range(strRange).FormatConditions.Add(xlExpression, ,
strCondition1)
With .Font
.ColorIndex = 3
End With
End With
Next
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



"Ian" <me@xxxxxx> wrote in message
news:E25yh.153$Fg4.126@xxxxxxxxxxxxxxxxxxxxxxx
This code is supposed toset a conditional format to "hide" the
contents of a cell if the following line has the same date:

Sub test()
Dim strRange As String
Dim strCondition1 As String
For x = 2 To 7
strRange = "L" & x
Debug.Print strRange
strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")"
Debug.Print strCondition1
With Worksheets("Time sheet for
submission").Range(strRange).FormatConditions.Add(xlExpression, ,
strCondition1)
With .Font
.ColorIndex = 3
End With
End With
Next
End Sub


I set sthe Debug.Print lines to try to understand where the code is
going wrong, but I still can't figure it out. On the face of it,
the code should work, especially given the output I got in the
Immediate window:
L2
=DAY(A2)=DAY(A3)
L3
=DAY(A3)=DAY(A4)
L4
=DAY(A4)=DAY(A5)
L5
=DAY(A5)=DAY(A6)
L6
=DAY(A6)=DAY(A7)
L7
=DAY(A7)=DAY(A8)

This is as expected, but the forumlae in the spread*** reference
the wrong lines after the first instance as below.

Conditional format contents:
L2 refers to A2 A3 as expected but
L3 refers to A4 A5 instead of A3 A4
-
-
-
L7 refers to A12 A13

Can anyone suggest a cure for what's happening here?

--
Ian
--


















.