|
Open compressed file
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 -- |
Open compressed file
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 -- |
Open compressed file
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 -- |
Open compressed file
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 -- |
Open compressed file
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 -- |
Open compressed file
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 -- |
| All times are GMT +1. The time now is 07:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com