Close an Excel Spreadsheet with VBA

I have an access database that manipulates data from a workbook that is downloaded from a website.  I have a button and some code that uploads the data automatically.    Here is the issue:  the format in which the internet provides it seems to be in .xls and it saves it as such but when the user re-opens it or saves it it wants to be a web file.   Because of this access cannot upload it so I have copied some VBA that opens the saved file, re-saves it as a CSV which then allows me to successfully uploads it.  Once that process is done, I have the program delete the newly created CSV as well as the original file.  

However, when the user goes to download the NEXT bit of data, the OLD, presumed deleted CSV file (with the same name) is opened and without the user having to go thru a bunch of steps I dont want them to - the old one saves as the new one.  

Any thoughts on how to kill it after its deleted?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Workbooks("BOOK1.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=FALSE
Dale FyeOwner, Dev-Soln LLCCommented:
are you closing the workbook after you do the save as csv?

Do you have your error handler enabled in the code segment that is supposedly killing the old .xls and .csv files?  if so, it should tell you that the deletion failed and provide a reason.
NUdovich2Author Commented:
Upon further investigation it appears that this is the file that keeps opening up upon subsequent downloads:


How do I delete the temp file once it is created and used.

no, no error handler.  I'm stealing code from the internet and modifying it to meet my needs, I'm actually not a programmer in the slightest.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dale FyeOwner, Dev-Soln LLCCommented:
What version of Access are you using (just wondering)?

There may be an issue with user permissions on the \App Data\Local\Temp\path
It may be locked down so that users cannot delete from the path.

Can you manually delete the file from that folder in Windows Explorer?
NUdovich2Author Commented:
Access 2010

Yep, inside the folder I can delete it.
I think I'm just missing the code to delete that file.
Dale FyeOwner, Dev-Soln LLCCommented:
It may have to do with the "." in the folder name.  I've had problems in the past dealing with filenames that contained more than one period.

What code are you using to delete the file?  Normally, you would use the

KILL statement.  Can you execute that command from the immediate window in Access?

KILL "C:\Users\xxx\AppData\Local\Temp\rad9249B.tmp\DrugReimbursement.csv"

If the file has not been closed prior to the deletion, then the delete probably will not work.

There may be another method available with the FileSystemObject to delete the file if that doesn't work.

You might want to add some error handling to your routine.
Private Sub whatever

    On Error Goto ProcError                                               '<=  insert this line

   'your code goes here

'Insert everything from here down.

    'might need to put more code here to elegantly close the subroutine
    Exit Sub

    msgbox err.number & vbcrlf & err.Description, vbOkOnly, "Error in subroutine Whatever"
    debug.print "Error in subroutine Whatever", err.number, err.description
    Resume ProcExit

End Sub

Open in new window

The key to adding error handling is to put a line at the top of your code module telling the code where to go if it encounters an error.  Then adding a way to elegantly exit the subroutine (ProcExit) which must occur before ProcError and must include the line "Exit Sub" (or function) or your code will proceed through the error handler every time it runs through the subroutine.  Then, in your error handler, you could use if/Then statements to test for specific errors and perform some form of error handling.  What I provided above is the simplest version and only displays a message and then writes the error to the immediate window.  The last line (Resume ProcExit) would not be necessary in this instance, but if you needed to elegantly close recordsets or something else in the ProcExit, then using that to elegantly exit the subroutine would be a good thing.  There are a variety of options for the  Resume statement, look that up in the Access Help for more detail.
Use the old-school Kill command:  Kill strFilename

First, add reference to Microsoft Scripting Runtime (Tools menu -> References) and then write a subroutine like this.  Maybe pass the filename as an argument?
Public Sub RemoveIt()
    Dim strFilename As String
    Dim fso As New Scripting.FileSystemObject
    Dim strTempFolderName As String
    ' Suggest not hard coding the temporary file folder name
    strTempFolderName = fso.GetSpecialFolder(TemporaryFolder)
    strFilename = strTempFolderName & "\" & "rad9249B.tmp\DrugReimbursement.csv"
    If fso.FileExists(strFilename) Then
        fso.DeleteFile strFilename, True
        ' Suggest you log somewhere the fact that you couldn't delete the file
        ' Since due to regulations you might not want this hanging around
    End If
End Sub

Open in new window

NUdovich2Author Commented:
Yep, so I added a kill statement in the code and that worked.  But I had to add a general kill statement:

Dim sKillExcel As String

sKillExcel = "TASKKILL /F /IM Excel.exe"
Shell sKillExcel, vbHide

Each time the code generates a temp file it goes to a different temp folder so I can't always rely on the full path.  Dont know how to reference that variable.
Dale FyeOwner, Dev-Soln LLCCommented:
From your original post: "but when the user re-opens it or saves it it wants to be a web file."

How are your users saving this file when they download it from the web?

How are you doing the conversion process from xls to csv?

Are you using the Excel Workbook.SaveAs method to save the file?  if so, you can save it to a specific path\filename, and file format something like:

Workbook.SaveAs "C:\Users\xxx\AppData\Local\DrugReimbursement.csv", xlCSV

or if you would prefer to save it as an Excel (.xlsx) file:

Workbook.SaveAs "C:\Users\xxx\AppData\Local\DrugReimbursement.csv", xlExcel12

This would allow you to keep track of where the file is going, and you would know where to check if the file exists before trying to save it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Also try the following more "polite" approach.  If it doesn't shut down Excel, then there is something else using its services or there is more than one instance of Excel running.
Sub ShutDownExcel()
    Dim pExcelApplication As Object '  Or, Excel.Application
    On Error Resume Next
    Set pExcelApplication = GetObject(, "Excel.Application")
    If Not pExcelApplication Is Nothing Then
        pExcelApplication.ActiveWorkbook.Close False
        Kill pExcelApplication.GetOpenFilename
    End If
End Sub

Open in new window

Also, browse throught the methods and properties on Excel.Application.  Maybe you will find something helpful.  Do this by first adding a reference to it (Tools -> References dialog, Microsoft Excel xx.x Object Library) then use the Object Browser (press F2) or IntelliSense to help you (another benefit of adding references).
NUdovich2Author Commented:
So, in the end what it all came down to was the last / in the temporary file path

 strTempPath = fso.GetSpecialFolder(TemporaryFolder) & "\" & fso.GetTempName & ""

once removed, I didn't have to employ the kill function
I simply deleted the folder

Thanks to all for the help.  Points to Fyed as he put me down that path.
NUdovich2Author Commented:
The actual solution was not posted by the points winner but they led me to my final conclusion and were otherwise the most helpful.  My final post should lead any further users of this post to see what the real answer was / may be.   I somehow need to draw attention to that for future readers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.