Solved

Excel VBA

Posted on 2014-10-03
15
147 Views
Last Modified: 2014-10-03
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
Comment
Question by:ProfessorJimJam
  • 8
  • 7
15 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40359057
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
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359068
thanks Rgonzo,

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

Expert Comment

by:Rgonzo1971
ID: 40359089
pls try

Set f = fso.GetFile(userpath & "PERSONAL_Backup.xlsb")
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359176
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
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40359189
Set f1 = fso.GetFolder(userpath & "PERSONAL.xlsb")
f1.Attributes = 2
Set f2 = fso.GetFolder(userpath & "PERSONAL_Backup.xlsb")
f2.Attributes = 2
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359227
this worked thanks. but the folder "XLSTART" is still not hidden
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40359260
instead of
fld.Attributes = 18
Msgbox fld.Attributes

and tell me the result
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359273
message box result is 18
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40359281
that means it should be hidden
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359287
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
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359305
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40359321
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359338
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40359347
Normal File 0
Hidden File 0+2 = 2
Normal folder 16
Hidden Folder 16+2=18
0
 
LVL 25

Author Comment

by:ProfessorJimJam
ID: 40359360
thank you . you are very smart
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now