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?

 

[PHV]

Don't use macro but VBA code with OLE Automation.

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

 

[SkipVought]

Hi,

Check out my post to you in the MS Office forum68.

All you need is ONE parameter query. Loop thru the facility names, refresh the query, SendTo... facility.

Skip,

_[red][/red]

 

[NMiller007]

I'm sorry PH, I am using VBA. I seem to refer to them as macros, which gets confused with the simple macros in Access. Where can I find some info on OLE Automation? Basically all I need is to output to Excel and then format one column, then e-mail the result.

Skip, I couldn't find your post. I need a little more than a parameter query, because I need to do conditional formatting on the outputted Excel file. I have VBA procedure to cycle through all the different versions of the query and it will either send the file or save the file depending on what I do. Now that I need to do the conditional formatting on the Excel document I am having trouble.

Thanks for the replies!

 

[PHV]

The starting point of OLE Automation is the CreateObject function:
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
Set objWB = .Workbooks.Add
...
objWB.SendMail strTo, strSubject
objWB.Saved = True
objWB.Close False
.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]

Excellent. I pasted the code in and it works, now I have to fill in the blanks.

Once the object is created, how do I put my query data into the Excel document? I was using OutputTo to send the query to Excel, can I use that or is there another command I need?

I think I should be able to handle the conditional formatting part in Excel once the data is in that document.

Thanks!

 

[PHV]

I was using OutputTo to send the query to Excel
Could you please post that code ?

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

 

[NMiller007]

    DoCmd.OutputTo acQuery, "TestQuery", "MicrosoftExcelBiff8(*.xls)", "temp.xls", True, "", 0

Nothing fancy. It also requires me to save the file, but I can do it automatically. I guess that same functionality would work. The file can be saved, I just don't want to make the user save it and then import it into a mail message.

I tried setting objXL = OutputTo, but of course, that didn't work. Is there any way I can OutputTo and then take control of the file to do my formatting?

 

[PHV]

DoCmd.OutputTo acQuery, "TestQuery", "MicrosoftExcelBiff8(*.xls)", "temp.xls", True, "", 0
DoEvents
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
Set objWB = .Workbooks.Open "temp.xls"
...
objWB.SendMail strTo, strSubject
objWB.Saved = True
objWB.Close False
.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]

VB doesn't like this line:

  Set objWB = .Workbooks.Open "temp.xls"

I get a "Compile Error: Expected End of Statement".

I removed the "temp.xls" part and ran the macro, but I eventually got an "Argument Not Optional" error, which I assume is from that line?

 

[PHV]

Sorry for the typo:
Set objWB = .Workbooks.Open("temp.xls")

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

 

[NMiller007]

I'm sorry, I thought I tried that.

It seems to be working, thanks! Now all I have to do is figure out the conditional formatting portion.

Thanks for taking the time to get this going, I really appreciate it.

 

[NMiller007]

Err...back again.

I have created a macro in Excel and now I want to run that macro from within the VBA procedure. Here is the Excel macro code:

    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$D2=1"
    Selection.FormatConditions(1).Font.ColorIndex = 3

I tried preceding each line with objWB. but that doesn't work and it won't work with them as is. What do I have to do to tell Access to do these things to the active Worksheet?

 

[PHV]

With objXL
...
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
...

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

 

[NMiller007]

Okay, that looks good, but it seems to be sending the original file, rather than the one with the conditional formatting. Do I have to save the document after performing the Excel macro portion?

Here is all the code:

DoCmd.OutputTo acQuery, "TestQuery", "MicrosoftExcelBiff8(*.xls)", "temp.xls", True, "", 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
  ' Save here?

  objWB.SendMail strTo, strSubject
  objWB.Saved = True
  objWB.Close False
  .Quit
End With
Set objWB = Nothing
Set objXL = Nothing

 

[PHV]

I guess you should.
...
End With
objWB.Save
objWB.SendMail strTo, strSubject
objWB.Close False
.Quit
End With
Set objWB = Nothing
Set objXL = Nothing


BTW, did you populate strTo and strSubject ?

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

 

[NMiller007]

For some reason, there are two versions of the Excel sheet opening (I should have said that when I first noticed it). I guess I am running the macro on the second, read-only version. Maybe that is why the modified version is not sending. any idea why I might be opening two? Should I remove one of thesre lines?

The code is still the same as above, I won't repost it.

I haven't populated strTo or strSubject yet. I think I will use the strSubject, but the person running the files will add the recipients manually (so how much work am I really saving them?)

Thanks again!

 

[PHV]

DoCmd.OutputTo acQuery, "TestQuery", "MicrosoftExcelBiff8(*.xls)", "temp.xls", [!]False[/!], "", 0

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

 

[NMiller007]

Okay, that only opens one version and I can go through everything fine, but it still is sending the non-edited version.

I looked at the saved file and that is the original as well. The formatted one is visible on the screen though.

Any ideas?

 

[PHV]

Did you add the objWB.Save line before objWB.SendMail ?

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