Hi Bob
I've managed to sort it out. I've modified my original code from
.save
to
.saveas filename:=name fileformat:=xlWorkbookNormal
The reason I couldn't find any reference to fileformat is because I was
looking in Access VBA help, not Excel VBA help. I still don't see where you
got the 46 & 56 from, though.
The only problem I've found is that I had to save to a different filename
that the one I was working on as Excel brings up a dialog warning that the
file already exists etc. and I need it to be transparent to the user. It's
not a problem as I'm outputting data from Access to an Excel sheet,
modifying the sheet, sending the result as a file attachment then deleting
the file. I just have 2 files to delete instead of 1.
--
Ian
--
"Bob Phillips" wrote in message
...
Ian,
I don't know what the ActiveX Data Objects has to do with getting an
instance of Excel. It is more likely that Excel was not started, in which
case you should use
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error Goto 0
If oXL Is Nothing Then
Set oXL = CreateObject("Excel.Application")
oXL.Visible = true
End If
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Ian" wrote in message
...
Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't
get your code to work.
I created a new module, pasted your code into it and ran it. The Set oXL
line returns "Run-time error '429': ActiveX component can't create
object".
I updated the references to MS ActiveX Data Objects 2.8 Library (from
2.1) and got over this hurdle, but the "saveas" line returns "Run-time
error '1004': SaveAs method of Workbook class failed". I tried adding
references to MS ActiveX Data Objects 2.8 Recordset Library and MS
ActiveX Plugin, but they didn't help.
Any ideas why this could be? Do I have the wrong version of ActiveX? Do I
need to enable something else in Access?
--
Ian
--
"Bob Phillips" wrote in message
...
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
--