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

Joe D · Mar 30, 2009

Any help would be appreciated.

Jacob Skaria · Mar 30, 2009

..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 · Mar 30, 2009

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

If this post helps click Yes

Jacob Skaria · Mar 30, 2009

Sorry the below is meant for a different post.

Joe D · Mar 31, 2009

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 · Mar 31, 2009

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 · Mar 31, 2009

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 · Apr 1, 2009

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 · Apr 1, 2009

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 said:
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 · Apr 2, 2009

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 · Apr 2, 2009

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 · Apr 2, 2009

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 · Apr 2, 2009

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

Ron de Bruin · Apr 2, 2009

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 said:
Thanks Dave and Ron. I think will do waht I need. I appreciate the help.

Dave Peterson · Apr 2, 2009

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 · Apr 7, 2009

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 said:
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 · Apr 7, 2009

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 said:
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 said:

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.

Click to expand...

Click to expand...

Ron de Bruin · Apr 7, 2009

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 said:
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 said:

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 said:

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.

Click to expand...

Click to expand...

Ron de Bruin · Apr 7, 2009

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 said:
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 said:

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 said:

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.

Click to expand...

Click to expand...

Ron de Bruin · Apr 7, 2009

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 said:
Try to add q

Click to expand...

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 said:

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 said:

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.

Click to expand...

Click to expand...