[Harry32]

Can anyone tell how to write a macro for Excel 2010 that will do the following?

After performing some modifications to a currently open Excel workbook, I want to:

• Save it to a particular folder on a backup drive
• Return to the original document and save it to its original location
• Close the original document
• Display the "Open" window for selection of the next desired document
• End the macro

I have an old macro that performed these functions for many years, but it doesn't work properly in Excel 2010 on a Win 7 computer. I've been unsuccessful in trying to record a macro to do the above, but if I knew the necessary code wording, I could write the macro from scratch using the VBA editor that's provided with Excel 2010.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Celeron(R) CPU E3400 @ 2.60GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2013 Mb
Graphics Card: Intel(R) G41 Express Chipset, 782 Mb
Hard Drives: C: Total - 465551 MB, Free - 392139 MB; D: Total - 11285 MB, Free - 1058 MB;
Motherboard: FOXCONN, 2A8C
Antivirus: Norton Internet Security, Updated and Enabled

[Keebellah]

Hi harry,
The Old macro should work just as well with new versions of Excel.
The only thinbg you'll have to check is if it saves it as xls and ingnores the new Excel format,

xlsx non macro, xslm with a macro.

I advice you to take a look at this oink which might help you
http://www.rondebruin.nl/saveas.htm

If you're still stuk could you post that macro that takes care of the save and backup?

[Keebellah]

I received an answer with some rows of text whichshould be your macro.
The answer is not here on the site but could you post the actual macro asyou run it?
Copy it to a new file of at least copy the code as it is.
I can't get it from the body text as you posted it

[Harry32]

Thanks for the link to Ron de Bruin's tips on Excel save-as macros. I've printed it out and will try to understand it, which may require a little effort, because I've never written any VBA code.

You suggested I post my old macro; here it is. I named it xlbackup.xlm and execute it with a Ctrl+s hotkey combination. It exists as an Excel spreadsheet (hence the row & column reference 'B3' in the 3rd line of code):

