[SOLVED] Extract .rar file in Vba Excel

Extract .rar file in Vba Excel

I have a vba macro in Excel which download a .rar file from a sharepoint site and extract it. The downloading is working fine, i can see the file downloaded in the folder but extracting the file is not working. I will paste just the part of extracting the file so my code looks like this

Sub Extract()

    Dim RarIt As String
    Dim Source As String
    Dim Desti As String
    Dim WinRarPath As String

    WinRarPath = "C:\Program Files\WinRar\"
    Source = "C:\Reports\EMEA Load.rar"
    Desti = "C:\Reports\"

    RarIt = Shell(WinRarPath & "WinRar.exe e " & Source & " " & Desti, vbNormalFocus)

End Sub

so when i run it, it returns a message box "no archives found" although i can see the .rar file in the folder.

I have created another solution, a function which unzip files

Function Unzip(str_FILENAME As String)

    Dim oApp As Object
    Dim Fname As Variant
    Dim FnameTrunc As Variant
    Dim FnameLength As Long
    Dim FnamePath As Long

    Fname = str_FILENAME
    FnamePath = InStrRev(Fname, "\")
    FnameTrunc = Left(Fname, FnamePath)

    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(FnameTrunc).CopyHere oApp.Namespace(Fname).Items

End Function

and i am calling this function in my Sub, it works fine for .zip files but for .rar file it returns this error

"method of Namespace object ishelldispatch6 failed"

Sub Extract2()

    Dim strFilePath As String
    strFilePath = "C:\Reports\EMEA Load.rar"

     Unzip (strFilePath)

End Sub

Any suggestions please what i need to change in my code ? Thank you very much.

Solution

Because we have spaces in files name we need to enclose it by Chr(34) :

Sub Extract()

    Dim RarIt As String
    Dim Source As String
    Dim Desti As String
    Dim WinRarPath As String

    WinRarPath = "C:\Program Files\WinRar\"
    Source = "C:\Reports\EMEA Load.rar"
    Desti = "C:\Reports\"

    RarIt = Shell(Chr(34) & WinRarPath & "WinRar.exe" & Chr(34) & " e " & Chr(34) & Source & Chr(34) & " " & Chr(34) & Desti & Chr(34), vbNormalFocus)

End Sub