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
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)
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")
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"
Dim strFilePath As String
strFilePath = "C:\Reports\EMEA Load.rar"
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
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