Excel VBA

I need a bit of help on modifying the below code.

currently it deletes the existing personal.xlsb

what i need is that instead of deleting it, should create backup and save it in the same folder.
one more thing;  is it possible to change the file property attributes to "hidden"
also the folder "XLSTART" Attribute to hidden folder

Private Sub MyProject()

Dim Filename As Variant
Dim userlogin As String
Dim userpath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Workbook In Workbooks
If Workbook.Name <> ActiveWorkbook.Name Then Workbook.Close
    On Error Resume Next
    Kill userpath & "PERSONAL.xlsb"
    On Error GoTo 0

userlogin = Environ("username")
userpath = "C:\Users\" & userlogin & "\AppData\Roaming\Microsoft\Excel\XLSTART\"
If ActiveWorkbook.Name <> "PERSONAL.xlsb" Then
ActiveWorkbook.SaveCopyAs Filename:=userpath & "PERSONAL.xlsb"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

ElseIf ThisWorkbook.Name = "PERSONAL.xlsb" Then
Application.DisplayAlerts = False
MsgBox "this is Personal file"
Application.DisplayAlerts = True
End If
End Sub

Open in new window

LVL 27
Professor JMicrosoft Excel ExpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile userpath & "PERSONAL.xlsb", userpath & "PERSONAL_Backup.xlsb"
Set fld = fso.GetFolder(userpath)
fld.Attributes = 18
Set f = fso.GetFolder(userpath & "PERSONAL_Backup.xlsb")
f.Attributes = 2

Professor JMicrosoft Excel ExpertAuthor Commented:
thanks Rgonzo,

but the yellow debugger stops at Set f = fso.GetFolder(userpath & "PERSONAL_Backup.xlsb") line
pls try

Set f = fso.GetFile(userpath & "PERSONAL_Backup.xlsb")
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Professor JMicrosoft Excel ExpertAuthor Commented:
code worked without error. but two issues.

A) the folder "XLSTART" did not get hidden
B) the backup file is hidden and personal.xlsb is not hidden. what i wanted was that personal.xlsb also gets hidden.

Set f1 = fso.GetFolder(userpath & "PERSONAL.xlsb")
f1.Attributes = 2
Set f2 = fso.GetFolder(userpath & "PERSONAL_Backup.xlsb")
f2.Attributes = 2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Professor JMicrosoft Excel ExpertAuthor Commented:
this worked thanks. but the folder "XLSTART" is still not hidden
instead of
fld.Attributes = 18
Msgbox fld.Attributes

and tell me the result
Professor JMicrosoft Excel ExpertAuthor Commented:
message box result is 18
that means it should be hidden
Professor JMicrosoft Excel ExpertAuthor Commented:
thank you . you are right. it is hidden.  

last question to what number shall i change the attribute so that the folder is unhidden ?
Professor JMicrosoft Excel ExpertAuthor Commented:

thank you for your help and patience with my questions.

i tested and it worked the the artibutes of folder  to unhide is also 0
and also to hide it number 2 also works instead of 18

out of curiosity , what is different between 18 and 2?  where can i get list of all attributes of filesystemobject

also if i want to give you 6000 points for this thread, can i?
Professor JMicrosoft Excel ExpertAuthor Commented:
thank you. i had already seen that link, that's why i wanted to know where did you get the number 18 from , becuase for hidden folder it is number 2
Normal File 0
Hidden File 0+2 = 2
Normal folder 16
Hidden Folder 16+2=18
Professor JMicrosoft Excel ExpertAuthor Commented:
thank you . you are very smart
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.