Solved

Close an Excel Spreadsheet with VBA

Posted on 2013-12-23
12
516 Views
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?
0
Comment
Question by:NUdovich2
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 6

Expert Comment

by:Bxoz
ID: 39736175
Workbooks("BOOK1.XLS").Close SaveChanges:=False
or
ActiveWorkbook.Close SaveChanges:=FALSE
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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.
0
 

Author Comment

by:NUdovich2
ID: 39736277
Upon further investigation it appears that this is the file that keeps opening up upon subsequent downloads:

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

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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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?
0
 

Author Comment

by:NUdovich2
ID: 39736313
Access 2010

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

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 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.

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

ProcError:
    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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:DudMc3
ID: 39736347
Use the old-school Kill command:  Kill strFilename
—or—

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
    Else
        ' 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

0
 

Author Comment

by:NUdovich2
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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.
0
 

Expert Comment

by:DudMc3
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
        pExcelApplication.Quit
    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).
0
 

Assisted Solution

by:NUdovich2
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.
0
 

Author Closing Comment

by:NUdovich2
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now