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:

Mac folder access error
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?
LVL 15
Jamie GarrochSenior Technical Consultant at BrightCarbonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Owen RubinConsultantCommented:
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.

Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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
	com.apple.quarantine	   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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Owen RubinConsultantCommented:
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.
Owen RubinConsultantCommented:
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
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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.
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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.
Owen RubinConsultantCommented:
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!
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
I've found a solution. These don't work:

1. Calling the MkDir function directly from VBA (quarantined folder created)
2. Calling the Bash MkDir function via VBA MacScript (quarantined folder created)
3. Calling the Bash MkDir function prefixed with sudo via VBA MacScript (returns error as OSX password required)

Calling this script routine from VBA's AppleScriptTask function does work but the script must pre-exist in the file system in an Office application-specific location:

AppleScript file myScript.scpt:

on CreateAppFolder()
	tell application "Finder"
			set p1 to alias ((path to library folder from user domain as string) & "Group Containers:UBF8T346G9.Office")
			-- Creates folder with permissions set to drwxr-xr-x
			make new folder at p1 with properties {name:"TEST"}
		on error errMsg number errorNumber
			return errMsg
		end try
	end tell
end CreateAppFolder

Open in new window

AppleScriptTask call in VBA:

AppleScriptTask ScriptFile:="myScript.scpt", ScriptHandler:="CreateAppFolder", scriptParmeterString:=""

Open in new window

Script file location:

PowerPoint : ~/Library/Application Scripts/com.microsoft.Powerpoint
Excel : ~/Library/Application Scripts/com.microsoft.Excel
Word : ~/Library/Application Scripts/com.microsoft.Word
Outlook : ~/Library/Application Scripts/com.microsoft.Outlook

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Owen RubinConsultantCommented:
Glad you got it to work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.