VBA MkDir not working on Office:mac 2016 / Office365

Jamie Garroch
Jamie Garroch used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Thanks
Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

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?
Top Expert 2014

Commented:
To issue a system command or run a program from with VBA, you might try the Shell command.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

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

Author

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 BrightCarbon

Author

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.

Commented:
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!
Senior Technical Consultant at BrightCarbon
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"
		try
			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

Commented:
Glad you got it to work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial