?
Solved

Create folders and Share with permissions from the excel file

Posted on 2007-09-30
21
Medium Priority
?
1,643 Views
Last Modified: 2008-06-20
Hi,

I have a excel which has Foldernames in Colum A.Usernames who need full access in Colum B in security (NTFS Permissions)
All the folders should have full permissions to Everyone in Share permissions.

Is there a way to do this with a script or a Macro.

Regards
Sharath
0
Comment
Question by:bsharath
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 19987129
Sharath,

If I have said it already for you please excuse me. My recommendation is do not share Excel workbooks. They are liable to get corrupted as Excel does not handle sharing reliably.

Patrick
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 19987457
Sharath -
Did you mean to create those folder and set NT share permissions to all users in Column B(Or do you want to give permission to "Everyone") using macro?

Patrick - I am not sure but his question didnt sound like excel share. Lets see. :-)
0
 
LVL 11

Author Comment

by:bsharath
ID: 19987511
Haa...
My Q is to create folders with the names in Colum A.Everyone full access and NTFS to the names that are in Colum B...
0
Independent Software Vendors: 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!

 
LVL 45

Expert Comment

by:patrickab
ID: 19987621
Not sure whether that means 'shared' workbooks or not. Perhaps Sharath you're not sure what I mean be 'shared' workbooks - if that's the case then I guess they aren't shared.
0
 
LVL 11

Author Comment

by:bsharath
ID: 19988934
Patrick.

Folders on drives.
Say i want to create folders as
"Sharath" on c:\ with full access to share and Full access to "Sharath" to NTFS permissions.
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 19989776
Hi Sharath,

Assuming that Column A contains the full path to the folder, and Column B contains one username, then you could save the file as a CSV and run this at the command prompt of the server containing the shared folders:

for /f "tokens=1,2 delims=," %i in (YourFile.CSV) do cacls %i /t /e /c /g Domain\%j:C

The options in the cacls command will edit (not replace - /t) the permissions of the folder (%i) and all subfolders (/e) and grant (/g) the user (%j) change permissions (:C). It wil continue on access denied errors (/c).

If you want to give users full permissions just use :F instead of :C at the end of the line.


HTH,
Wallsy
0
 
LVL 11

Author Comment

by:bsharath
ID: 20011696
Wallsy...
Sorry for the delay..

I get this...
C:\>for /f "tokens=1,2 delims=," %i in (share.CSV) do cacls %i /t /e /c /g Devel
opment\%j:C

C:\>cacls c:\folder1 /t /e /c /g Development\sharathr:C

C:\>

No errors but the folder is not created...
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20013774
Sharath,

I thought the folders already existed!

Try this in a batch file instead:
for /f "tokens=1,2 delims=," %%i in (YourFile.CSV) do (
md "%%i"
cacls "%%i" /t /e /c /g Domain\%j:C
)

The first line will make the directory and the second line will set the permissions.

HTH,

Wallsy
0
 
LVL 11

Author Comment

by:bsharath
ID: 20014060
I get this...


C:\>"Create folders.bat"

C:\>for /F "tokens=1,2 delims=," %i in (folders.CSV) do (
md "%i"
 cacls "%i" /t /e /c /g Domain\C
)

C:\>(
md "sharathr "
 cacls "sharathr " /t /e /c /g Domain\C
)
Invalid arguments.Displays or modifies access control lists (ACLs) of files

CACLS filename [/T] [/M] [/S[:SDDL]] [/E] [/C] [/G user:perm] [/R user [...]]
               [/P user:perm [...]] [/D user [...]]
   filename      Displays ACLs.
   /T            Changes ACLs of specified files in
                 the current directory and all subdirectories.
   /M            Changes ACLs of volumes mounted to a directory
   /S            Displays the SDDL string for the DACL.
   /S:SDDL       Replaces the ACLs with those specified in the SDDL string
                 (not valid with /E, /G, /R, /P, or /D).
   /E            Edit ACL instead of replacing it.
   /C            Continue on access denied errors.
   /G user:perm  Grant specified user access rights.
                 Perm can be: R  Read
                              W  Write
                              C  Change (write)
                              F  Full control
   /R user       Revoke specified user's access rights (only valid with /E).
   /P user:perm  Replace specified user's access rights.
                 Perm can be: N  None
                              R  Read
                              W  Write
                              C  Change (write)
                              F  Full control
   /D user       Deny specified user access.
