It's a Long!
I don't work much with Access, but I just ran it up and used this code in
Access
Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)
With oWS.range("A1")
With .FormatConditions.Add(2, , "=A15")
.interior.colorindex = 3
End With
.Value = 25
End With
oWB.saveas filename:="C:\Test.xls", FileFormat:=46
Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing
End Sub
and it created a file fine with CF preserved.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Ian" wrote in message
...
Hi Bob
I've been doing some more checking and found references to fileformat in
the MS knowledge base. Unfortunately it's expecting a string, not a
number.
--
Ian
--
"Bob Phillips" wrote in message
...
Can you try setting the fileformat property of the save to 56?
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Ian" wrote in message
...
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" wrote in message
...
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 spreadsheet. Do you know if there's a
way to force the saving in Excel 2000 format?
--
Ian
--
"Bob Phillips" wrote in message
...
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" wrote in message
...
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
sheet. Most of the other code I've used has easily transferred, but
this isn't working out.
If I use:
objExcel.activecell.FormatConditions.Add(xlExpress ion,,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 spreadsheet 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 spreadsheet
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
--