Macro to save copy of Personal.xlsb

Question

I want a macro to save backups of my Personal.xlsb -- Why won't this work for me? Workbooks("Personal.xlsb").SaveCopyAs "C:Users\Tom\Documents\Test.xlsb" I get runtime error 1004 saying Excel cannot access the file "C:Users\Tom\Documents\Test.xlsb" -- which I wouldn't think it would need to access but instead to create. (I know I can manually copy Personal.xlsb from one place to another.)

If you have a correction, use the [edit] link below the tags and make that correction. Also, make an effort to properly format your code. You can get help doing that fomratting so by clicking the `?` icon on the right side of the toolbar. — Ken White, Oct 23 '20 at 03:41

score 2 · Answer 1 · answered Oct 30 '20 at 18:19

As provided by my teacher Leila Gharani, the key is: Even using a different name, a copy of the open Personal.xlsb file can only be saved to the xlstart folder. So the code to save a copy of Personal.xlsb in another folder (with User Name substituted for Tom):

Workbooks("Personal.xlsb").SaveCopyAs _
    "C:\Users\Tom\AppData\Roaming\Microsoft\Excel\xlstart\Personal(1).xlsb" _
    'can only save it to this folder

 Name "C:\Users\Tom\AppData\Roaming\Microsoft\Excel\xlstart\Personal(1).xlsb" As _
        "C:\Users\Tom\Documents\Personal-Test.xlsb" _
        'can name as (and thus move to) any folder & name

score 0 · Answer 2 · answered Oct 09 '22 at 09:01

0

I use a seperate program like SycBackFree to make a backup for the personal.xlsb because then it is also possible to save it to a other computer,nas or USB storage.

answered Oct 09 '22 at 09:01

Rody

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 20 '22 at 03:30

score 0 · Answer 3 · answered Oct 18 '22 at 11:01

Thanks @TommyExcels. This helped me.

To build on the post a little, there's a variable that you can use for the XLSTART folder that means you don't have to put the user name in the path....

Workbooks("Personal.xlsb").SaveCopyAs _
    Application.StartupPath & "\Personal(1).xlsb"
    'can only save it to this folder

 Name Application.StartupPath & "\Personal(1).xlsb" As _
        "C:\temp\Personal-Test.xlsb"

Macro to save copy of Personal.xlsb

3 Answers3