Deleting existing excel on C using VBA

Summer123

Registered User.
Local time
Yesterday, 21:37
Joined
Feb 9, 2011
Messages
216
Hi, want to know if there is a way to delete an existing file in excel that i export out of my access DB? What i am trying to do is, export many queries to excel worksheets in one excel workbook (i have this part working fine and its creating a spreadsheet and formatting it and putting it out on the C drive) and then when the user is done reviewing the spreadsheet and hits the "X" button (or exits excel) on the spreadsheet it would close excel and delete that file on the c drive. Is this doable? is there a code i can use?

thank you,
Summer
 
Code:
Dim strFile
 
strFile = "C:\SomeFolder\TheFile.xls"
 
If Dir(strFile) <> vbNullString Then
   Kill(strFile )
End If
 
Thank you Boblarson. however its not deleting the excel file.. here is my code

Code:
[COLOR=seagreen]...       [/COLOR]
         If Not rst.EOF Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
            End If
            rst.Close
            Set rst = Nothing
        End If
    End Select
Next ctl
strFileName = Dir("C:\SCF Errors.xls")
If strFileName = "SCF Errors.xls" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn)
    intCountofSheets = xlBook.sheets.Count
    intCurrentSheet = 1
    Do While intCurrentSheet <= intCountofSheets
    ... [COLOR=seagreen]'format the spreadsheet[/COLOR]
    intCurrentSheet = intCurrentSheet + 1
    Loop
    xlBook.Worksheets(1).Activate 'Returns the selection to the first worksheet.
    xlApp.Visible = True
    xlBook.Save
    'xlBook.Close
    'xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing
Else
    MsgBox "No error reports to export"
End If
[COLOR=green]'is this where you place the code?
[/COLOR][COLOR=red]If Dir(strFileName) <> vbNullString Then
    Kill (strFileName)
[/COLOR]End If
 
Now that you show the code, I don't know that it is possible to do what you want. The problem is that one - You can't delete a file which is open and it appears that the file would still be open. Also, I don't see any place where someone could view the file and then close it and have it delete.

So, I think you would have to programmatically add a module and procedure to the Excel file so that in its close event it would kick off a batch file that is dynamically created and then delete the file after it has closed. Not an easy process and not one I would really want to attack.
 

Users who are viewing this thread

Back
Top Bottom