I am trying to open a ".gz" file with a macro in Excel 2003.

[Joe D]

Any help would be appreciated.

 

[Jacob Skaria]

..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]

Will this help http://www.math.mcmaster.ca/~grasselli/john.pdf

If this post helps click Yes

 

[Jacob Skaria]

Sorry the below is meant for a different post.

 

[Joe D]

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??

 

[Dave Peterson]

Ron de Bruin has some sample code for zipping files here:
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.

 

[Ron de Bruin]

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

 

[Joe D]

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

 

[Dave Peterson]

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 here:
http://sourceforge.net/project/downloading.php?groupname=sevenzip&filename=7za452.zip&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???).

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

 

[Ron de Bruin]

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]

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>)

The commandline version exe name is
7z.exe
instead of
7za.exe

On my machine(version 4.65)

 

[Ron de Bruin]

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

 

[Joe D]

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

 

[Ron de Bruin]

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

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

 

[Dave Peterson]

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-Zip>7z

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.

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

 

[Joe D]

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

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-Zip>7z

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.

 

[Dave Peterson]

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.

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

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-Zip>7z

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.

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

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

--
Joe D


:

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

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 here:
http://sourceforge.net/project/downloading.php?groupname=sevenzip&filename=7za452.zip&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

:

Ron de Bruin has some sample code for zipping files here:
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

:

.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


:

Any help would be appreciated.

 

[Ron de Bruin]

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

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.

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

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-Zip>7z

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

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

--
Joe D


:

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

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 here:
http://sourceforge.net/project/downloading.php?groupname=sevenzip&filename=7za452.zip&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

:

Ron de Bruin has some sample code for zipping files here:
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

:

.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


:

Any help would be appreciated.

 

[Ron de Bruin]

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

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

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.

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

:

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-Zip>7z

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

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

--
Joe D


:

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

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 here:
http://sourceforge.net/project/downloading.php?groupname=sevenzip&filename=7za452.zip&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

:

Ron de Bruin has some sample code for zipping files here:
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

:

.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


:

Any help would be appreciated.

 

[Ron de Bruin]

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

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

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

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

:

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-Zip>7z

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

Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

--
Joe D


:

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

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 here:
http://sourceforge.net/project/downloading.php?groupname=sevenzip&filename=7za452.zip&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

:

Ron de Bruin has some sample code for zipping files here:
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

:

.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


:

Any help would be appreciated.