=DIRECTORY()
Identifies current directory (in this case, C:\XLFILES, as text for later use.
=SAVE.AS("g:\xlfiles\"&GET.DOCUMENT(1))
Saves active file in backup directory G\XLFILES. GET.DOCUMENT(1) inserts filename of active file.
=SAVE.AS(B3&"\"&GET.DOCUMENT(1))
Saves active file in original directory (which was identified in first macro command).
=CLOSE()
Closes active file.
=OPEN?()
Displays FILE OPEN dialog box for original directory (C:\XLFILES) in preparation for loading next spreadsheet.
=RETURN()
Ends macro.

This was written for me by an IT tech at work more than 20 years ago when we were still using Excel 4.0. It has continued to work normally with subsequent Excel & Windows versions, even after upgrading to Excel 2010 and Windows 7, but only with files transferred from my old Win '93 computer. It won't work with newly-created files, regardless of whether they are saved as .xls or .xlsx.

[Keebellah]

Well now I understand the =????

Weel this is a macro which you should convert to 'normal' vba.

Now the questions, I can probably write the vba macor for you but this xlbackup.xlsm (as it should be named) is this just the controlling sheet?

I mean you want to backup the active file, right?
So this macro should always be available?
The Ctrl+s combination is the Execl keycombination to Save a file, so it will probably not work becasue it just saves the file and will not trigger the macro, but I'm not that acquanteid with the 'old' Execl 2004 macro's, I never used macro's then.
1. The macro has to be available to be applied to any opne workbook?
2. Shortcut key has to be changed, but thta's noproblem
3. Do you need to be promted for the the foldernames or will these always be the same?
4. If you indicate a foleder will you want a dialog prompting for a next file?

[Keebellah]

I think I got what you want.
The VBAproject contains two extra modules which must remain there since they contain supoorting functions for it to work.
It looks quite complicated but it's simple really.
I just applied found code and create the necessary dialogs to verify.
I hope this is what you expected.
As I could make up from your macro was that all you need is copy a file from location A to location B.

I named the file xlBackup.xlsm as you already did too and the Ctrl + s seems to work without a hitch, but the button on Sheet1 does the same.

Let me know.

[Keebellah]

Just and update. Made some small changes like asking if you want to copy all the files in the folder or be prompted one-by-one.
Furthermore added mode feedback to the dialogs.
Tested it here under 2003 and 2010 and it all works.

[Harry32]

Hans, I'll reply to your last 3 in sequence, startng with your 5 Feb post:

Well now I understand the =????

Weel this is a macro which you should convert to 'normal' vba.

Now the questions, I can probably write the vba macor for you but this xlbackup.xlsm (as it should be named) is this just the controlling sheet?
I'm not familiar with the term 'controlling sheet'. What I have is a spreadsheet displaying the 6 lines of code shown in my 5 Feb post. Filename of the spreadsheet is xlbackup.xlm. It was transferred from my old computer along with all my other files and runs on Word 2010 in compatibility mode.
I mean you want to backup the active file, right? Yes.
So this macro should always be available? Yes. It opens each time I launch Excel.
The Ctrl+s combination is the Execl keycombination to Save a file, so it will probably not work becasue it just saves the file and will not trigger the macro, but I'm not that acquanteid with the 'old' Execl 2004 macro's, I never used macro's then. It does work.
1. The macro has to be available to be applied to any opne workbook? Yes.
2. Shortcut key has to be changed, but thta's noproblem. The existing shortcut key works fine.
3. Do you need to be promted for the the foldernames or will these always be the same? The active folder is identified in the first macro instruction; the backup folder is identified in the second macro instruction (see my 5 Feb post).
4. If you indicate a foleder will you want a dialog prompting for a next file? Yes, that's the fifth macro instruction (see my 5 Feb post).

[Keebellah]

Okay, I see you're really not familiar with macro's.

The original sheet you posted is an 'old style' macro which was stored in a Macro Sheet.
It's still possible to use these but VBA (Visual Basic for Applications) is the new standard

The controlling sheet or file is the one where the VBA code is stored in. You could even save it as an addin, but then you should remove the button.
The macro is then always available under Ctrl + s but the Ctrl + s as save function for Excel will be overruled.

What my version does is quite simple.
You work on all the sheets you need to edit and save.
If you open the xlBackup.xlsm sheet you can press the button, indicate the Source folder, alls the files in tha folder can be listed and you can either (backup/copy) all of thes to your backup folder.
Since you have to indicate the source and target folder you are no londer denpendant of a hard coded value an you can selct the folders on the fly.
The existing shortcut works fine, I mentioned that too.

The only difference is that you do not have to opne the execl sheet you want to backup/copy

See if this makes sense to you.
No problem to change things.

[Harry32]

Hans, in reply to your two Feb 6 posts –

I think I got what you want.
The VBAproject contains two extra modules which must remain there since they contain supoorting functions for it to work.
It looks quite complicated but it's simple really.
I just applied found code and create the necessary dialogs to verify.
I hope this is what you expected.
As I could make up from your macro was that all you need is copy a file from location A to location B.
Not quite. See my original post of 30 Jan. I want to:
1. Save the active file to a particular folder on a backup drive
2. Return to the active file and save it to its original location
3. Close the active file
4. Display the "Open" window for selection of the next desired file
5. End the macro

I named the file xlBackup.xlsm as you already did too
No, although I tried this by saving my existing macro (XLbackup.xlm) as XLbackup.xlsm, but it wouldn't run.
and the Ctrl + s seems to work without a hitch, but the button on Sheet1 does the same.

Just an update. Made some small changes like asking if you want to copy all the files in the folder or be prompted one-by-one. No! only the active file. Furthermore added mode feedback to the dialogs. Tested it here under 2003 and 2010 and it all works.

I don't want a macro for general backup purposes, only a simple one to perform the 5 steps shown above, one file at a time. My existing macro does this with only 6 lines of code. I want a VBA version that does the same with a single button click or hotkey command (currently Ctrl+s). I will use this macro each time I finish modifying one of my Excel files, not multiple files.

[Keebellah]

Okay, here's an Excel addin. It's in the zip file becasue an xlam file is not allowed.
Place it in the addins folder or just double click to open.
the sheet will remain blank.
just open your first file in C:\XLFILES, edit it and strike Ctrl+s
The macro will be started (if recognized) and the file is saved in G:\XLFILES and then the dialog is shown to open a next file
If you need to edit the code, opne the VBA project and edit the VBA code for the xlBackup.xlam

This is then the simple version the same as the original oldstyle macro.

Sorry for trying something diffrent.
Happy coding

[Keebellah]

Well it's almost 12 days since my last answer, any acknowledgement that it works?
If so just use the Mark Solved button so we don't have to check again, and, you're welcome.

[Harry32]

Hans –

I apologize for the long delay in responding to your last post. I am grateful for the help you have provided to date, and I want to wrap this up soon.

The macro you sent me on 7 Feb (xlbackup.xlsm) works fine with either hot key – Ctrl+s backs up to the \XLfiles folder and Ctrl+f backs up to the \XLfiles\Finance folder, but only with files transferred from my old Win '93 computer. It won't work with newly created files regardless of whether they are saved as .xls or .xlsx. In other words, it does nothing different than my original Excel 4.0 macro.

To look at existing macros, I clicked the Macros button on the Developer tab, which produces a macro dialog box listing the following:

XLBACKUP.xlsm!chart.backup_q
chart.backup_q
XLBACKUP.xlsm!invst.backup_f
invst.backup_f
XLBACKUP.xlsm!xlbackup
xlbackup

Selecting one of these and clicking Edit to examine it further does not open the VBA Editor, but merely closes the dialog box.

After experiencing the above, I then unhid Personal.xlsb and again hit the Macros button. This time the Macro dialog box showed:

XLBACKUP.xlsm!chart.backup_q
XLBACKUP.xlsm!invst.backup_f
XLBACKUP.xlsm!xlbackup

Selecting each of these and clicking Edit produced different results. For the first one listed I got an error message saying "Reference is not valid." For each of the other two, I was returned to the underlying spreadsheet containing the Excel 4.0 macro instructions.

Besides getting the macro to work with newly created files, I would like it to perform backups to one or more additional folders, e.g., Ctrl+t for backup to \XLfiles\Taxes. The spreadsheet on which macro instructions are written for Ctrl+s and Ctrl+f already contains instructions for Ctrl+t but that hot key does not work. I don't know why.

If I could send you a copy of the Excel 4.0 macro sheet, it might be helpful. However, I know of no way to add it as an attachment to this post, and of course I don't know your email address.

Still struggling –
George

[Keebellah]

No problem, but also no promises
To attach a file (there is a size limit, if it exceeds this you can first ZIP or RAR it)

When repplying press the Go Advanced button
and then if you scroll down you will see a button Manage Attachments ... zelf explanatory from there on.

[Keebellah]

BTW the way you've got xlsx and xlsm check the macro not only takes xlsx files into account, probably need to change the filter to xls* for select any type of Excel file