CODEWITHSUNDEEP
ms-access
Greg
Greg

Reputation:

How do I overwrite files without prompt in Access 2007?

I am trying to output multiple tables to excel files. Every time I run the macro it prompts me to overwrite the old file. I am looking for a method that doesn't involve send keys because it locks the keyboard and mouse until the macro completes.

What would the best solution be?

Upvotes: 1

Views: 17377

Answers (6)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

To avoid duplicate files, I usually append the date and time to the filename. This also has the advantage of allowing the user to keep multiple versions of the same report.

Public Sub ExportToExcel(objectToExport As Variant, _
                         outPutType As AcOutputObjectType, _
                         filename As String, _
                         Optional outputFormat = acFormatXLS)
    ' Construct the filename using the date and time '
    Dim fnamebase As String
    Dim ext As String
    ext = Mid$(filename, InStrRev(filename, "."))
    fnamebase = Left$(filename, Len(filename) - Len(ext)) _
                & Format(Now, "-yyyymmdd-hh\hmm")

    ' check if there is another file with the same name '
    ' append (1), (2) until we find a free slot '
    Dim fname As String
    Dim count As Integer
    fname = fnamebase & ext
    Do While Len(Dir(fname)) > 0
        count = count + 1
        fname = fnamebase & "(" & count & ")" & ext
    Loop

    ' Now we're fairly sure no identical filename exists '
    DoCmd.OutputTo objectType:=outPutType, _
                    ObjectName:=objectToExport, _
                    outputFormat:=outputFormat, _
                    OutputFile:=fname, _
                    Encoding:=vbUnicode

End Sub

You can now export queries and tables:

  • Query to Excel 2007/2010 format (XLXS format only available in Access2007/2010):
    ExportToExcel "qrySupplierList", acOutputQuery, "D:\suppliers.xlsx", acFormatXLSX

  • A table to a Text file:
    ExportToExcel "Suppliers", acOutputTable, "D:\suppliers.txt", acFormatTXT

Now, TransferSpreadSheet is better at transfering tables to Excel, so you may prefer to use that one instead:

Public Sub ExportTableToExcel(tableName As String, _
                              filename As String, _
                              Optional spreadSheetType = acSpreadsheetTypeExcel8)
    ' Construct the filename using the date and time '
    Dim fnamebase As String
    Dim ext As String
    ext = Mid$(filename, InStrRev(filename, "."))
    fnamebase = Left$(filename, Len(filename) - Len(ext)) _
                & Format(Now, "-yyyymmdd-hh\hmm")

    ' check if there is another file with the same name '
    ' append (1), (2) until we find a free slot '
    Dim fname As String
    Dim count As Integer
    fname = fnamebase & ext
    Do While Len(Dir(fname)) > 0
        count = count + 1
        fname = fnamebase & "(" & count & ")" & ext
    Loop

    ' Now we're fairly sure no identical filename exists '
    DoCmd.TransferSpreadsheet TransferType:=acExport, _
                              spreadSheetType:=spreadSheetType, _
                              tableName:=tableName, _
                              filename:=fname, _
                              HasFieldNames:=True
End Sub

An you use it like this:

  • To export a Table to an Excel97 format:
    ExportTableToExcel "Supplier", "D:\Suppliers.xlx"

  • To export it to XLSX format (from Access 2007/2010 only): ExportTableToExcel "Supplier", "D:\Suppliers.xlsx", acSpreadsheetTypeExcel12Xml

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

I suggest you get away from using macros as soon as possible. They are not robust (no error handling) and not manageable (how do you figure out where a macro is supposed to be used?).

DoCmd.OutputTo or one of the DoCmd.TransferXXX commands is going to be much eaiser to deal with in the long run.

Upvotes: 1

BIBD
BIBD

Reputation: 15384

You can do something like this, but it's cludgy and not very robust.

DoCmd.SetWarnings False
'do stuff
DoCmd.SetWarnings true

What you could also do is first see if the file is there, and then if it is, delete it (of course this destroys any special file permissions you had set on it).

If Dir(strPath) <> "" Then
    Kill (strPath) 'Delete (strPath)
End If
DoCmd.TransferSpreadsheet acExport, _
                            acSpreadsheetTypeExcel8, _
                            "MyTableQueryName", _
                            strPath, _
                            True

Upvotes: 3

pjp
pjp

Reputation: 17639

Are you using DoCmd.OutputTo?

 DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, "c:\temp\test.xls"

This doesn't appear to prompt to overwrite existing files.

Upvotes: 4

Gary McGill
Gary McGill

Reputation: 27526

Not sure about Access, but most other Office applications have a DisplayAlerts property or something similar, so you can do Application.DisplayAlerts = False before doing operations that normally cause a dialog to appear.

Upvotes: 0

Neil Barnwell
Neil Barnwell

Reputation: 42125

Have another macro that runs first and deletes the file if it already exists.

Upvotes: 2

Related Questions