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:

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?
Microsoft OfficeMicrosoft 365Office ProductivityVBAMac OS X

Avatar of undefined
Last Comment
Owen Rubin

8/22/2022 - Mon
Owen Rubin

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 Garroch (MVP)

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.

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?
aikimark

To issue a system command or run a program from with VBA, you might try the Shell command.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Owen Rubin

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 Rubin

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 Garroch (MVP)

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:

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jamie Garroch (MVP)

ASKER
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 Rubin

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!
ASKER CERTIFIED SOLUTION
Jamie Garroch (MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Owen Rubin

Glad you got it to work.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes