Solved

Close an Excel Spreadsheet with VBA

Posted on 2013-12-23
12
518 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

11 Experts available now in Live!

Get 1:1 Help Now