Replace Existing File? Y/N in a Macro or VBA

[Guest]

In my Macro to export Access data to Excel, I am getting the pop-up message
that my xxx file already exists, do you want to overwrite it. I saw in
another post on this newsgroup, 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 have posted the macro, converted to VBA, below... Can anyone make
suggestions for either the Kill or SendKeys?
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

 

[Steve Schapel]

Katie

You can't do this with a macro.

Your VBA code is confusing. I don't understand the purpose of the
OpenQuery method, nor the SetWarnings method, you have a Function
whereas it is not really a function, and the OutputTo is using html
format whereas your message said you are trying to export to Excel.

Anyway, try something like this, assuming the export is supposed to
happen when you click a button called Cases_Closed_Comments1 ...

Private Sub Cases_Closed_Comments1_Click()
If Len(Dir("S:\Groupcast Solution Services\Weekly Comments.htm") Then
Kill "S:\Groupcast Solution Services\Weekly Comments.htm"
End If
DoCmd.OutputTo acQuery, "Cases Closed Query - Weekly Comments",
"HTML(*.html)", "S:\Groupcast Solution Services\Weekly Comments.htm", False
End Sub

 

[Guest]

Steve -
Thanks for your help. Sorry it was confusing... I am not good at the VBA
stuff at all. I have only really used Macro's that you create in Design
view of Access, but was trying to use VBA. I took the Macro I had -- that
was working except for the part with the pop-up message, and had Access Save
As a Module - to convert it to VBA.

Perhaps I should attack this differently.... What I am needing to do is
export my datasheet from a Query in Access (Cases Closed Query - Weekly
Comments) to Excel and save that datasheet as "S:\Groupcast Solution
Services\Weekly Comments.htm". When this happens I want it to overwrite the
file that previously existed.

I didn't really want to have to click for this to happen, but rather be able
to schedule this routine so it can happen without any user intervention. Is
that possible?

Thanks again!

 

[Steve Schapel]

Katie

Weekly Comments.htm is a HTML format file. If you want to export to an
spreadsheet, it would probably be called Weekly Comments.xls instead.

As for it happening without user intervention, what do you imagine will
be the event that triggers the export... At a certain time/date? When
the database is opened? Or closed? When a certain form is opened?
When certain data is updated?

 

[Guest]

Steve -
Yes, I want to save the Excel file in an HTML format, so I do want it to be
Weekly Comments.htm. This allows others to access the file that will get
overwritten each week, through the same link, and always have the current
weeks data.

I planned to use Windows Scheduler to schedule this processes to happen on a
weekly basis.

Thanks

 

[Steve Schapel]

Katie,

Yes, I want to save the Excel file in an HTML format, so I do want it to be
Weekly Comments.htm.

I am sorry, you have completely lost me here, I can't figure out what
this means. Which Excel file?

I planned to use Windows Scheduler to schedule this processes to happen on a
weekly basis.

Good idea.