Consulting

Results 1 to 7 of 7

Thread: Unzip in Excel/VBA for Mac - CreatObject("Shell.Application") not working

  1. #1
    VBAX Regular
    Joined
    Jan 2023
    Location
    Belgium
    Posts
    6
    Location

    Unzip in Excel/VBA for Mac - CreatObject("Shell.Application") not working

    Hello,
    I'm trying to get some VBA to work on my Mac (Big Sur 11.7.2) to Unzip a Zip file.

    This is the code that would normally work on a Windows system ... but, I have a Mac :
    Sub UnzipFile(zippedFileFullName As Variant, unzipToPath As Variant)
        Dim ShApp As Object
        Set ShApp = CreateObject("Shell.Application")
        ShApp.Namespace(unzipToPath).CopyHere ShApp.Namespace(zippedFileFullName).items
    End Sub
    This is how I would call it:
    Call UnzipFile("/Users/Colin/Documents/Test/eurofxref-hist.zip", "/Users/Colin/Documents/Test")
    When I call it (or step through the Sub), as ActiveX isn't available on the Mac, I get: Run-Time error '429': ActiveX component can't create object.

    Does anyone have an idea how to get an equivalent solution working on a Mac?

    Many thanks.

    UPDATE : Just found one way to do it, with a Mac App that I wrote to call the Archive Utility.app and run with:
    Shell "/Users/Colin/Documents/Computing/Scripts/Expand_Zip.app"
    Maybe there is a more elegant way to do this within VBA.
    Last edited by Colin_In_BE; 01-13-2023 at 12:49 AM.

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,217
    Location
    Didn't you get help in Excel Forum?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Jan 2023
    Location
    Belgium
    Posts
    6
    Location
    No, not yet ; it was just suggested to find a specific Apple forum (and I didn't find much on the Apple forums on VBA).

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,217
    Location
    Have you cross posted this thread else where? Simple courtesy about cross posting should have been the order of the day here. It'll be up to the members here whether they assist you or not given that you have cross posted.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Regular
    Joined
    Jan 2023
    Location
    Belgium
    Posts
    6
    Location
    In the FAQs related to multiposting (cross-posting), clinking the link in "If you are still confused, read A message to forum cross posters." gives me a 404 error.
    I would have liked to delete the posts on the other site, as it appears they don't have the knowledge of the Mac side. But, it seems not to be possible.
    If you have a solution to improve this situation now, I'd be pleased to read it. And I know the approach for the future. Thanks.
    Last edited by Colin_In_BE; 01-13-2023 at 02:11 AM.

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,217
    Location
    Mate... it's about the the integrity of the person asking the question. If for example you had said... "I've asked this question here but it's failed to extract any real response"... no issues, or you had said I've posted this here and to date there's been minimal response.... again no issues. We will if possible try to assist you. On the other hand if you cross post and don't want to acknowledge it... members here ( who may well be members elsewhere) take this as a sign that you don't recognise their contributions and may well not try to assist. If you wish to post here in this forum, please feel free tp do so, but do so with complete honesty.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    VBAX Regular
    Joined
    Jan 2023
    Location
    Belgium
    Posts
    6
    Location
    Resolved by writing an AppleScript, which I call as an app from VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •