3

I am using Excel for Mac 2011 which has VBA version 14.0.

I am trying to use this code which accesses the file system, which I know works on Windows:

Function qfil_GetDirectory(strDirectoryName As String)

    Dim objFSO As Variant
    Dim objDirectory As Variant

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objDirectory = objFSO.GetFolder(strDirectoryName)
    Set qfil_GetDirectory = objDirectory

End Function

However, when I run it in Excel for Mac 2011, it gives me this error:

Run-time error 429 Object creation with ActiveX component not possible

To fix this on Windows, I know I have to just reference a specific DLL under tools.

However on the Mac, when I go under tools | references it only gives me these:

alt text

And none of them allow me to use Scripting.FileSystemObject.

What do I have to do in Excel 2011 for Mac so that I can use Scripting.FileSystemObject to read files from the hard drive from an Excel sheet via VBA?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047

4 Answers4

2

To my knowledge, this approach won't be valid on Mac, as the external library doesnt exist.

Don't know Office 11 VBA, but the Dir() function works accross platforms for '03.

trickwallett
  • 2,418
  • 16
  • 15
0

What about...? Writing mac compatible file i o code in vba

kksf
  • 55
  • 1
  • 2
  • 10
0

Mac doesn't support ActiveX (it's a Windows technology), so you can't create the Scripting.FileSystemObject; it doesn't exist. (Even if it did, it's based on FAT32 or NTFS file systems, so it wouldn't work on OS X anyway.)

Ken White
  • 123,280
  • 14
  • 225
  • 444
0

If you're just trying to enumerate all the files in a folder, Codematic have a pretty good VBA module which should work fine on the Mac. It's not free, though.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Chris Rae
  • 5,627
  • 2
  • 36
  • 51