Write-Once only rights

[marekba]

Dear All,

I have a macro in Excel 2007 that must create a folder on a server-based directory. Each user has specific rights to this directory. Some have read only, some have full access, and some only write-once rights.

Is there a way to send a request to this server or whatever other action to check what access rights a user has to specific folder?

The issue is the write-once only rights.

For the read-only and the full access, I can just try to create and see if it's successful, or use the fso.attributes, but this one doesn't have the write-once access rights.

Thank you in advance for your help !
M.

[marekba]

Further input, after some investigation :
using the following shell command on one of the concerned folder:
cacls myWriteOnceOnlyFolderPath > "return.txt"

, I got the following return :

myWriteOnceOnlyFolderPath

-AdminName : (OI)(CI)F

-TargetedGroupName : (OI)(IO)(DENY)(special access: )

FILE_APPEND_DATA
FILE_WRITE_EA
FILE_WRITE_ATTRIBUTES

(OI)(CI)(special access: )

READ_CONTROL
SYNCHRONIZE
FILE_GENERIC_READ
FILE_GENERIC_WRITE
FILE_GENERIC_EXECUTE
FILE_READ_DATA
FILE_WRITE_DATA
FILE_APPEND_DATA
FILE_READ_EA
FILE_WRITE_EA
FILE_EXECUTE
FILE_READ_ATTRIBUTES
FILE_WRITE_ATTRIBUTES

Found here ss64.com/nt/cacls can be also the meaning of OI, IO, CI.

I guess, the easiest solution to this problem would be to run this shell command from within VBA, let it export the result in a temporary textfile somewhere in a FullAccessible location, and read that from VBA and check if the group was assigned (DENY)/FILE_APPEND_DATA ...

A prettier solution than going through a temp text file would be to have the batchfile ECHO directly to a VBA variable or worksheet... or to the clipboard. But I couldn't find a code for that (note that we are talking here about XP, so we don't have yet the clip from Vista and win7 (if I got it right...)

Is there a known way to you for this kind of transfer batch ECHO --> worksheet of the currently opened ThisWorkbook (without using SendKey...)?

The shell PERMS seemed promising as well, but it doesn't seem to work, probably because it's only for win2000 and I'm in XP..

Well... Any suggestion ?

[snb]

you can use this shell command to store the result into variable 'c00':

sub M_snb()
c00=createobject("wscript.shell").exec("cmd /c .....").stdout.readall
end sub

what is the result if you use:

c018 = GetAttr("G:\OF\example.xls")

[marekba]

Hello, and Thank you for this quick reply !

This works perfectly, returns the shell output in the variable, then I can further process it in VB, thanks !

The GetAttr returns the expected 32, Archive Bit set.

I have made a little sub to tidy things up :

Option Explicit

Public Function ShellReturn(strShellCommand As String, Optional ImportSheetName As String) As String
    Dim str$
'load the Shell Command and return the string
    str = Trim(CreateObject("wscript.shell").exec("cmd /c " & strShellCommand).stdout.readall)
'    Debug.Print str
'split the string in rows, if linefeed, then in a collection
    Dim colStr As New Collection
    str = Trim(str)
    Do While InStr(str, Chr(10)) <> 0
'        str = Left(str, 1)
        If Not InStr(str, Chr(10)) = 0 Then     'if we are not on the last row
            colStr.Add Left(str, InStr(str, Chr(10)))
    '        Debug.Print colStr(colStr.Count)
            str = Right(str, Len(str) - InStr(str, Chr(10)))
        Else    'last row (does not contain any chr(10) = linefeed
            colStr.Add str
        End If
'        Debug.Print str
    Loop
    Debug.Print colStr.Count
'we can import the elements in a new or an existing sheet for further work
    If Not ImportSheetName="" Then
        Dim ws As Worksheet
        On Error GoTo WorkSheetDoesNotExist 'if the worksheet doesn't exist, we create it
        Set ws = ThisWorkbook.Worksheets(ImportSheetName)
        On Error GoTo 0
ws.UsedRange.ClearContents
        ws.Columns("A:AA").ColumnWidth = 14
Dim c As Range
        Set c = ws.Range("A1")
        Dim i
        For i = 1 To colStr.Count
            c.Cells(i, 1) = colStr(i)
        Next
ws.UsedRange.WrapText = False
        ws.UsedRange.HorizontalAlignment = xlLeft
        ws.Range("A1").Select
    End If
Exit Function
'###### Error Handling
WorkSheetDoesNotExist:  'The target Worksheet does not exist, so we create it
    ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = ImportSheetName
    Resume
End Function

[marekba]

you can use this shell command to store the result into variable 'c00':

sub M_snb()
c00=createobject("wscript.shell").exec("cmd /c .....").stdout.readall
end sub

but snb, this seems not to work with shell-for loops.
If I want for example to get a list of folders using a shell command and return it to VBA, I tried the following, in the direct window :

?CreateObject("wscript.shell").exec("cmd /c for /d %%a in (*) do echo %%a").stdout.readall

It works well with the DIR command, for example, or the CSCLS, but doesn't seem to work with the FOR. Probably because the stream is not generated in 1 go...

DO you have any Idae about how to resolve that issue? I'm quite struggling right now...

[Kenneth Hobs]

For folders only, use /a:d rather than /a:-d.

Sub DirStdOut()
  Dim s As String, a() As String
  ' /b = bare file listing, /s = search subfolders, /c = open command shell, run command and then close shell.
  ' /a:-d means list files only and not subfolders
  s = CreateObject("Wscript.Shell").Exec("cmd /c dir x:\test\*.* /a:d /b").StdOut.ReadAll
  a() = Split(s, vbCrLf)
  With Range("A1")
    .EntireColumn.Clear
    .Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
  End With
End Sub

[snb]

instead of a new collection I'd prefer an array:

Sub M_snb() 
sn=split(createobject("wscript.shell").exec("cmd /c .....").stdout.readall,vbcrLf) 
End Sub

for folders (including subfolders) in Drive G I use

Sub M_snb() 
    sn=split(createobject("wscript.shell").exec("cmd /c G:\*. /b /s").stdout.readall 
End Sub

[marekba]

Indeed, this split command in way more handy than my awkward workaround! Geez, if I had known of that it would have saved me some time until now

Thank you so much, both, wish you a nice day !
M.

[marekba]

One further question..
For launching a batch file "Silently" (without the black windows appearing), I usually use a vbs script with the following and launch the batch file from there :

CreateObject("Wscript.Shell").Run """" & WScript.Arguments(0) & """", 0, False

This ",0,false" works only with the .run command though (see http://ss64.com/vb/run.html)

To use the StdOut.ReadAll possibility though, we must use the .Exec function, instead of .Run, but then we cannot have it silent... Do you know a workaround that would let a shell command be run from within VBA in silent mode and return a string?

Thanks for your help!