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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <rondebruin@xxxxxxxxxxxx> wrote in message news:%23aES3A8tJHA.4488@xxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message news:ufdmJ%237tJHA.4980@xxxxxxxxxxxxxxxxxxxxxxx
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" <petersod@xxxxxxxxxxxxxxxx> wrote in message news:49DBA5CC.290B4BF7@xxxxxxxxxxxxxxxxxxx
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-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
> >
> > "Joe D" <jdrzal@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:161E6BB2-CB4A-4815-A3AA-375704ABDF14@xxxxxxxxxxxxxxxx
> > > 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" <petersod@xxxxxxxxxxxxxxxx> wrote in message news:49D2C945.5E9903BA@xxxxxxxxxxxxxxxxxxx
> > >> > > 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
> > >> > >>
> > >> > >> "Dave Peterson" wrote:
> > >> > >>
> > >> > >> > 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
> > >> > >> > >
> > >> > >> > > "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
.

Relevant Pages