Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Close an Excel Spreadsheet with VBA

Posted on 2013-12-23
Medium Priority
Last Modified: 2013-12-28
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?
Question by:NUdovich2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1

Expert Comment

ID: 39736175
Workbooks("BOOK1.XLS").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=FALSE
LVL 48

Expert Comment

by:Dale Fye
ID: 39736186
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.

Author Comment

ID: 39736277
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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 48

Expert Comment

by:Dale Fye
ID: 39736302
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?

Author Comment

ID: 39736313
Access 2010

Yep, inside the folder I can delete it.
I think I'm just missing the code to delete that file.
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 39736324
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.

Expert Comment

ID: 39736347
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


Author Comment

ID: 39736388
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.
LVL 48

Accepted Solution

Dale Fye earned 2000 total points
ID: 39736427
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.

Expert Comment

ID: 39736483
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).

Assisted Solution

NUdovich2 earned 0 total points
ID: 39736528
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.

Author Closing Comment

ID: 39743294
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.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question