File handling workaround for VBA on mac: a tutorial

I was recently forced to create a relatively simple plugin for PowerPoint 2011 in Microsoft’s Visual Basic for Applications (VBA), mostly because it has the greatest functionality of all PowerPoint APIs out there. I quickly found that most image and file handling functionality in VBA for mac seems to be broken, which is the OS I work on. After lots of searching, and quite some struggles, I found some workarounds however. I figured I would put them up here so others don’t have to go through the same meticulous process.These are the issues for which I will present workarounds:

  • Both file and folder prompting (Application.FileDialog) are not supported.
  • Wildcards in both the file and folder search (Dir) are unsupported.

I was also almost able to find a workaround for the following problem:

Luckily, VBA has the capability to run AppleScripts2. If you don’t know about Applescripts3, they are just what they sound like: scripts that allow you to automate various things in macOS. The cool thing about the VBA integration is that we can even exchange information between the VBA program and the AppleScript, which allows us to replicate some of the missing functionality in VBA. Let’s go through all the workarounds!

File prompting

Have a look at the excellent answer to this stackoverflow question, from which I learnt the Applescript trick.

Folder prompting

To prompt the user to choose a folder instead of the file, all we have to do is change the Applescript around in the file aforementioned prompting code by CuberChase on stackoverflow. Here is a listing of the modified example:

' Example by CuberChase, 2013 on http://stackoverflow.com/questions/15535413/filedialog-in-word-2011-vba
' Modified by Arthur Admiraal, 2016 on http://www.arthuradmiraal.nl/programming/file-handling-workaround-for-vba-on-mac-a-tutorial
  Dim vFolderName As Variant
  
  'Call the function to return the files
  vFolderName = Select_Folder_Mac
  
  'If it's empty then the user cancelled
  If IsEmpty(vFolderName) Then
    ' The cancel button has been pressed
    GetFolderOnMac = ""
    Exit Function
  End If
  
  GetFolderOnMac = vFolderName
End Function

Function Select_Folder_Mac() As Variant
  'Uses AppleScript to select a folder on a Mac
  Dim MyScript As String, MyFolder As String
  
  'Get the documents folder as a default
  On Error Resume Next
  
  'Set up the Apple Script to look for text files
  MyScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
             "set theFolder to (choose folder " & _
             "with prompt ""Choose picture folder.""" & _
             " multiple selections allowed false) as string" & vbNewLine & _
             "set applescript's text item delimiters to """" " & vbNewLine & _
             "return theFolder"
  
  'Run the Apple Script
  MyFolder = MacScript(MyScript)
  On Error GoTo 0
    
  Select_Folder_Mac = MyFolder
End Function

Better file search

We can use an AppleScript to search for files. This also has the advantage that it adds recursive searching functionality. Have a look at this code:

Function FindFiles(Search As String, SearchFolder As String)
  MyScript = "tell application ""Finder"" to set fileList to the entire contents of alias """ & SearchFolder & """ as alias list" & vbNewLine & _
             "set matchedFiles to {}" & vbNewLine & _
             "repeat with aFile in my fileList" & vbNewLine & _
             "    tell application ""System Events"" to if aFile's name contains """ & Search & """ then set end of matchedFiles to (aFile as text)" & vbNewLine & _
             "end repeat" & vbNewLine & _
             "set listSize to count of matchedFiles" & vbNewLine & _
             "If listSize > 0 Then" & vbNewLine & _
             "    return item 1 of matchedFiles " & vbNewLine & _
             "Else" & vbNewLine & _
             "    return """"" & vbNewLine & _
             "End If"
  
  FindFiles = MacScript(MyScript)
End Function

LoadPicture for non-bmp images

This is perhaps the most annoying problem in mac VBA, which isn’t surprising considering that I’ve seen it described as impossible on some places. Nevertheless, I tried it anyway. One cool thing about AppleScripts is that we can execute command-line commands from it. This allows us to do even more things with AppleScript than without this capability.

The first problem is that the images need to be a specific type of image to be loaded into the userform. We can use the command-line tool ImageMagick to convert our images to one of these types, which is a specific bmp format. You will have to install it first though. But after that, just open the terminal and execute:
imagemagick convert "" -alpha off -colorspace srgb -depth 8 -define bmp:format=bmp3 ".bmp"
You should be able to manually load in the converted images to an imageControl in the userform.

You can also try to do this programatically, provided that the user has ImageMagick installed4:

Sub main()
  Dim Source As String
  Dim Destination As String
  
  Source      = ".png"
  Destination = ".bmp"

  convertImgToBmp Source, Destination
  Set imgPreview.picture = LoadPicture(Destination) ' Doesn't work on mac. There doesn't seem to be a workaround
  DeleteFile (Destination)
  UserForm1.Repaint
End Sub

Sub convertImgToBmp(file As String, newfile As String)
  'Uses AppleScript to convert files to bmp files to get around the annoying invalid picture type error
  Dim file2 As String
  Dim newfile2 As String
  ' converts filepaths to POSIX, since the vba interpreter doesn't seem to handle this.
  file2 = Replace(Replace(file, ":", "/"), "Macintosh HD", "")
  newfile2 = Replace(Replace(newfile, ":", "/"), "Macintosh HD", "")
  '"set command to ""/opt/ImageMagick/bin/convert \""" & file & "\"" -alpha off -colorspace srgb -depth 8 -define bmp:format=bmp3 \""" & newfile & "\""""" & vbNewLine &
  cmd = "/opt/ImageMagick/bin/convert \""" & file2 & "\"" -alpha off -colorspace srgb -depth 8 -define bmp:format=bmp3 \""" & newfile2 & "\"""
  MyScript = "do shell script """ & cmd & """"
  
  'Run the Apple Script
  MacScript MyScript
  On Error GoTo 0
End Sub

' code taken from "How to delete files using VBA, including files which may be readonly"
' by Dave Rado and Doug Steele
' http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm
Sub DeleteFile(Killfile As String)
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Sub

But *Spoilers* this won’t work. For some reason, VBA for mac doesn’t recognize LoadPicture as a function, so that the whole image conversion was for nothing. Still, I have included it here, in the hopes that this may just give someone the inspiration they need to get this to work. Edit (4-7-2016): I just found a cool add-in by Richard Worthington that provides LoadPicture functionality for vba in Excel for mac by reloading the userform. Go check it out here. It seems to only work for the specific types of images that you can manually load in though. I haven’t tested this myself, but I think you could adapt the code to applications other than Excel. By cobbling it together with the above image conversion code, it should allow you to load in all sorts of image file types, like on windows.

Cross-platform compatibility

I only just discovered this, but VBA has the #IF, #ELSE, and #END IF preprocessor commands, meaning that you could detect the operating system type and only compile the code needed for that system. Just adding the #-signs to a normal if statement to switch between functions should do it.

Conclusion

I really hope this helps someone out.

If you’re reading this and only just started a VBA project on mac, I would recommend you to reconsider your language choice for anything but the most basic projects. During my VBA development I got a lot of strange errors, code that wouldn’t work unless removed and inserted again, for example. If python-pptx had animation support, I would have used that. I also tried OpenOffice, which has a much better API in my opinion, but I found the animation quality to be too low for my application, and the export to PowerPoint didn’t seem to preserve animations, nor images.

Anyhow, good luck with your automation projects everyone!

0 0

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *