Prompt if overwriting

Question

I had an access macro that I let access convert to vba.  The macro used to stop and give the windows built-in prompt to the user if the file already existed, and give them the opportunity to rename or replace.  The code does not:

DoCmd.OutputTo acOutputReport, "ESR", "MicrosoftExcelBiff8(*.xls)", Forms!ExcelExport!ReportTitle, True, "", 0, acExportQualityPrint

How do I make the vba give the prompt if overwriting?

Answer 1

Check out the Dir function in the VBA help file.

Answer 2

Thank you, but that is  little over my head. I hate to ask, but can anyone simplify, or maybe spoon feed me a little on this one? I am self taught, and not ingesting this properly :-(

This is my macro:

It gives me this prompt automatically if the file already exists:

How can I get this behavior to kick in with the VBA code that Access created for me when I chose the "convert to vba" feature from the macro screen?  If the macro does it automatically, I assume there may be some switch in the vba command that would kick in this windows behavior.  Hoping its that simple...

Answer 3

Please post the VBA code that the Convert routine created and someone should be able to fix it.

Note that (because of the nature of macros, mostly) the Convert routine builds some REALLY ugly VBA. It'll work but it's rarely clear or efficient code.

Answer 4

Sure thing! This is the VBA code that the Convert routine created:

DoCmd.OutputTo acOutputReport, "ESR", "MicrosoftExcelBiff8(*.xls)", Forms!ExcelExport!ReportTitle, True, "", 0, acExportQualityPrint

Answer 5

KarenAnne_hm –

1. Launch your database.
2. Near the top of the application window are the menus. In Access 2010, they are File, Home, Create, External Data, Database Tools, and perhaps another one or two. Click the database tools menu tab to open that ribbon.
3. Second from the left is Visual Basic. Click that. A whole new world opens before you.
4. In the upper right hand corner is the help search window. In that window, type docmd.outputto. The Help window will open, but may say “nothing available”. That’s when you go to the lower right corner of the help window and click on “Connected to Office.com” to open yet another dropdown. Choose “Show content only from this computer”. (Are we having fun yet?)
5. Now at last, you should have some helpful content in the help window. You might find that the 4^th^ parameter “Forms!ExcelExport!ReportTitle” is the name of the file to be (over)written.
6. Now you can do an internet search on “VBA file exists” and get some code you can use to prevent overwriting. It will look something like this:

Public Function ShallIOverwrite(fname As String) As Boolean

    Dim exists As Boolean

    exists = FileExists(fname) '  find on internet

    Dim result As Integer

    result = vbNo

    If exists Then

        result = MsgBox(fname & " exists. Overwrite?", vbYesNo, "OVERWRITE FILE?")

    End If

    If Not exists Or (result = vbYes) Then

        DoCmd.OutputTo acOutputReport, "ESR", "MicrosoftExcelBiff8(*.xls)", fname, True, "", 0, acExportQualityPrint

    End If

End Function