I'm probably overthinking this, but what I'm looking for is:
I need VBA code to put in the Workbook_Open() event of a blank workbook, which I will call "File Opener.xlsm".
When File Opener.xlsm is opened, I need it to do the following:
1. open another workbook stored on a network drive - "networkfilepath\Master Log.xlsm".
2. save a copy of Master Log.xlsm in a specified folder on the desktop of the local machine, adding a time/date stamp to the file name - "pathoflocalfolder\Master Log4-10-19-1322.xlsm", assuming the file was opened today at 1:22 pm.
3. close the original network copy of Master Log.xlsm without saving any changes (none should have been made, and I believe "SaveAs" will automatically close this file already)
4. open the newly saved and timestamped file, if it's not already open as a result of the SaveAs command (I believe it will be)
5. close File Opener.xlsm without saving any changes
Additionally, I don't want any message prompts to pause and ask for user confirmation, such as "save changes before closing", etc.
Basically, from the user's perspective, clicking the file icon to open "File Opener" from the desktop should feel like directly opening the Master Log file from the network, except the now open file is a locally saved copy and the original file is left closed and unchanged.
I started writing code that created the new file first, and then opened the network file, copied sheets, inserted them to the new file, deleted excess sheets, and so on, but I kept having issues with maintaining correct formatting of the cells and whatnot, and it just seems there should be an easier way. I was also struggling with the formatting of the time/date stamp, and had just accepted using the numeric value of the "Now" function.
Any help would be appreciated... I've been debugging other areas of the code on this project for days, and just went live for company-wide BETA testing today, so I'm a bit fried at the moment.
Thanks in advance!
Bookmarks