How do you transfer from one active workbook to another and close the previous active workbook?

Photomofo

Board Regular
Joined
Aug 20, 2012
Messages
237
I have an On Workbook Open event in a workbook that does a check and makes sure the user isn't opening an old version of the program from a non-standard location. If the program sees this happening the program currently puts in a stop.

If the Workbook Path and Name is the correct version it pops up a message box and asks for the User Name.

I'm thinking... Let's say it's an old version... Obviously I can do a msgbox to prompt the user to go get the newest version but is there a way to open the newest version from a known file location and then kill the version that was originally running the code?

Hmmm I think I may have answered my question. Maybe I just need to insert a 5 second delay during the On Workbook Open event. This would provide enough time for the other program to delete before you got the opening message prompt to insert the User Name on the new workbook that just opened. Is there a better way to do this than with a time delay? Hmmm...

Does my question make sense? Does the solution make sense?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Photomofo

Board Regular
Joined
Aug 20, 2012
Messages
237
OK... I solved my problem. The one curveball was that I couldn't open another workbook with the same name. There's probably a better way to do this but this worked.

VBA Code:
ARG = ThisWorkbook.Name
Path = ThisWorkbook.Path & "\"

If Path & ARG <> "C:\ABC\SCHEDULE - 2021.xlsm" Then

    ActiveWorkbook.SaveAs "This message will self-destruct in 5, 4, 3, 2, 1.xlsm"
    Kill Path & ARG

    Path = "C:\ABC\"

Workbooks.Open (Path & "SCHEDULE - 2021.xlsm")

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

Application.DisplayAlerts = True

End If
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,958
Members
416,002
Latest member
t10k14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top