| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am trying to open a ".gz" file with a macro in Excel 2003.
Any help would be appreciated.
-- Joe D |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
..GZ is a gzip compressed file format. (http://www.gzip.org/) You need to
extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Will this help http://www.math.mcmaster.ca/~grasselli/john.pdf
If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Sorry the below is meant for a different post.
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Will this help http://www.math.mcmaster.ca/~grasselli/john.pdf If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
I have the 7zip software and can manuially open the files. I am looking for
code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Ron de Bruin has some sample code for zipping files he
http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Or if you want to use the Default zip program see
http://www.rondebruin.nl/windowsxpunzip.htm Never test it for zip files with a gz extension -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
I have tried modifying Ron's code but unfortunately have not been successful.
I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Without knowing what failed, I'm gonna make a guess that you didn't have the
commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
The commandline version exe name is
7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
I think you're right, Ron.
7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Hi Dave
I see if I can find more info about the parameters of 7zip this week and when I update the WinZip page add also the example for 7zip. Btw: 7zip is a great program to open Office 2007 files as zip container in one click -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Thanks Dave and Ron. I think will do waht I need. I appreciate the help.
-- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
In the Help file you can find all the parameters that you can use Joe
You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Another way to see the commandline parms is to:
shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you
to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Try to add q
ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Try to add q
Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Got it
When I test it I not tell the code if I want to overwrite the files if they exist for example -aoa ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -aoa" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
When I test it I not tell the code if I want to overwrite the files if they exist for example
This is the problem but The code I posted is not working and I am to tired now to test more If Dave not give you a answer I will reply tomorrow after work. Good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it When I test it I not tell the code if I want to overwrite the files if they exist for example -aoa ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -aoa" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
This is realy the last mail <g
You can use -y ShellStr = Path7Z & "7z.exe e -y" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... When I test it I not tell the code if I want to overwrite the files if they exist for example This is the problem but The code I posted is not working and I am to tired now to test more If Dave not give you a answer I will reply tomorrow after work. Good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it When I test it I not tell the code if I want to overwrite the files if they exist for example -aoa ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -aoa" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Ron,
I didn't test this, but I don't recall having any problem with 7za.exe (different from 7z.exe) and using shellandwait. It's been a long time, but it may be worth a test for the OP. Ron de Bruin wrote: This is realy the last mail <g You can use -y ShellStr = Path7Z & "7z.exe e -y" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... When I test it I not tell the code if I want to overwrite the files if they exist for example This is the problem but The code I posted is not working and I am to tired now to test more If Dave not give you a answer I will reply tomorrow after work. Good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it When I test it I not tell the code if I want to overwrite the files if they exist for example -aoa ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -aoa" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Hi Joe
Play with it for a few minutes You can use -y to overwrite the files if they exist ShellStr = Path7Z & "7z.exe e -y" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Or use one of the other options below like this ShellStr = Path7Z & "7z.exe e -aou" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Any help would be appreciated. -- Joe D |
|
#25
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
Thanks -
The "-y" did it. I appreciate all the help. Joe -- Joe D "Ron de Bruin" wrote: This is realy the last mail <g You can use -y ShellStr = Path7Z & "7z.exe e -y" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... When I test it I not tell the code if I want to overwrite the files if they exist for example This is the problem but The code I posted is not working and I am to tired now to test more If Dave not give you a answer I will reply tomorrow after work. Good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Got it When I test it I not tell the code if I want to overwrite the files if they exist for example -aoa ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -aoa" & Chr(34) & FolderName & Chr(34) -aoa Overwrite All existing files without prompt. -aos Skip extracting of existing files. -aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt). -aot auto rename existing file (for example, name.txt will be renamed to name_1.txt). -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q Not working correct I see the same problem as the OP Will play with this week to find out wat is going on -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try to add q ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) & " q" I will start testing and create a new page for it this week -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Try it without the vbhide stuff. vbNormalFocus to see if 7z is waiting for you to do something. But I'm not sure why ShellAndWait failed for you. I don't recall having this problem when I used it. Joe D wrote: Dave and Ron, One last issue to resolve which I am sure you will finfd easy to address. When I run this I end up with multiple versions of 7S running in my control panel processes. The code I am using to actually unzip the files is below. I originally had the SHELLANDWAIT, but this gave me an error (I am using excel 2000 and 2003). Is there a command to close or end the 7s application each time I run it. Thanks 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7z.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) Shell ShellStr, vbHide -- Joe D "Dave Peterson" wrote: Another way to see the commandline parms is to: shell to DOS (windows start|run|type: CMD and hit enter) Then traverse to that folder that contains 7z.exe and type 7z and hit enter. I see this: C:\Program Files\7-Zip7z 7-Zip 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03 Usage: 7z <command [<switches...] <archive_name [<file_names...] [<@listfiles...] <Commands a: Add files to archive b: Benchmark d: Delete files from archive e: Extract files from archive (without using directory names) l: List contents of archive t: Test integrity of archive u: Update files to archive x: eXtract files with full paths <Switches -ai[r[-|0]]{@listfile|!wildcard}: Include archives -ax[r[-|0]]{@listfile|!wildcard}: eXclude archives -bd: Disable percentage indicator -i[r[-|0]]{@listfile|!wildcard}: Include filenames -m{Parameters}: set compression Method -o{Directory}: set Output directory -p{Password}: set Password -r[-|0]: Recurse subdirectories -scs{UTF-8 | WIN | DOS}: set charset for list files -sfx[{name}]: Create SFX archive -si[{name}]: read data from stdin -slt: show technical information for l (List) command -so: write data to stdout -ssc[-]: set sensitive case mode -ssw: compress shared files -t{Type}: Set type of archive -v{Size}[b|k|m|g]: Create volumes -u[-][p#][q#][r#][x#][y#][z#][!newArchiveName]: Update options -w[{path}]: assign Work directory. Empty path means a temporary directory -x[r[-|0]]]{@listfile|!wildcard}: eXclude filenames -y: assume Yes on all queries Then type exit and hit enter to close that command window. Ron de Bruin wrote: In the Help file you can find all the parameters that you can use Joe You can find the chm in this folder C:\program files\7-zip\ Good luck -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... Thanks Dave and Ron. I think will do waht I need. I appreciate the help. -- Joe D "Dave Peterson" wrote: I think you're right, Ron. 7za.exe is a different application that does the same thing. 7z.exe is bundled(?) with the 7zip and serves as the commandline version of 7zip. (Maybe we're both right, but yours is simpler--so you win! <vbg) Ron de Bruin wrote: The commandline version exe name is 7z.exe instead of 7za.exe On my machine(version 4.65) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dave Peterson" wrote in message ... Without knowing what failed, I'm gonna make a guess that you didn't have the commandline version of 7z (7za.exe). You can find it he http://sourceforge.net/project/downl...ip&use_mirror= (I haven't used it in a long time, but I believe I went searching for this file when I needed to do this, too.) Anyway, this worked for me: Option Explicit Sub UnZip_GZFile() Dim Path7Z As String, FileNameGZ As String Dim ShellStr As String, FolderName As String Path7Z = "C:\program files\7-zip\" 'This will check if this is the path where 7za is installed. If Dir(Path7Z & "7za.exe") = "" Then MsgBox "Please find your copy of 7za.exe and try again" Exit Sub End If FileNameGZ = "C:\Data\Test.gz" FolderName = "C:\Data\" 'Unzip the zip file in the folder FolderName ShellStr = Path7Z & "7za.exe e" _ & " " & Chr(34) & FileNameGZ & Chr(34) _ & " -o" & Chr(34) & FolderName & Chr(34) ShellAndWait ShellStr, vbHide MsgBox "Look in " & FolderName & " for extracted files" End Sub Remember to include Ron's ShellAndWait code, too. And after your data is extracted, you can use Dir() to look for what you want (and open it???). Joe D wrote: I have tried modifying Ron's code but unfortunately have not been successful. I can get it to find the gz files, but not the items inside the files. Any suggestions would be appreciated. Thanks -- Joe D "Dave Peterson" wrote: Ron de Bruin has some sample code for zipping files he http://www.rondebruin.nl/unzip.htm You can this as the basis for your code--but he does use winzip. You'll have some minor modifications to do. Joe D wrote: I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria |
|
#26
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to open a ".gz" file with a macro in Excel 2003.
FYI
I add the Zip page today for 7-zip http://www.rondebruin.nl/7zipwithexcel.htm Next week I will create the Unzip page -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joe D" wrote in message ... I have the 7zip software and can manuially open the files. I am looking for code to uncompress them with VBA on a windows machine. Any ideas on this?? -- Joe D "Jacob Skaria" wrote: .GZ is a gzip compressed file format. (http://www.gzip.org/) You need to extract the excel file from this. If this post helps click Yes --------------- Jacob Skaria "Joe D" wrote: Any help would be appreciated. -- Joe D |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| " %20" signs in file name when open attachement in excel | Excel Discussion (Misc queries) | |||
| "Cannot access read-only document" (I get this error when I try to open an Excel file) | Excel Discussion (Misc queries) | |||
| "Cannot access read-only document" (I get this error when I try to open an Excel file) | Setting up and Configuration of Excel | |||
| Excel 2002/2003 can not open xls file says "may be read only" | Excel Discussion (Misc queries) | |||
Linear Mode
