Avatar of KLM56
KLM56
 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.
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
ScriptAddict

8/22/2022 - Mon
ScriptAddict

Have you considered using the recent list?


Check this out:

http://msdn.microsoft.com/en-us/library/ff197180.aspx
KLM56

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?
ScriptAddict

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.Item(1).Path

or if it starts at 0 then
  Application.RecentFiles.Item(0).Path

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
KLM56

ASKER
Certainly over my head, but will play around a bit. Thanks for you guideance.
KLM56

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

Open in new window

Norie

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.

Sub CloseAndDelete()
Dim strFileAndPath As String

    strFileAndPath = Workbooks("NameOfFirstWorkbook.xlsm").FullName

    Workbooks("NameOfFirstWorkbook.xlsm").Close

    Kill strFileAndPath

End Sub

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ScriptAddict

pretty much, but that looks very dangerous.  

I'd use the Application.RecentFiles.count to get a count, and then look through all the item paths

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

Open in new window


This should only delete the most recently accessed file that included NameOfWorkbookToDelete in the path and/or filename.
KLM56

ASKER
No, the first workbook is already closed. The second workbook closes the first.
ScriptAddict

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Norie

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.
KLM56

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
Norie

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.
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 

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KLM56

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.
Norie

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.
KLM56

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
scsyme

To quote from the question:
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.
KLM56

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

Open in new window

ASKER CERTIFIED SOLUTION
scsyme

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
KLM56

ASKER
This work perfectly......Thanks....Now my users can update their workbook with just a couple clicks.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ScriptAddict

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.

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 

Open in new window

ScriptAddict

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 :)
Norie

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ScriptAddict

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:

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

Open in new window


Yes, I can.
Norie

ScriptAddict

I don't what you mean.

If I offended you somehow I apologise.
ScriptAddict

Nah, just saying that both solutions, and even the one ultimately selected by the original poster all work.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.