macro to search and unzip and collect to total amount

Re: macro to search and unzip and collect to total amount

Hi Stone,

Welcome to the board.

You can use either Dir or FileSearch to look through your directory to try to identify which files you want to unzip.

Are you familiar with the Command Line instructions for WinRar? I've written code for unzipping files using WinZip but I'm not familiar with WinRar. You'll need to use Shell to pass the instruction to the archive program. I've attached an example using WinZip.

See what you can put together and repost with any problems.

HTH

Re: macro to search and unzip and collect to total amount

OK, I was curious ...
[vba]'******************************************************************
'Using WinRar within Excel
'Syntax
'
' RAR <command> [ -<switches> ] <archive> [ <@listfiles...> ]
' [ <files...> ] [ <path_to_extract\> ]
'******************************************************************

'*************
' Adding files
'*************

Sub WinRarIt()

Dim WinRarPath As String 'WinRar.exe location
Dim RarIt As String 'Command line instruction
Dim SourceDir As String 'The source directory
Dim SourceFile As String 'The source file
Dim Source As String 'The combined Rar from path(s)(FROM)
Dim DestDir As String 'The Rarped file directory
Dim DestRarName As String 'The Rarped file
Dim Dest As String 'The combined Rar to path (TO)

'*** Check installation of WinRar ***
WinRarPath = "C:\Program Files\WinRar\"
If Dir(WinRarPath, vbDirectory) = "" Then
MsgBox "WinRar is not installed in the default directory." _
& Chr$(13) & "Archiving of files will not be possible."
Exit Sub
End If

'*** Set the source details ***
SourceDir = ThisWorkbook.Path & "\"
SourceFile = "This has spaces.xls"
Source = SourceDir & "\" & SourceFile
'If source name has one or more spaces surround it with ""
If InStr(1, Source, " ", vbTextCompare) <> 0 Then Source = Chr(34) & Source & Chr(34)

'*** Set the destination details
DestDir = "C:\Rarped Excel Files"
'check that it exists
If Dir(DestDir, vbDirectory) = "" Then MkDir DestDir
DestRarName = "Test.Rar"
Dest = DestDir & "\" & DestRarName
If InStr(1, Dest, " ", vbTextCompare) <> 0 Then Dest = Chr(34) & Dest & Chr(34)

'*** Do the Rarping ***
RarIt = Shell(WinRarPath & "WinRar.exe a " & Dest & " " & Source, vbNormalFocus)

End Sub

'*****************
' Extracting files
'*****************

Sub UnWinRarIt()

Dim WinRarPath As String 'WinRar.exe location
Dim RarIt As String 'Command line instruction
Dim SourceDir As String 'The source directory
Dim SourceRarFile As String 'The source file
Dim Source As String 'The combined Rar from path(s)(FROM)
Dim Dest As String 'The combined unRar to path (TO)

WinRarPath = "C:\Program Files\WinRar\"
If Dir(WinRarPath, vbDirectory) = "" Then
MsgBox "WinRar is not installed in the default directory." _
& Chr$(13) & "Archiving of files will not be possible."
Exit Sub
End If

SourceDir = "C:\Rarped Excel Files"
SourceRarFile = "Test.Rar"
Source = SourceDir & "\" & SourceRarFile
If InStr(1, Source, " ", vbTextCompare) <> 0 Then Source = Chr(34) & Source & Chr(34)

Dest = "C:\UnRarped Excel Files\"
If Dir(Dest, vbDirectory) = "" Then MkDir Dest
If InStr(1, Dest, " ", vbTextCompare) <> 0 Then Dest = Chr(34) & Dest & Chr(34)

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

End Sub[/vba]Note that I have not explored the full range of command line options (I'll leave that to you) but this should get you started.

Good luck

Re: macro to search and unzip and collect to total amount

WinRar files i have never ever had joy with, Richie(uk) kickied this off i have some code thats bassed to what Richie has given, ermmm he sent it to me ages back, there are two options files serch and directory search - i do warn you this is a nasty projcet and will not be easy to just make code to run all you want ... maybe not so helpful my advice would be to unzip the FULL dir and bone pick what you want and build the project, also i assume shareware remember as it shelling out you will need to click evaluation ersion eash time it shells ie could be hundreds, if its registered can crash for millins of complex reasons... so care is needed.. i still have massive problems on this one, evn file names can crash out..

Jack

Re: macro to search and unzip and collect to total amount

Hi Stone,

OK, one approach I suggested above was to use Dir. You can even turn this into a function-based approach to return an array of the matching filenames. For an example, see JW's example here:
http://j-walk.com/ss/excel/tips/tip18.htm

We want to use the function to produce a list of files with the .rar extension. This could be achieved like this:
[vba]Sub Main()
Const strCriteria As String = "C:\Rarped Excel Files\*.rar"
Dim varFiles As Variant, lCnt As Long

varFiles = GetFileList(strCriteria)

Select Case IsArray(varFiles)
Case True 'files found
For lCnt = LBound(varFiles) To UBound(varFiles)
Sheets("Sheet1").Cells(lCnt, 1).Value = varFiles(lCnt)
Next lCnt
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub

Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:
GetFileList = False
End Function[/vba]In this example the rar files are simply listed to Sheet1. What you need to do is to modify the looping part of the code so that instead of listing the files to Sheet1 it makes use of the unzipping routine that we looked at yesterday. Give it a try for yourself (it really is the only way to learn!). If you get stuck then post back here with the code that you have used and a description of the problem(s) encountered.

Also, keep in mind the very valid point that Jack has made, namely:

I'm sure, even from my brief look at WinRar yesterday, that it is possible to unzip a whole directory at once. This may prove to be a lot less trouble (and a lot quicker) than unzipping one file at a time.

HTH

Re: macro to search and unzip and collect to total amount

Hi Stone

I have a Translated UnRar file I did a while ago that may be of use.
See what you can make of this ....