Prompt to overwrite existing files

[GohDiamond]

Hello again,

I'm using the 'docmd.output to' action to output excel files to a specific directory on my computer. The output works fine except, it DOESN'T recognize existing files of the same name and type <filename.xls> for example and ask if you want to overwrite the file.

I want to have that safeguard in the function. Here is the code.

'******************** Code Begin ****************
'This part gets the base path automagically
'
'Code courtesy of
'Terry Kreft & Ken Getz
'
Function CurrentDBDir() As String
Dim strDBPath As String
Dim strDBFile As String

strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))

End Function
'******************** Code End ****************

'------------------------------------------------------------
' Export_Corporate - Exports a query called Corporate to an Excel.xls file to the directory indicated (Exports\Corporate\Corporate.xls)
'
'------------------------------------------------------------

Function Export_Corporate()
On Error GoTo Export_Corporate_Err

DoCmd.OutputTo acOutputQuery, "Corporate", "Excel97-Excel2003Workbook(*.xls)", CurrentDBDir & "Exports\Corporate\Corporate.xls", False, "", 0, acExportQualityPrint


Export_Corporate_Exit:
Exit Function

Export_Corporate_Err:
MsgBox Error$
Resume Export_Corporate_Exit

End Function
------------------------------------------------

How can I get it to ask about OVERWRITING the existing file if it's there?
Cheers!
GohDiamond

 

[boblarson]

Do this:

If Dir(CurrentDBDir & "Exports\Corporate\Corporate.xls") <> "" Then
   If MsgBox("File Exists, Overwrite?, vbQuestion + vbYesNo, "Overwrite?") = vbYes Then
     DoCmd.OutputTo acOutputQuery, "Corporate", "Excel97-Excel2003Workbook(*.xls)", CurrentDBDir & "Exports\Corporate\Corporate.xls", False, "", 0, acExportQualityPrint
   Else
    ' put code here to elicit a new file name or you can add a number on to the name
   End If
End If

 

[GohDiamond]

So if the answer is NO (do not overwrite) then exit or saveas right?

-Goh

 

[boblarson]

So if the answer is NO (do not overwrite) then exit or saveas right?

-Goh

In my sample I let you decide how you want to deal with NO. You could bring up a dialog box to let them choose or an inputbox for the file name or you could exit or like I suggested append a number to the file name and save it that way.

 

[GohDiamond]

Can the Excel Files be exported with Freeze Pane headers or with List/Filters active in the header?

-Goh

 

[boblarson]

Well, don't know about the frozen headers but filters should go if you use my code from my website to send the form's recordset to Excel. I think the headers would be a bit more complex because you would have to iterate through them to see which ones are selected in which order and then actually create a new SQL statement based on those to pull them in the right order and only the ones you want.