Solved

Close an Excel Spreadsheet with VBA

Posted on 2013-12-23
12
521 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

776 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