asked on
Macro to delete and replace Excel file
Hi guys,
I have an update button on a userform. When the user clicks this button it opens another workbook called update.xlsm. Once the update.xlsm opens, I have a macro in that workbook that closes the original workbook. This works great. Now I am trying to figure out, how I can add to the macro to delete the previous closed workbook, and replace that file with a fresh copy of the workbook stored on our network drive. Once the file is copied I want it to open and then close the update.xlsm workbook.
I am not sure how the macro will determine where the file to be deleted is located. Users save the file to any location they choose. When they click update, that file should be replaced regardless of location.
Any help on this will be greatly appreciated.
I have an update button on a userform. When the user clicks this button it opens another workbook called update.xlsm. Once the update.xlsm opens, I have a macro in that workbook that closes the original workbook. This works great. Now I am trying to figure out, how I can add to the macro to delete the previous closed workbook, and replace that file with a fresh copy of the workbook stored on our network drive. Once the file is copied I want it to open and then close the update.xlsm workbook.
I am not sure how the macro will determine where the file to be deleted is located. Users save the file to any location they choose. When they click update, that file should be replaced regardless of location.
Any help on this will be greatly appreciated.
Microsoft ExcelVisual Basic Classic
Last Comment
ScriptAddict
ASKER
Thank you for respond. Since I am new to this stuff, I wont know where to begin. Can you explain how this would work?
Sure, Since when you open a document in excel it jumps to the top of your recent list. You could use something like :
Application.RecentFiles.It
or if it starts at 0 then
Application.RecentFiles.It
If you wanted it to be a little more reliable you could search the list, comparing the path's to the file name you are looking for using something like instr()
http://www.techonthenet.com/excel/formulas/instr.php
then select that path and use it to do whatever you wish to the original workbook.
-SA
Application.RecentFiles.It
or if it starts at 0 then
Application.RecentFiles.It
If you wanted it to be a little more reliable you could search the list, comparing the path's to the file name you are looking for using something like instr()
http://www.techonthenet.com/excel/formulas/instr.php
then select that path and use it to do whatever you wish to the original workbook.
-SA
ASKER
Certainly over my head, but will play around a bit. Thanks for you guideance.
ASKER
I am really new to the vba thing, but does this make any sense at all? I would like to use the instr() like you mentioned, but like I said, really over my head.
Sub Update()
Dim fs
Dim filepath As String
' close Loan workbook.
ActiveWorkbook.Close SaveChanges:=False
' locate file path
Application.RecentFiles.Item(0).Path = filepath
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile = filepath
End Sub
Is the first workbook still open when you run the second code?
If it is you can grab it's full path/name with it's FullName property.
If it is you can grab it's full path/name with it's FullName property.
Sub CloseAndDelete()
Dim strFileAndPath As String
strFileAndPath = Workbooks("NameOfFirstWorkbook.xlsm").FullName
Workbooks("NameOfFirstWorkbook.xlsm").Close
Kill strFileAndPath
End Sub
pretty much, but that looks very dangerous.
I'd use the Application.RecentFiles.co
something like:
This should only delete the most recently accessed file that included NameOfWorkbookToDelete in the path and/or filename.
I'd use the Application.RecentFiles.co
something like:
dim x as integer
dim MostRecentFiletoDelete as string
x = Application.RecentFiles.Count
do until x = 0
If IsError(instr(applicaiton.recentfiles.item(x).path,"NameOfWorkbookToDelete")) Then
x=x-1
loop
else
MostRecentFiletoDelete = application.recentfiles.item(x).path
x=x-1
End if
loop
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile = MostRecentFiletoDelete
This should only delete the most recently accessed file that included NameOfWorkbookToDelete in the path and/or filename.
ASKER
No, the first workbook is already closed. The second workbook closes the first.
You might also want to do some kind of error trapping in case they named their file something different and so that name doesn't appear in the list of recent files at all.
Otherwise this would throw an error.
-SA
Otherwise this would throw an error.
-SA
At some point both workbooks must be open at the same time.
So you could just use the suggestion I made then to get the name of the file to delete before you close it.
So you could just use the suggestion I made then to get the name of the file to delete before you close it.
ASKER
Is the code missing a "Do" I get an error when I tried to run code as is. Do without Loop.
Thanks for the suggestion about the error trapping. Is there any reason it will ever delete the wrong file? Just want to make sure the macro never deletes any other user file. They would kill me. LOL
Thanks for the suggestion about the error trapping. Is there any reason it will ever delete the wrong file? Just want to make sure the macro never deletes any other user file. They would kill me. LOL
If the code you posted earlier is what you have in the 2nd workbook try this.
It gets the path/filename of the workbook you want to delete just before you close it.
It gets the path/filename of the workbook you want to delete just before you close it.
Sub Update()
Dim filepath As String
' get path and filename of workbook you are just about to close
filepath = ActiveWorkbook.FullName
' close Loan workbook.
ActiveWorkbook.Close SaveChanges:=False
' delete the workbook
Kill filepath
End Sub
ASKER
ScriptAddict: I used your code, but nothing happen. I removed "Loop" from part of the code, not sure if I was supposed too.
imnorie... I tried your code also, and nothing happens. File was not deleted.
imnorie... I tried your code also, and nothing happens. File was not deleted.
What exactly happened when you ran it?
Did you have both workbooks open then one of them was closed and nothing else?
Which workbook if any closed?
I think the problem might which workbook is active and using ActiveWorkbook.
I tried the code with the first workbook active and it was closed and deleted.
Then I tried it with the second workbook active and it was the workbook that closed.
No workbook was deleted because as soon as the second workbook closed the code stopped and the line (Kill) to delete the file was never reached.
The same thing will happen with ScriptAddict's code.
Did you have both workbooks open then one of them was closed and nothing else?
Which workbook if any closed?
I think the problem might which workbook is active and using ActiveWorkbook.
I tried the code with the first workbook active and it was closed and deleted.
Then I tried it with the second workbook active and it was the workbook that closed.
No workbook was deleted because as soon as the second workbook closed the code stopped and the line (Kill) to delete the file was never reached.
The same thing will happen with ScriptAddict's code.
ASKER
I have the first workbook open. From that workbook, I click the command button which open workbook 2 and automatically runs the macro. Workbook2 opens then immediately closes workbook1. Nothing else happens.
To quote from the question:
The macro that closes the workbook must do something similar to imnorie's code for that step, perhaps one of:
Workbooks(1).Close
Workbook("[name of workbook").Close
Incorporating inmorie's suggested code to obtain the path from the workbook before closing it seems a sensible idea.
Could you perhaps post the macro from update.xlsm?
Another thought is to have the "userfile.xlsm" write it's full path/filename to a local text file of a standard known name and location and then have the update.xlsm open that file to determine which file to delete and replace. This feels like overcooking things though.
Once the update.xlsm opens, I have a macro in that workbook that closes the original workbook.
The macro that closes the workbook must do something similar to imnorie's code for that step, perhaps one of:
Workbooks(1).Close
Workbook("[name of workbook").Close
Incorporating inmorie's suggested code to obtain the path from the workbook before closing it seems a sensible idea.
Could you perhaps post the macro from update.xlsm?
Another thought is to have the "userfile.xlsm" write it's full path/filename to a local text file of a standard known name and location and then have the update.xlsm open that file to determine which file to delete and replace. This feels like overcooking things though.
ASKER
Here is the Macro in the Update.xlsm. I have tried both suggestions. Thanks for your help on this. After the file is deleted I want to copy the new file to that location and open that file.
Sub Update()
Dim x As Integer
Dim MostRecentFiletoDelete As String
Dim fs
' close Loan workbook.
ActiveWorkbook.Close SaveChanges:=False
' deletes old copy of LoansTracking.xlsm
x = Application.RecentFiles.Count
Do Until x = 0
If IsError(InStr(applicaiton.RecentFiles.Item(x).Path, "LoanStatsTracking.xlsm")) Then
x = x - 1
Else
MostRecentFiletoDelete = Application.RecentFiles.Item(x).Path
x = x - 1
End If
Loop
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile = MostRecentFiletoDelete
Debug.Print
End Sub
'Sub Update()
'Dim filepath As String
'
'' get path and filename of workbook you are just about to close
'filepath = ActiveWorkbook.FullName
'
'' close Loan workbook.
'ActiveWorkbook.Close SaveChanges:=False
'
'' delete the workbook
'
'Kill filepath
'End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This work perfectly......Thanks....N
Ahhh sorry I didn't get back to this, I had to go to an offsite meeting. You're right I had two kinda obvious errors:
the extra loop and the misspelled application:
I think you'll find this works.
the extra loop and the misspelled application:
I think you'll find this works.
dim x as integer
dim MostRecentFiletoDelete as string
x = Application.RecentFiles.Count
do until x = 0
If instr(Application.recentfiles.item(x).path,"LoanStatsTracking.xlsm") > 0 Then
MostRecentFiletoDelete = application.recentfiles.item(x).path
x=x-1
else
x=x-1
End if
loop
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile = MostRecentFiletoDelete
Oh one last thing you may want to do, when testing code that deletes files, I always replace the delete with a msgbox command until I'm done testing.
That way you don't lose anything you didn't mean to :)
That way you don't lose anything you didn't mean to :)
ScriptAddict
There's no need to use the list of recent files or FSO.
The file you are going to close is open when you start the code.
So you can get the path and filename directly using it's FullName property before you close it.
There's no need to use the list of recent files or FSO.
The file you are going to close is open when you start the code.
So you can get the path and filename directly using it's FullName property before you close it.
It seems the higher level you get in Excel, the more ways you can do the same thing :).
It's funny the other day someone said to me even Excel has limitations. He went on to say "You can't make a cell highlight green by pressing the down arrow".
I couldn't help but think:
Yes, I can.
It's funny the other day someone said to me even Excel has limitations. He went on to say "You can't make a cell highlight green by pressing the down arrow".
I couldn't help but think:
Private Sub Workbook_Open()
Application.OnKey "{Down}", "GreenFill"
End Sub
Module:
Sub GreenFill()
'
' GreenFill Macro
'
' Keyboard Shortcut: Ctrl+g
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Yes, I can.
ScriptAddict
I don't what you mean.
If I offended you somehow I apologise.
I don't what you mean.
If I offended you somehow I apologise.
Nah, just saying that both solutions, and even the one ultimately selected by the original poster all work.
Check this out:
http://msdn.microsoft.com/en-us/library/ff197180.aspx