Need to call a batch file when a workbook is opened

Hi all

I want to have a batch file that runs automatically whenever a particular workbook is opened. So I need help working out what VBA command to use (the Shell command?), and where to put it in Excel.

I could also do with help getting the batch file code working if anyone here knows about that stuff:

echo off
:: set drive=M:\mybackup
SET dateNtime="%date:~0,2%-%date:~3,2%-%date:~6,6%_%time:~0,2%-%time:~3,2%"
set backupcmd=xcopy /s /c /d /e /h /i /r /k /y
%backupcmd% "*.xls" "\Backups\*%dateNtime.xls"

What I want this batch file to do is copy all "xls" files from the current directory to a subdirectory, with the date and time appended on the end, and put them in a "Backups" subdirectory. Then I can copy the batch file to all of the other directories without having to change filenames. Also there is one directory which has five to backup rather than one. Does anyone know how I can get this batch file work?

And does anyone know where I can put the Shell command in Excel to run the batch file when the workbook is opened (and possibly when certain VBA subroutines are ran)?

I tried that code, slightly modified in an attempt to set the path as the current path:

Please Login or Register  to view this content.

But nothing gets copied when I open the workbook. And I tried it before and after manually creating the "Backups" folder (although I'd like to avoid having to create that folder in every other folder anyway if I can avoid it).

To be honest I took the code from online, so I don't know what the various switches do in the batch file. I'd really like the copied files(s) to have the date and time stuck on the end though, rather than just replacing the original filename.

And I don't mind if I use a batch file called from VBA, or just VBA.

how about this:

Please Login or Register  to view this content.

That will create the backups folder if it doesn't already exist. I've tried it on a test folder on my system and it copies everything ok. Have you put it in the thisworkbook object and not in a new module?

/y will overwrite any existing files without prompt
/z puts a tracker on files in case you need to pick back up from where you left off.

The code I left above will call the .bat file from vba.

Thanks XeRo, but I'm starting to think that maybe using VBA would be the better way to go (now that I know that I can do that!).

Your code does work now yudlugar, although I had to change the name of the subroutine to workbook_open() for it to work. Not sure why. One minor nitpick - would it be possible to have a space between the filename and the date, like there is between the date and time? Not that it really matters.

Also, if I wanted to do this backup every time someone ran one of my other subroutines, would I just insert something at the beginning of my subroutine, something like:

"Call workbook_open()"?

And is it better to use "Call" or "Application.Run"?

Would the user still be returned to my subroutine afterwards?

Call workbook_open or just workbook_open, but you'll have to remove the Private from before Sub. If you want to do it that way you should probably put it in a standard module, change the name and call it from your workbook_open event.

I'm assuming the others Subs you're talking about are in the same workbook?

Edit:

You do want it to automatically run every time the workbook is open correct?

See Xero Solus' post regarding running from other modules. To add the space:

Please Login or Register  to view this content.

Ok, sorry to be a pain, but I could do with changing this slightly. The setup I have is this:

I have 12 folders. 11 folders each contain 1 workbook, and 1 folder contains 5 workbooks, totalling 16 workbooks. All of the workbooks contain subroutines which send and receive data to each other. The whole project is about to be tested in work, so I want regular backups of the files during the testing period.

So I originally wanted each workbook to back itself up when opened. But that doesn't back up any other workbooks that have data sent to them.

So then I thought, how about if each workbook had an "on-open" subroutine that went up two parent folders, and then copied (with the date and time added at the end) the entire project folder to a "Backups" folder. This would certainly be a tidier way of doing it. But of course, the "Backups" folder would get bigger and bigger, so it would take longer and longer to open each workbook every time. Unless VBA has some kind of switch to only back up the files that have been modified since the last backup?

I'd rather not insert code into all of my existing subroutines to backup the source and destination files if I can help it!

so something like:

Please Login or Register  to view this content.

would go back two parent folders and copy the entire folder/file structure to a backup with the date

That almost works, although it goes back one two many folders. The process runs from one of the spreadsheets, which is in a folder, so it needs to go out of that folder, then up one more folder (outside the main project folder), and then copy that folder to a backup folder. It seems to do that, but another folder up, so I get all of the versions copied into a backup.

Also, I could ideally do with that "Backup" folder being stored inside the project folder, otherwise it could get messy. But then I suppose I would have to specify all of the folder names in order to stop the process from also copying the "Backup" folder wouldn't I?

Something like this (although it says "invalid qualifier"):

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Interesting. Each time I run it it says "Object does not support this property or method" on the line:

Please Login or Register  to view this content.

But it does create a Backup folder each time (in the project folder). It just doesn't put anything in it.

Sorry my mistake, should be copyfolder.subfolders not copyfolder.folders

I'm not getting any errors, but it's not copying anything either. This is what I have. Incidentally should it not be 4 rather than 5 for the "Mid" command? I assume it's looking for the space after the first three letters? It doesn't copy anything either way anyway though:

Please Login or Register  to view this content.

I put a MsgBox command in the loop, and "fol" equals the full path from start to finish. So doesn't that means it's looking for the 4th (or 5th) character at the beginning of the full path instead of just the folder?

Try
If Strings.Mid(fol.name, 4, 1) = " " Then
?

That should just look at the name not the full path

Something went horribly wrong then...it was copying hundreds of megabytes of data and I had to stop it (which wasn't easy with all the MsgBox's popping up!):

Please Login or Register  to view this content.

Never mind it works now. Do you know what could have caused it to go wrong before? I did have another Excel spreadsheet open at the same time. Maybe it took ActiveWorkbook.Path from there or something, which took me to My Documents? Because if that was the case, then I'd have to be careful that the same thing didn't happen in work.

Ok I've tried this on a similar folder set-up to what you described and it works ok for me. I'm pretty much out of ideas after this so might be worth going back to the batch method

Please Login or Register  to view this content.

You could try referencing ThisWorkbook.path rather than activeworkbook.path which should give you the workbook the code is stored in not the active one. Although if you are running the code on the open event they should be the same.

Note, the last code I posted in the previous code will also recreate the other folders/structure in the backup folder.

Excellent! And you read my mind, I was just about to ask how I could retain the folder structure.

So if they are probably going to be the same anyway (with the code being in ThisWorkbook), would you suggest I go for Thisworkbook.path or activeworkbook.path?

Assuming you are only going to be running the macro on the workbook which the macro is stored in, I would use thisworkbook.path

Ok thanks. Just one last thing (which I tried to do myself, but couldn't get it to work) - how can I modify the code so that all the date and time-stamped "Backup" folders go inside one "Backups" folder in the project folder? I tried this, but it didn't work:

Please Login or Register  to view this content.

You need to create the destination folder before trying to copy something to it. Assuming you have already made "\backups" then maybe you can use:

Please Login or Register  to view this content.

Great, that works perfectly!

Thanks for all your help, much appreciated!

Never mind, I created an error handling routine. Thanks again everyone.