Open compressed file

[IanC]

Is it possible to open a file in a compressed folder using Excel VBA?

Windows happily sees a zip file as a folder, but Excel seems to react
differently.

Many thanks.

 

[Ron de Bruin]

Maybe unzip the files and open it is a option
http://www.rondebruin.nl/windowsxpzip.htm

See
http://www.rondebruin.nl/files/windowsxpunzip.txt

 

[IanC]

Thanks, Ron. That's a possibility. Ideally what I need to do is to extract a
specific file from the zip, rename it, then zip a replacement file. As all
this will be happening over a VPN, then I'd prefer to minimize the amount of
file manipulation required. Your routine Unzip2 is the closest for the unzip
part, but still involves unzipping everything.

Do you know of a way to specify a particular file to extract?

 

[Ron de Bruin]

Hi Ian

You can use this to get one file (ron.xlsm)

Sub Unzip1()
Dim FSO As Object
Dim oApp As Object
Dim fname
Dim FileNameFolder
Dim DefPath As String
Dim strDate As String

fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
MultiSelect:=False)
If fname = False Then
'do nothing
Else
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

strDate = Format(Now, " dd-mm-yy h-mm-ss")
FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

'Create normal folder
MkDir FileNameFolder

Set oApp = CreateObject("Shell.Application")
'Copy the files in the newly created folder
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(fname).Items.Item("ron.xlsm")

MsgBox "You find the files here: " & FileNameFolder
On Error Resume Next
Set FSO = CreateObject("scripting.filesystemobject")
FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True

Set oApp = Nothing
Set FSO = Nothing
End If
End Sub

 

[IanC]

Thanks Ron. I was sure it would have to be a modification to that line, but
I didn't know how.

--
Ian
--

Hi Ian

You can use this to get one file (ron.xlsm)

Sub Unzip1()
Dim FSO As Object
Dim oApp As Object
Dim fname
Dim FileNameFolder
Dim DefPath As String
Dim strDate As String

fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip),
*.zip", _
MultiSelect:=False)
If fname = False Then
'do nothing
Else
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

strDate = Format(Now, " dd-mm-yy h-mm-ss")
FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

'Create normal folder
MkDir FileNameFolder

Set oApp = CreateObject("Shell.Application")
'Copy the files in the newly created folder
oApp.Namespace(FileNameFolder).CopyHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

MsgBox "You find the files here: " & FileNameFolder
On Error Resume Next
Set FSO = CreateObject("scripting.filesystemobject")
FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True

Set oApp = Nothing
Set FSO = Nothing
End If
End Sub

 

[Ron de Bruin]

It is time that I update that page
Will add this tip also when I do the update