VBA MkDir not working on Office:mac 2016 / Office365
I'm sure this used to work but on the latest version of Office:mac 2016 (16.19 / 181001) the MkDir function appears to be broken.
Both of these lines will create a folder called "test", the second in the Office sandbox: (substitute "[USERNAME]" accordingly)
As a workaround, I tried using this script with the VBA MacScript function below but it returns error 5 when used in VBA even though it works in the OSX ScriptEditor app:
What is wrong and is there a workaround for this?
Both of these lines will create a folder called "test", the second in the Office sandbox: (substitute "[USERNAME]" accordingly)
MkDir "/Users/[USERNAME]/Documents/test"
MkDir "/Users/[USERNAME]/Library/Group Containers/UBF8T346G9.Office/test"As a workaround, I tried using this script with the VBA MacScript function below but it returns error 5 when used in VBA even though it works in the OSX ScriptEditor app:
tell application "Finder"
set p to path to documents folder
make new folder at p with properties {name:"ZIP"}
end tellPublic Sub MakeMacFolder()
Dim tAppleScript As String
tAppleScript = tAppleScript & "tell application ""Finder""" & vbCr
tAppleScript = tAppleScript & "set p to path to documents folder" & vbCr
tAppleScript = tAppleScript & "make new folder at p with properties {name:""test""}" & vbCr
tAppleScript = tAppleScript & "end tell"
MacScript tAppleScript
End SubWhat is wrong and is there a workaround for this?
ASKER
Thanks for the suggestion Owen.
I don't understand the sudo comment because you can't run a sudu command from VBA.
I ran the ls -l command to get the permission of two folders. test1 is the one created with the mkdir in VBA and test2 is created with mkdir in the shell.
So comparing the test1 directory which has the issue with test2 permissions as follows:
user : rw- versus rwx
group : r-- versus r-x
other : r-- versus r-x
New, given the erroneous directory has extended permissions (indicated by the @ symbol) I then ran the ls -l@ command and got this:
So it looks like there's an issue with VBA creating folders?
I don't understand the sudo comment because you can't run a sudu command from VBA.
I ran the ls -l command to get the permission of two folders. test1 is the one created with the mkdir in VBA and test2 is created with mkdir in the shell.
drw-r--r--@ 2 JamieGarroch staff 64 20 Oct 13:34 test1
drwxr-xr-x 2 JamieGarroch staff 64 20 Oct 13:39 test2So comparing the test1 directory which has the issue with test2 permissions as follows:
user : rw- versus rwx
group : r-- versus r-x
other : r-- versus r-x
New, given the erroneous directory has extended permissions (indicated by the @ symbol) I then ran the ls -l@ command and got this:
drw-r--r--@ 2 JamieGarroch staff 64 20 Oct 13:34 test1
com.apple.quarantine 35 So it looks like there's an issue with VBA creating folders?
To issue a system command or run a program from with VBA, you might try the Shell command.
I was not sure if VBA allowed the sudo or not. thanks for letting me know.
But yes, it looks like the mkdir command from within Excel VBA is not working correctly. A scan of Microsoft's forums show other people complaining about it as well.
Sadly, it sounds like creating the directory first, or requiring the user to create the directory first may be the only workaround for this.
It seems strange though, because I would assume that the VBA would run as you, and as such, would create the directory as you. But it looks like that is not what is happening.
But yes, it looks like the mkdir command from within Excel VBA is not working correctly. A scan of Microsoft's forums show other people complaining about it as well.
Sadly, it sounds like creating the directory first, or requiring the user to create the directory first may be the only workaround for this.
It seems strange though, because I would assume that the VBA would run as you, and as such, would create the directory as you. But it looks like that is not what is happening.
Ah, look here: https://www.rondebruin.nl/mac/mac034.htm
Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code
In Windows Excel 97-2016 and in Mac Excel 2011 you can open files or save files where you want in almost every folder on your system without warnings or requests for permission. But in Mac Office 2016 Microsoft have to deal with Apple’s sandbox requirements. When you use VBA in Mac Excel 2016 that Save or Open files you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file.
From that excerpt alone I can see that Apple's sandbox requirements are interfering with creating folders if it has that problem with creating files.
Go further down that article and it talks about folders
Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code
In Windows Excel 97-2016 and in Mac Excel 2011 you can open files or save files where you want in almost every folder on your system without warnings or requests for permission. But in Mac Office 2016 Microsoft have to deal with Apple’s sandbox requirements. When you use VBA in Mac Excel 2016 that Save or Open files you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file.
From that excerpt alone I can see that Apple's sandbox requirements are interfering with creating folders if it has that problem with creating files.
Go further down that article and it talks about folders
ASKER
I'm actually using the part of that article to establish in in-sandbox folder "UBF8T346G9.Office" from the section "How to create a Folder in the Office folder with VBA code". Four lines up from the end of that code snippet you can see Ron uses the VBA version of MkDir in this line:
It's this that was working a few months ago and has now stopped working.
Good idea about using Shell to get around the fact that Microsoft appear to have broken this aspect of Mac VBA.
MkDir PathToFolderIt's this that was working a few months ago and has now stopped working.
Good idea about using Shell to get around the fact that Microsoft appear to have broken this aspect of Mac VBA.
ASKER
More news. This VBA procedure uses OSX Bash to create the folder:
The resulting folder has the same permissions as the one created using the VBA MkDir command and is also appended with @. On -ls -l@ I see the folder is still quarantined.
I then tried prefixing the mkdir command with sudo and got a VBA error. So I switched back to terminal and tried the same sudo-prefixed command and was requested to enter the OSX password, after which the folder was created as expected.
So it looks like something in OSX security has changed that prevents VBA from creating folders.
Sub MkDirTest()
Dim AppleScript As String
Const USER_NAME = "MyUserName"
AppleScript = "do shell script ""mkdir /Users/" & USER_NAME & "/Library/'Group Containers'/UBF8T346G9.Office/Test"""
MacScript AppleScript
End SubThe resulting folder has the same permissions as the one created using the VBA MkDir command and is also appended with @. On -ls -l@ I see the folder is still quarantined.
I then tried prefixing the mkdir command with sudo and got a VBA error. So I switched back to terminal and tried the same sudo-prefixed command and was requested to enter the OSX password, after which the folder was created as expected.
So it looks like something in OSX security has changed that prevents VBA from creating folders.
Yea, I posted the article because it had that code. I just wasn't sure what part you needed, so easier to point you to the article which seemed to cover the problem you were having. I think this is Apple's fault, but I get their security model. Just breaks things from time to time. But even this does not seem to be enough from to get around the problem from your posts. Sorry about that.
Maybe file a bug with Apple? I suspect they will tell you its a security issue so they won't fix it. SIGH!
Maybe file a bug with Apple? I suspect they will tell you its a security issue so they won't fix it. SIGH!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you got it to work.
Can you open a terminal, go to /Users/[USERNAME]/Document
Thanks