问题描述:

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.

网友答案:

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
相关阅读:
Top