Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export table (Access 2000)

    I've got some buttons set up to export specified tables to Excel files. Only troulbe is, if the file already exists, then the code gets confused.
    What is the appropriate syntax for Access to overwrite the file (possibly with confirmation dialogue box).

    Here's what I've got so far;

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TblAttraction", "Cocuments and SettingsTblAttraction.xls", True, "a1:k50"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export table (Access 2000)

    Normally, TransferSpreadsheet will not overwrite the entire Excel workbook if it already exists, only the worksheet with the same name as the table/query being exported. If you want to overwrite the entire workbook, you could use something like this:

    Dim strWorkbook As String
    strWorkbook = "Cocuments and SettingsTblAttraction.xls"
    If Not (Dir(strWorkbook) = "") Then
    If MsgBox("Workbook already exists. OK to overwrite it?", vbOKCancel + vbQuestion) = vbOK Then
    Kill strWorkbook
    Else
    Exit Sub
    End If
    End If
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TblAttraction", strWorkbook, True, "a1:k50"

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export table (Access 2000)

    Thank you yet again, that does the job nicely.

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •