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.

--
Ian
--

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
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.

--
Ian
--

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?

--
Ian
--
"Ron de Bruin" wrote in message
...
Maybe unzip the files and open it is a option
http://www.rondebruin.nl/windowsxpzip.htm

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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.

--
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 he " & 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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
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?

--
Ian
--
"Ron de Bruin" wrote in message
...
Maybe unzip the files and open it is a option
http://www.rondebruin.nl/windowsxpzip.htm

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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.

--
Ian
--

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

--
Ian
--
"Ron de Bruin" wrote in message
...
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 he " & 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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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?

--
Ian
--
"Ron de Bruin" wrote in message
...
Maybe unzip the files and open it is a option
http://www.rondebruin.nl/windowsxpzip.htm

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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.

--
Ian
--

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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

--
Ian
--
"Ron de Bruin" wrote in message
...
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 he " & 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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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?

--
Ian
--
"Ron de Bruin" wrote in message
...
Maybe unzip the files and open it is a option
http://www.rondebruin.nl/windowsxpzip.htm

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
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.

--
Ian
--