Wildcards can be used to specify more that one file in a command.
You can specify more than one user in a command.

Abbreviations:
   CI - Container Inherit.
        The ACE will be inherited by directories.
   OI - Object Inherit.
        The ACE will be inherited by files.
   IO - Inherit Only.
        The ACE does not apply to the current file/directory.

C:\>

I also tried with the domain name and without.
Csv file has this.

Colum A    Colum B
Sharath    Domain name\sharathr
and even this
Sharath     Sharathr
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20014552
Sorry Sharath - the dangers of copy and paste!

%j in the file should read %%j

I've assumed that all accounts are in the same domain so it's hardcoded in the cacls file. If you did want to specify domain and user you'd change
Domain\%%j:C
to
%%j:C

Cheers,
Wallsy
0
 
LVL 11

Author Comment

by:bsharath
ID: 20014679
Hi Wallsy,

I get this.


C:\>"Create folders.bat"

C:\>for /F "tokens=1,2 delims=," %i in (folders.CSV) do (
md "%i"
 cacls "%i" /t /e /c /g Development\%j:C
)

C:\>(
md "sharathr "
 cacls "sharathr " /t /e /c /g Development\john:C
)
processed dir: C:\sharathr

The problem is it adds "john" to security permissions (NTFS) but does not share the folder "Sharathr"
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20015689
Right - I'm going to start again to make sure that this file will work on servers as well!!  :-)
You can set the "SourceFolder" variable to create the folders in a different drive. I'm assuming the folder names won't have spaces as they will also be share names.

@Echo Off
SetLocal
PushD %0\..
Set CSVFile=YourFile.CSV
Set Domain=YourDomain
Set SourceFolder=D:\UserData
For /f "tokens=1,2 delims=," %%i in (%CSVFile%) do (
      PushD %SourceFolder%
      Md %%i
      Cacls %%" /t /e /c /g Domain\%%j:C
      Net Share %%i=%SourceFolder%\%%i
      PopD
)
Popd
EndLocal
0
 
LVL 11

Author Comment

by:bsharath
ID: 20015789
I get this.


C:\>"Create folders.bat"
The system cannot find the path specified.
The system cannot find the path specified.
The syntax of this command is:


NET SHARE
sharename
          sharename=drive:path [/GRANT:user,[READ | CHANGE | FULL]]
                               [/USERS:number | /UNLIMITED]
                               [/REMARK:"text"]
                               [/CACHE:Manual | Documents| Programs | None ]
          sharename [/USERS:number | /UNLIMITED]
                    [/REMARK:"text"]
                    [/CACHE:Manual | Documents | Programs | None]
          {sharename | devicename | drive:path} /DELETE


C:\>

I have used the same script changing the domain name and the csv file name that's it...
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20016553
Did you change the SourceFolder variable to something other than D:\UserData? If not, does the computer you are running this script on have a folder called D:\UserData?

I changed that part of the script because I thought that eventually you would be running this on a server and you don't want all of your folders to be created in C:\

0
 
LVL 11

Author Comment

by:bsharath
ID: 20019276
Yes i have created a folder  in c:\userdata
After i run this bat file i just get the folder created no share or Ntfs permissions set.

@Echo Off
SetLocal
PushD %0\..
Set CSVFile=folders.CSV
Set Domain=development
Set SourceFolder=C:\UserData
For /f "tokens=1,2 delims=," %%i in (%CSVFile%) do (
      PushD %SourceFolder%
      Md %%i
      Cacls %%" /t /e /c /g Domain\%%j:C
      Net Share %%i=%SourceFolder%\%%i
      PopD
)
Popd
EndLocal
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20020249
The Cacls line has %%" instead of %%i

Can you run it without the @Echo Off line so that we can see if Net Share is throwing up an error?

Cheers,
Wallsy
0
 
LVL 11

Author Comment

by:bsharath
ID: 20020262
I get this...

C:\>"Create folders.bat"

C:\>SetLocal

C:\>PushD "Create folders.bat"\..

