how do i open a zip file using a macro
 

i would like to open a zip file when a command button is pressed any ideas

how do i open a zip file using a macro
 

http://www.rondebruin.nl/tips.htm

near the bottom are three links for information on working with zip files.

--
Regards,
Tom Ogilvy

"pete the greek" wrote in message
...
i would like to open a zip file when a command button is pressed any ideas

how do i open a zip file using a macro
 

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"pete the greek" wrote in message
...
i would like to open a zip file when a command button is pressed any ideas

how do i open a zip file using a macro
 

Hi Ron,

Finally a solution on using XPs compressed folders!

However McAfee VirusScan was complaining on your NewZip procedure.
Apparently FSO's CreateTextFile is suspect!

Here's my alternative, which McAfee allows without complaints
(shorter and simpler too)

Sub NewZip(sPath)
'Create empty Zip File
If Len(Dir(sPath)) 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

Sub newziptest() '
Call NewZip("c:\test.zip")
End Sub


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm

how do i open a zip file using a macro
 

Hi keepITcool

McAfee
A few years ago McAfee start deleting my mail in my inbox.
Since then I use another program, first Norton and now Trend Micro.

I test your code and try to update the site this evening

Thanks for the feedback


--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message .com...
Hi Ron,

Finally a solution on using XPs compressed folders!

However McAfee VirusScan was complaining on your NewZip procedure.
Apparently FSO's CreateTextFile is suspect!

Here's my alternative, which McAfee allows without complaints
(shorter and simpler too)

Sub NewZip(sPath)
'Create empty Zip File
If Len(Dir(sPath)) 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

Sub newziptest() '
Call NewZip("c:\test.zip")
End Sub


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm

how do i open a zip file using a macro
 

Hi keepITcool

You code example is working Ok and is shorter and simpler .

BTW : do you have better idea about the wait code on my site
http://www.rondebruin.nl/windowsxpzip.htm#Wait

I test a few other things but this is the only thing that is working for me
(also with big Excel files 5-10 mb)



--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message .com...
Hi Ron,

Finally a solution on using XPs compressed folders!

However McAfee VirusScan was complaining on your NewZip procedure.
Apparently FSO's CreateTextFile is suspect!

Here's my alternative, which McAfee allows without complaints
(shorter and simpler too)

Sub NewZip(sPath)
'Create empty Zip File
If Len(Dir(sPath)) 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

Sub newziptest() '
Call NewZip("c:\test.zip")
End Sub


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm

how do i open a zip file using a macro
 

Ron..

the Wait solution...Counting!

I also noted that the NameSpace method
fails if you pass it a string variable....


Sub Zip_All_Files_in_Folder()

Dim sDefPath$, sDate$
Dim vZipPath, vFldPath 'MUST be Variants!
Dim lFldCount&
Dim oApp As Object
Dim oZip As Object
Dim oFld As Object

vFldPath = "e:\testje\" '<< Change

sDefPath = Application.DefaultFilePath
sDefPath = "e:\"

If Right(sDefPath, 1) < "\" Then
sDefPath = sDefPath & "\"
End If

sDate = Format(Now, " dd-mmm-yy h-mm-ss")
vZipPath = sDefPath & "MyFilesZip " & sDate & ".zip"

'Create empty Zip File
Call NewZip(vZipPath)

Set oApp = CreateObject("Shell.Application")
Set oFld = oApp.Namespace(vFldPath)
Set oZip = oApp.Namespace(vZipPath)

'Get the count of files to be compressed
lFldCount = oFld.Items.Count

'Copy the files to the compressed folder
oZip.CopyHere oFld.Items

'Keep script waiting until Compressing is done
Do Until oZip.Items.Count = lFldCount
Application.Wait (Now + TimeValue("0:00:01"))
Loop


MsgBox "You find the zipfile he" & vbLf & vZipPath
Set oFld = Nothing
Set oZip = Nothing
Set oApp = Nothing
End Sub



--






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi keepITcool

You code example is working Ok and is shorter and simpler .

BTW : do you have better idea about the wait code on my site
http://www.rondebruin.nl/windowsxpzip.htm#Wait

I test a few other things but this is the only thing that is working
for me (also with big Excel files 5-10 mb)

how do i open a zip file using a macro
 

Good suggestion

I change the NewZip macro to yours on the site

Thanks



--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message .com...

Ron..

the Wait solution...Counting!

I also noted that the NameSpace method
fails if you pass it a string variable....


Sub Zip_All_Files_in_Folder()

Dim sDefPath$, sDate$
Dim vZipPath, vFldPath 'MUST be Variants!
Dim lFldCount&
Dim oApp As Object
Dim oZip As Object
Dim oFld As Object

vFldPath = "e:\testje\" '<< Change

sDefPath = Application.DefaultFilePath
sDefPath = "e:\"

If Right(sDefPath, 1) < "\" Then
sDefPath = sDefPath & "\"
End If

