Export to Excel and overwrite existing file automatically

[kmacd6951]

I am running an Access Query and then I am wanting to save the results of this query as an HTML file. I need to be able to overwrite this file each time I rerun the query.

I set up a Macro to export the Access Data to Excel, and save as an HTML file using the "OutputTo" function, but this is not working because I am getting the pop-up message "Do you want to replace xxx file? Y/N"

I saw in some other posts on newsgroups, that someone suggested using the "SendKeys". I tried to add that in and hit the left arrow and enter keys (for those to be the keys that get sent - to move focus over the YES button and then accept), but it is still not working.

Then I have seen a few other suggest using the KILL function.... I am assuming to do that you need to use VBA, not a Macro.

I have posted the macro, which I had Access convert to VBA, below... Can anyone make suggestions for a way that I can export my results from Access query out to an HTML file that will get overwritten each time I run the query?
Thanks!


Option Compare Database

'------------------------------------------------------------
' Cases_Closed_Comments1
'
'------------------------------------------------------------
Function Cases_Closed_Comments1()
On Error GoTo Cases_Closed_Comments1_Err

DoCmd.OpenQuery "Cases Closed Query - Weekly Comments", acViewNormal,
acEdit
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acQuery, "Cases Closed Query - Weekly Comments",
"HTML(*.html)", "S:\Groupcast Solution Services\Weekly Comments.htm", False,
"", 0
SendKeys "", True
DoCmd.Close , ""


Cases_Closed_Comments1_Exit:
Exit Function

Cases_Closed_Comments1_Err:
MsgBox Error$
Resume Cases_Closed_Comments1_Exit

End Function



Expand AllCollapse All

 

[kmacd6951]

Re: Export HTML and overwrite existing file automatically

Disregard what I said about Excel... Excel is not included here...
I am wanting to Export my Access results to an HTML file that gets overwritten each time without having to click the YES button...

Thanks

 

[Norie]

How about simply this:

Kill "S:\Groupcast Solution Services\Weekly Comments.htm"

Put it right at the start of the code.

I'm assuming the name of the file is always the same.

 

[kmacd6951]

Re: Export to Excel and overwrite existing file automaticall

Thank you!!!