C:\>Set CSVFile=folders.CSV

C:\>Set Domain=development

C:\>Set SourceFolder=C:\UserData

C:\>For /F "tokens=1,2 delims=," %i in (folders.CSV) do (
PushD C:\UserData
 Md %i
 Cacls %i" /t /e /c /g Domain\%j:C
 Net Share %i=C:\UserData\%i
 PopD
)

C:\>(
PushD C:\UserData
 Md sharathr
 Cacls sharathr " /t /e /c /g Domain\enochj:C
 Net Share sharathr =C:\UserData\sharathr
 PopD
)
A subdirectory or file sharathr already exists.
Displays or modifies access control lists (ACLs) of files

CACLS filename [/T] [/M] [/S[:SDDL]] [/E] [/C] [/G user:perm] [/R user [...]]
               [/P user:perm [...]] [/D user [...]]
   filename      Displays ACLs.
   /T            Changes ACLs of specified files in
                 the current directory and all subdirectories.
   /M            Changes ACLs of volumes mounted to a directory
   /S            Displays the SDDL string for the DACL.
   /S:SDDL       Replaces the ACLs with those specified in the SDDL string
                 (not valid with /E, /G, /R, /P, or /D).
   /E            Edit ACL instead of replacing it.
   /C            Continue on access denied errors.
   /G user:perm  Grant specified user access rights.
                 Perm can be: R  Read
                              W  Write
                              C  Change (write)
                              F  Full control
   /R user       Revoke specified user's access rights (only valid with /E).
   /P user:perm  Replace specified user's access rights.
                 Perm can be: N  None
                              R  Read
                              W  Write
                              C  Change (write)
                              F  Full control
   /D user       Deny specified user access.
Wildcards can be used to specify more that one file in a command.
You can specify more than one user in a command.

Abbreviations:
   CI - Container Inherit.
        The ACE will be inherited by directories.
   OI - Object Inherit.
        The ACE will be inherited by files.
   IO - Inherit Only.
        The ACE does not apply to the current file/directory.
The syntax of this command is:


NET SHARE
sharename
          sharename=drive:path [/GRANT:user,[READ | CHANGE | FULL]]
                               [/USERS:number | /UNLIMITED]
                               [/REMARK:"text"]
                               [/CACHE:Manual | Documents| Programs | None ]
          sharename [/USERS:number | /UNLIMITED]
                    [/REMARK:"text"]
                    [/CACHE:Manual | Documents | Programs | None]
          {sharename | devicename | drive:path} /DELETE


C:\>Popd

C:\>EndLocal

C:\>
0
 
LVL 6

Accepted Solution

by:
Wallsy earned 2000 total points
ID: 20020384
Hi Sharath,

Well, I've found a few typos (mine) and the main problem, which is caused by the fact that we're not trimming spaces off the names. So here's a version that should work for you:

@Echo Off
SetLocal
PushD %0\..
Set CSVFile=folders.CSV
Set Domain=Development
Set SourceFolder=C:\UserData
For /f "tokens=1,2 delims=," %%i in (%CSVFile%) do (
      PushD %SourceFolder%
      Md %%i
      For %%k in (%%j) Do Cacls %%i /t /e /c /g %Domain%\%%k:C
      For %%l in (%%i) Do Net Share %%l=%SourceFolder%\%%l
      PopD
)
Popd
EndLocal
0
 
LVL 11

Author Comment

by:bsharath
ID: 20020423
Wallsy

I get this...
C:\>"Create folders.bat"
processed dir: C:\UserData\sharathr
sharathr was shared successfully.

It has done as expected...
I wanted some more tweaking in this...
I shall raise a new Q...
if you say yes

1. Full  access to everyone in Shared and NTFS permissions
2. Full access to everyone in Shared permissions and remove all others than the system and the name in the file for Ntfs Full access to the user
3. Full access to everyone in Shared permissions and give read only access to user and System
0
 
LVL 6

Expert Comment

by:Wallsy
ID: 20020440
Thanks Sharath!

Of course we can do more. You can use cacls to tweak the ntfs permissions, but you will probably have to download SetACL to change the share permissions.

Wallsy
0
 
LVL 11

Author Comment

by:bsharath
ID: 20020462
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 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