Link to home
Start Free TrialLog in
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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)

MkDir "/Users/[USERNAME]/Documents/test"
MkDir "/Users/[USERNAME]/Library/Group Containers/UBF8T346G9.Office/test"

Open in new window

But neither of them are accessible programmatically or via Finder:

User generated image
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 tell

Open in new window

Public 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 Sub

Open in new window

What is wrong and is there a workaround for this?
Avatar of Owen Rubin
Owen Rubin
Flag of United States of America image

I do not have 2016 in front of me, but it was suggested by a colleague that you add the 'sudu' command in front of the mkdir command.  I can't test this though, but obviously the ownership or permissions on the directory created are wrong.

Can you open a terminal, go to /Users/[USERNAME]/Documents/ and do an ls-al command?  I want to see the premissions and who the owner of the folder you created.

Avatar of Jamie Garroch (MVP)


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.

drw-r--r--@ 2 JamieGarroch  staff     64 20 Oct 13:34 test1
drwxr-xr-x  2 JamieGarroch  staff     64 20 Oct 13:39 test2

Open in new window

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:

drw-r--r--@ 2 JamieGarroch  staff     64 20 Oct 13:34 test1	   35 

Open in new window

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.
Ah, look here:

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
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:

MkDir PathToFolder

Open in new window

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.
More news. This VBA procedure uses OSX Bash to create the folder:

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 Sub

Open in new window

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.
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!
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad you got it to work.