Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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
Next
    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"
ActiveWorkbook.Close

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

0
ProfessorJimJam
Asked:
ProfessorJimJam
  • 8
  • 7
1 Solution
 
Rgonzo1971Commented:
Hi,

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

Regards
0
 
ProfessorJimJamAuthor Commented:
thanks Rgonzo,

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

Set f = fso.GetFile(userpath & "PERSONAL_Backup.xlsb")
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ProfessorJimJamAuthor 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.

thanks
0
 
Rgonzo1971Commented:
Set f1 = fso.GetFolder(userpath & "PERSONAL.xlsb")
f1.Attributes = 2
Set f2 = fso.GetFolder(userpath & "PERSONAL_Backup.xlsb")
f2.Attributes = 2
0
 
ProfessorJimJamAuthor Commented:
this worked thanks. but the folder "XLSTART" is still not hidden
0
 
Rgonzo1971Commented:
instead of
fld.Attributes = 18
Msgbox fld.Attributes

and tell me the result
0
 
ProfessorJimJamAuthor Commented:
message box result is 18
0
 
Rgonzo1971Commented:
that means it should be hidden
0
 
ProfessorJimJamAuthor 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 ?
0
 
ProfessorJimJamAuthor Commented:
Rgonzo1917

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?
0
 
ProfessorJimJamAuthor 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
0
 
Rgonzo1971Commented:
Normal File 0
Hidden File 0+2 = 2
Normal folder 16
Hidden Folder 16+2=18
0
 
ProfessorJimJamAuthor Commented:
thank you . you are very smart
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now