sDate = Format(Now, " dd-mmm-yy h-mm-ss")
vZipPath = sDefPath & "MyFilesZip " & sDate & ".zip"

'Create empty Zip File
Call NewZip(vZipPath)

Set oApp = CreateObject("Shell.Application")
Set oFld = oApp.Namespace(vFldPath)
Set oZip = oApp.Namespace(vZipPath)

'Get the count of files to be compressed
lFldCount = oFld.Items.Count

'Copy the files to the compressed folder
oZip.CopyHere oFld.Items

'Keep script waiting until Compressing is done
Do Until oZip.Items.Count = lFldCount
Application.Wait (Now + TimeValue("0:00:01"))
Loop


MsgBox "You find the zipfile he" & vbLf & vZipPath
Set oFld = Nothing
Set oZip = Nothing
Set oApp = Nothing
End Sub



--






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi keepITcool

You code example is working Ok and is shorter and simpler .

BTW : do you have better idea about the wait code on my site
http://www.rondebruin.nl/windowsxpzip.htm#Wait

I test a few other things but this is the only thing that is working
for me (also with big Excel files 5-10 mb)

how do i open a zip file using a macro
 

cheer folks i look forwrd to trying these out

"Tom Ogilvy" wrote:

http://www.rondebruin.nl/tips.htm

near the bottom are three links for information on working with zip files.

--
Regards,
Tom Ogilvy

"pete the greek" wrote in message
...
i would like to open a zip file when a command button is pressed any ideas

how do i open a zip file using a macro
 

I can't tell you how much I apprecaite your help on zipping files. I will be
sending these out to my locations tomorrow.
Starting the first of the year, all the locations will be sending in their
reports (hopefully zipped using your code) by email. I use Outlook but do not
have WinZip installed.
I looked at your code to use the XP unzip, but I think I need a little bit
more.
My emails with the attached reports come into a specific folder.
I need to open the attached zip files, open the worksheet, copy data from 17
cells on sheet 2 into a "master file" on my computer, and then save the zip
file to an archive folder (static location without changing the file name).
What's the possibility of writing one macro that will do all of that? I have
one now that does everything once I open the attached file. The zipped file
adds a wrinkle and I would like to further automate it to look in the
specified inbox in Outlook and if it finds any email with a zipped
attachement (can partially test the filename) run the whole procedure.

Thanks much for all your help so far!

"Ron de Bruin" wrote:

Hi keepITcool

McAfee
A few years ago McAfee start deleting my mail in my inbox.
Since then I use another program, first Norton and now Trend Micro.

I test your code and try to update the site this evening

Thanks for the feedback


--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message .com...
Hi Ron,

Finally a solution on using XPs compressed folders!

However McAfee VirusScan was complaining on your NewZip procedure.
Apparently FSO's CreateTextFile is suspect!

Here's my alternative, which McAfee allows without complaints
(shorter and simpler too)

Sub NewZip(sPath)
'Create empty Zip File
If Len(Dir(sPath)) 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

Sub newziptest() '
Call NewZip("c:\test.zip")
End Sub


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm

how do i open a zip file using a macro
 

Look on Dick's site
http://www.dicks-clicks.com/excel/olRetrieving.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"David" wrote in message ...
I can't tell you how much I apprecaite your help on zipping files. I will be
sending these out to my locations tomorrow.
Starting the first of the year, all the locations will be sending in their
reports (hopefully zipped using your code) by email. I use Outlook but do not
have WinZip installed.
I looked at your code to use the XP unzip, but I think I need a little bit
more.
My emails with the attached reports come into a specific folder.
I need to open the attached zip files, open the worksheet, copy data from 17
cells on sheet 2 into a "master file" on my computer, and then save the zip
file to an archive folder (static location without changing the file name).
What's the possibility of writing one macro that will do all of that? I have
one now that does everything once I open the attached file. The zipped file
adds a wrinkle and I would like to further automate it to look in the
specified inbox in Outlook and if it finds any email with a zipped
attachement (can partially test the filename) run the whole procedure.

Thanks much for all your help so far!

"Ron de Bruin" wrote:

Hi keepITcool

McAfee
A few years ago McAfee start deleting my mail in my inbox.
Since then I use another program, first Norton and now Trend Micro.

I test your code and try to update the site this evening

Thanks for the feedback


--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message .com...
Hi Ron,

Finally a solution on using XPs compressed folders!

However McAfee VirusScan was complaining on your NewZip procedure.
Apparently FSO's CreateTextFile is suspect!

Here's my alternative, which McAfee allows without complaints
(shorter and simpler too)

Sub NewZip(sPath)
'Create empty Zip File
If Len(Dir(sPath)) 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

Sub newziptest() '
Call NewZip("c:\test.zip")
End Sub


HTH

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote :

Hi Pete

If you are a WinZip user try
http://www.rondebruin.nl/unzip.htm

If you use Windows Xp zip program
http://www.rondebruin.nl/windowsxpzip.htm