How to copy a file to a different folder when closing it?

[rakard]

I need to write a macro that copies a specific file to another folder every time I close the file (or every time I save the file).

Can it be done? The triggers doesn't offer much options...

Thanx in advance.

[Bob Hansen]

What program are you using to have the file open? What do mean "close it"?

Are you using the file or editing it? Just viewing it? What it the file type? Are there any security issues about who can read/write/change?

You could just close the file then hava a macro move the file from its current folder to the folder that you want it. The macro could probably close the file for you also, then move it between folders.

[gdyvig]

Hi rakard,


The simplest way is to do as Bob suggests. But it appears you are worried that something may happen to your current folder before you move it to the archive folder. This would make sense if the 2 folders are on separate drives, especially if one of them is at a remote location. I'm assuming by "close it" you mean you will be saving the file one final time before going off to do something else. I am also assuming you want to use your usual applications to update the file without needing Macro Scheduler as a go-between.

You mentioned triggers, so you checked on the FileChanged OnEvent but found it will only respond once a day at the most. This means you will need to create a monitor script to check whether the file has been modified since last checked. This untested code should do it:

Code: Select all

//Make sure you overwrite the existing archive file
Let>CF_OVERWRITE to 1
Let>filename=c:\temp\myfile.doc
Let>archivefile=f:\archive\myfile.doc
//How often do you want to check the file in seconds
//Should be 1 or more seconds
Let>interval=1
//Set up a forever loop
Let>forever=0

Repeat>forever
  //do not increment the forever counter
  //Get file modification time to nearest second.
  FileTime>%filename%,FileModTime
  if>%FileModTime%=%OldModTime%
     wait>%interval%
  else
     FileTime>%archivefile%,OldArchiveModTime
     copyfile>%filename%,%archivefile%
     //copyfile does not have documented error handling
     FileTime>%archivefile%,ArchiveModTime
     if>%ArchiveModTime%=%OldModTime%
        MDL>Unable to copy to %archivefile%, aborting
        Let>forever=%forever%+1
     endif
     Let>OldModTime=%FileModTime%
     wait>%interval%
  endif
Until>forever=1

If you want to keep a history of file changes you could append the date and FileModicationTime to its name in the archive folder.

If you have Macro Scheduler Pro you could compile this script so it can run in the background while you have other Macro Scheduler scripts doing other things.

Are you going to be doing this to lots of files and don't want to have to tell the macro which ones you are working on? If so you may need a more generalized solution. Search the forums for the word "archive".


Gale

[rakard]

Thank you Bob an Gale for your time.

I should confess I'm extremely newbe, so most of your kind and dedicated responses are still difficult for me to understand. I'm learning to use the "wizard" for programing macros rather than writing directly the macros.

Answering to some of your questions:

1. You're right Gale, I'm working on some big excel files (40 MB+ each) located in a folder on a network drive (in this case "U:"). My problem is that the network is very unstable (causing the files to hang or crash several times), so I wanted to initially copy those files to my notebook hard drive (C:) and work locally on the files. Then, every time I saved the files (or close+save) I'd like the macro to automatically overwrite that same file on U: so as to be sure the network and local drives have the updated file.
In the end I´m thinking it as working on a network but without the stability issues of the network.

2. Yes I need it to work for several and different files, but it's not important to have one solution for all files. I was planning to do it considering a folder in C: that overwrited the same folder in U:, or in the worst case enabling one macro for each file...

3. I mentioned triggers because I only saw 2 options regarding files: "File Exists" and "File Doesn't Exist". Something similar happens with folder triggers. I didn't find a "File Saves" trigger or anything like that, but since I'm newbe, I probably missed something on the way.

Thanks again for your time.

[rakard]

Great Gale! Tried your script and replaced file locations and names. It works fine.

Now, if it doesn't take much time for you, how could I add the date to the name of the file? Say:

file on C:
"excel.xlsx"

file copied on U:
"excel (29/07/09).xlsx"

Could the macro copy the file (with the date in its name) and after copying it without problem, erase the previous version one so as to only have the latest backup?

Thanx again
(and I promise to read the beginners guide after this)

[gdyvig]

Hi rakard,

It looks like my solution should work very well in your case. You could expand the macro to check for a list of files and maybe a convenient way to indicate which ones you are working on.

Here is another option:

If you always use the keypad to save and close files (while leaving Excel open) you can use the OnEvent KEY_DOWN event type for Alt+S or Ctrl+S (Save) and Alt+X for close. The OnEvent handler will respond when you press these keys for other applications so you could optionally test in the OnEvent subroutine that Excel is the active window.

As for the triggers for files, no you did not miss anything. What you want would make a nice new trigger for an enhancement.

Because you are having network problems, the OnEvent subroutine may fail to copy the files to the U drive occasionally, so be sure to display an error message so you will know to try again.

My original solution stops the macro when the copyfile fails. Another alternative is to take out exit code and let it keep trying.


Gale

[gdyvig]

Hi rakard,

You asked:

Now, if it doesn't take much time for you, how could I add the date to the name of the file? Say:

file on C:
"excel.xlsx"

file copied on U:
"excel (29/07/09).xlsx"

Could the macro copy the file (with the date in its name) and after copying it without problem, erase the previous version one so as to only have the latest backup?

I don't like putting slashes and extra dots in a filename because it makes it difficult to parse, so I will make the name a little different.

Creating the dated filename is simple enough:

Code: Select all

day>dd
month>mm
year>yyyy

copyfile>C:\excel.xlsx,U:\excel(%dd%_%mm%_%yyyy%).xlsx

Deleting the old file is a little more difficult if you have not worked on it in a few days. You need a way of finding or keeping track of a file whose name occasionally changes. Finding the file requires getting a directory file list and picking from it. I think keeping track of it with an ini file would be simpler. You could do something like this:

Code: Select all

/*Ini file looks like this:
[c:\excel.xlsx]
saved=29_07_2009
*/

day>dd
month>mm
year>yyyy
Let>curdate=%dd%_%mm%_%yyyy%
//do the copyfile
//read the ini file to get old date
ReadIniFile>c:\myfiles.ini,c:\excel.xlsx,saved,fdate
if>%fdate%=%curdate%
  //overwrote existing file
else
  //delete the oldfile
  deletefile>U:\excel(%fdate%).xlsx
  //update the ini file
  EditIniFile>c:\myfiles.ini,c:excel.xlsx,saved,%curdate%
endif

Gale

[rakard]

Thanx again!
I'll try that and be in contact.

Sincerely,
rakard
PD: I hope I have enough reputation points to give you!