Format Excel document from Access Macro 1

[NMiller007]

I am using Access to output a query to Excel. I need to do some conditional formatting on the data, which doesn't export easily.

What I need to do is export the query, do the conditional formatting in Excel and then send the updated Excel document in an e-mail. I'd like to do all this without saving the Excel document locally. This process needs to be repeated about 20 times for each of our facilities and I'd like it to be as automated as possible.

I'm stuck at the very beginning; how can I control the Excel document from within my Access macro?

 

[NMiller007]

Yes, it is in there, but it doesn't seem like it is saving.

Should I save and close the file, then re-open it before sending? I don't know why that would make a difference, but...

Would you like me to re-post the code?

 

[NMiller007]

Okay, I got it to save by removing the objWB from that line and just using .Save.

Now it says something about the file being Excel '95 (or some older version) do you want to save over it. If I select Yes, everything goes through. How can I eliminate that box and just Save?

I tried setting Warning to False, but that didn't work. Is there a parameter I can set on Save?

 

[PHV]

Perhaps this ?
DoCmd.OutputTo acQuery, "TestQuery", "MicrosoftExcelBiff8(*.xls)", "temp.xls", False, "", 0
DoEvents
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
Set objWB = .Workbooks.Open("temp.xls")
With .Range(.Range("B2"), .Range("B2").End(-4121)) ' -4121=xlDown
.FormatConditions.Delete
.FormatConditions.Add Type:=2, Formula1:="=$D2=1" ' 2=xlExpression
.FormatConditions(1).Font.ColorIndex = 3
End With
.DisplayAlerts = False
objWB.Save
objWB.SendMail strTo, strSubject
objWB.Close True
.Quit
End With
Set objWB = Nothing
Set objXL = Nothing

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

 

[NMiller007]

That did it!!!

Everything seems to be working perfectly. Thank you very much.

Have a good one!