Unzipping multiple files using VBA - Why is this happening?

[agarwaldvk]

Hi Everybody


I am trying to unzip multiple user selected files using Winzip through Excel VBA. This is what I have been trying - there are 2 versions of it, one works but open a Windows Explorer window for each file that it unzips and the other version comes up with an error (explained later). This is the code that I have (the code for the other version has been commented out) :-


[vba]
Sub UnzipUserSelectedMultipleZippedFiles()
Dim fd As FileDialog
Dim fso As New FileSystemObject
Dim msg1 As String
Dim startTime As Single, finishTime As Single, totalTime As Single
Dim countStart As Long, countWrkg As Long, countFinal As Long, reportFilesCount As Long
countStart = 1
Application.StatusBar = "Unzipping Selected Zipped Files Now...Please wait"
Application.ScreenUpdating = False
msg1 = "Please select the location where you would like the files to be extracted in the next step!"
MsgBox prompt:=msg1, Buttons:=vbCritical + vbOKOnly, Title:="Select 'Select Location for the Extracted Files"
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.AllowMultiSelect = False
.InitialView = msoFileDialogViewDetails
.InitialFileName = "C:\"
.Show
If .SelectedItems.Count = 0 Then
msg1 = "No selection has been made"
msg1 = msg1 & Chr(10) & Chr(10) & Chr(13) & "Terminating Program Now"
msg1 = msg1 & Chr(10) & Chr(10) & Chr(13) & "Please rerun the program and then appropriate selections for the program to continue."
MsgBox prompt:=msg1, Buttons:=vbCritical + vbOKOnly, Title:="Selection Error"
End
Else
Dim userSelectedLocation As String
userSelectedLocation = .SelectedItems.Item(1)
If Right(userSelectedLocation, 1) <> "\" Then
userSelectedLocation = userSelectedLocation & "\"
End If
End If
End With
Set fd = Nothing
msg1 = "Please select the file(s) to be unzipped in the next step!"
MsgBox prompt:=msg1, Buttons:=vbCritical + vbOKOnly, Title:="Select 'Select Files Tobe Unzipped"
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
.InitialView = msoFileDialogViewDetails
.InitialFileName = "C:\"
.Show
If .SelectedItems.Count = 0 Then
msg1 = "No selection has been made"
msg1 = msg1 & Chr(10) & Chr(10) & Chr(13) & "Terminating Program Now"
msg1 = msg1 & Chr(10) & Chr(10) & Chr(13) & "Please rerun the program and then appropriate selections for the program to continue."
MsgBox prompt:=msg1, Buttons:=vbCritical + vbOKOnly, Title:="Selection Error"
End
Else
reportFilesCount = .SelectedItems.Count
countWrkg = countStart
countFinal = reportFilesCount
startTime = Timer
Do While countWrkg <= countFinal
Debug.Print .SelectedItems.Item(countWrkg)
Application.StatusBar = "Unzipping File " & countWrkg & " of " & countFinal & " - " & .SelectedItems.Item(countWrkg)

Dim strTargetFolder As String, strZippedFileName As String
strZippedFileName = .SelectedItems.Item(countWrkg)
strTargetFolder = userSelectedLocation

'Code for version 2 - currently commented out
' Dim objshell As Object
' Set objshell = CreateObject("Shell.Application")
' objshell.Namespace(strTargetFolder).CopyHere objshell.Namespace(strZippedFileName).Items


'Code for version 1
Const PATHWINZIP As String = "C:\progra~1\winzip\"
Dim targetFolder As Folder, zippedFileName As File
Dim ShellStr As String
Set fso = CreateObject("Scripting.FileSystemObject")
fso.GetFile (strZippedFileName)
fso.GetFolder (userSelectedLocation)
ShellStr = PATHWINZIP & "Winzip32 -min -e -o" & " " & Chr(34) & strZippedFileName & Chr(34) & " " & Chr(34) & strTargetFolder & Chr(34)
Shell ShellStr, vbHide


countWrkg = countWrkg + 1
Loop
finishTime = Timer
End If
End With
Set fd = Nothing
Application.ScreenUpdating = True
Application.StatusBar = False
totalTime = Application.WorksheetFunction.RoundUp((finishTime - startTime) / 60, 0)
msg1 = "Process Completed in " & totalTime & "minute(s)!"
MsgBox prompt:=msg1, Buttons:=vbInformation + vbOKOnly, Title:="Process Complete"
End Sub
[/vba]

This code when run "As Is" works but open an Explorer window for each file unzipped. So I end up with multiple open Explorer window which then need to be closed manually. Can this be run such that no additional Explorer window get opened. It doesn't matter whether the "vbHide" is commented out or not.

The other version using the Shell application (code for which is commented out and is just above the code for this version) comes up with the following error :-

"Object variable or With Block variable not set"


Any suggestions to address both of these issues?



Best regards



Deepak

[Dave]

HTH. Dave
http://www.vbaexpress.com/forum/showthread.php?t=27095

[agarwaldvk]

Hi Dave


I have looked at the link that you posted but none of the posts cover the problem that I am encountering.

More specifically, this line

[VBA]
objshell.Namespace(strTargetFolder).CopyHere objshell.Namespace(strZippedFileName).Items
[/VBA]

in my VB code comes up with this error :-

"Object variable or With Block variable not set"


Is this to do with setting up some reference that I might not have done? I can't figure this one out.

The other version works except that it open another explorer window for each file unzipped. How do I get it to not open this window?


Any suggestions?



Deepak

[Dave]

'You just need to pass 2 strings.
'C:\FullPath\Filename.zip - the file to UNZIP
'C:\FullPath\ - folder to unzip to

[VBA]
strZippedFileName = .SelectedItems.Item(countWrkg)
strTargetFolder = userSelectedLocation
[/VBA]
trial this code (after the above code) for a file syntax check
[VBA]
msgbox strZippedFileName
msgbox strTargetFolder
[/VBA]
Note the .zip requirement and "\" requirement. Dave

[agarwaldvk]

Hi Dave


Here is the screen dump of what happens when the version (using Namespace) is used. The content of the variables for file name and the folder are shown in the attached file : they both the .zip and "\" as required. I have also attached the error message that I get.

Do you not believe that it might have something to do with the References that it might need and I haven't included it/them?



Best regards



Deepak

[Dave]

Trial using Option Explicit and Dim your folder and file variables as strings. Please post the code you're using without the winzip part. Dave

[Aussiebear]

Will it work if you remove the "New" from the following line?
[vba]Dim fso As New FileSystemObject[/vba]

And as I understand it you need to "set" the fso as well