agarwaldvk
11-08-2011, 05:03 PM
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) :-
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
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
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) :-
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
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