Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is waiting
until the number of files in the zip matches the number in the folder. This
will never happen as there's only 1 file in the folder and more than that in
the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the files,
delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the CopyHere operation or that your VBA
code will be notified that the operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is waiting
until the number of files in the zip matches the number in the folder. This
will never happen as there's only 1 file in the folder and more than that in
the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the files,
delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be able
to persuade my employer to buy a copy of WinZip, but that still doesn't
really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention. If
only I could delete the extracted file from the zip (this doesn't appear to
be possible with Windows zip), then I could probably look to see if the file
exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip folder
(only working with normal folders).
Also there is no possibility to avoid that someone can cancel the CopyHere
operation or that your VBA code will be notified that the operation has
been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder and
more than that in the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the
files, delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be able
to persuade my employer to buy a copy of WinZip, but that still doesn't
really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention. If
only I could delete the extracted file from the zip (this doesn't appear to
be possible with Windows zip), then I could probably look to see if the file
exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip folder
(only working with normal folders).
Also there is no possibility to avoid that someone can cancel the CopyHere
operation or that your VBA code will be notified that the operation has
been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder and
more than that in the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the
files, delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be able
to persuade my employer to buy a copy of WinZip, but that still doesn't
really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention. If
only I could delete the extracted file from the zip (this doesn't appear to
be possible with Windows zip), then I could probably look to see if the file
exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip folder
(only working with normal folders).
Also there is no possibility to avoid that someone can cancel the CopyHere
operation or that your VBA code will be notified that the operation has
been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder and
more than that in the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the
files, delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Thank you, Ron. I was thinking that MoveHere might be a possibility, but
haven't had the time to test it. If that works, then I can use wait until
count = count + extracted files.

Something like:

a = oApp.NameSpace(FileNameZip).items.Count
b = oApp.NameSpace(FolderName).items.Count
Do Until a = a + b
Application.Wait (Now + TimeValue("0:00:01"))
Loop

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be
able to persuade my employer to buy a copy of WinZip, but that still
doesn't really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the
file.

1. I'm prompted that the zip already contains a copy of the file. Can
I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder
and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract all
the files, delete the zip, replace the file I need to, then recreate
the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be
able to persuade my employer to buy a copy of WinZip, but that still
doesn't really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the
file.

1. I'm prompted that the zip already contains a copy of the file. Can
I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder
and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract all
the files, delete the zip, replace the file I need to, then recreate
the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may be
able to persuade my employer to buy a copy of WinZip, but that still
doesn't really get round the issue of the time involved (which could be
considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA Kill
command

2 : If you use WinZip I have code on my site that give you more control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the
file.

1. I'm prompted that the zip already contains a copy of the file. Can
I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder
and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract all
the files, delete the zip, replace the file I need to, then recreate
the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Hi Ron

MoveHere placed the target file in the destination directory, but did not
remove the original from the zip. Could this be because you have WinZip
installed and this is being used?

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may
be able to persuade my employer to buy a copy of WinZip, but that
still doesn't really get round the issue of the time involved (which
could be considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA
Kill command

2 : If you use WinZip I have code on my site that give you more
control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how
2 problems with zipping. I'm using your "Sub
Zip_All_Files_in_Folder()" routine to zip a file to a zip which
already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file.
Can I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to
the Do...Loop and hangs up. I see now that this is because the code
is waiting until the number of files in the zip matches the number
in the folder. This will never happen as there's only 1 file in the
folder and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract
all the files, delete the zip, replace the file I need to, then
recreate the zip.

As this is to be done over a VPN connection, the time involved and
the chances of a problem with the connection increases greatly.

--
Ian
--

Tomorrow I have time Ian to look good

I will reply tomorrow evening



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

MoveHere placed the target file in the destination directory, but did not
remove the original from the zip. Could this be because you have WinZip
installed and this is being used?

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may
be able to persuade my employer to buy a copy of WinZip, but that
still doesn't really get round the issue of the time involved (which
could be considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA
Kill command

2 : If you use WinZip I have code on my site that give you more
control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how
2 problems with zipping. I'm using your "Sub
Zip_All_Files_in_Folder()" routine to zip a file to a zip which
already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file.
Can I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to
the Do...Loop and hangs up. I see now that this is because the code
is waiting until the number of files in the zip matches the number
in the folder. This will never happen as there's only 1 file in the
folder and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract
all the files, delete the zip, replace the file I need to, then
recreate the zip.

As this is to be done over a VPN connection, the time involved and
the chances of a problem with the connection increases greatly.

--
Ian
--

Fast test

Working OK here

Vista and O 2007
No WinZip installed (only 7-Zip)

Test it tomorrow on a Win XP sysyem

What do you use (Windows and Office) ?



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Tomorrow I have time Ian to look good

I will reply tomorrow evening



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

MoveHere placed the target file in the destination directory, but did not
remove the original from the zip. Could this be because you have WinZip
installed and this is being used?

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it is
diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I may
be able to persuade my employer to buy a copy of WinZip, but that
still doesn't really get round the issue of the time involved (which
could be considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual intervention.
If only I could delete the extracted file from the zip (this doesn't
appear to be possible with Windows zip), then I could probably look to
see if the file exists in the zip instead of counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters link)

But you can Delete the zip first before you create it with the VBA
Kill command

2 : If you use WinZip I have code on my site that give you more
control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how
2 problems with zipping. I'm using your "Sub
Zip_All_Files_in_Folder()" routine to zip a file to a zip which
already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file.
Can I automatically overwrite the existing file without the prompt
appearing?

2. Having asked for the file to be replaced, the code moves on to
the Do...Loop and hangs up. I see now that this is because the code
is waiting until the number of files in the zip matches the number
in the folder. This will never happen as there's only 1 file in the
folder and more than that in the zip.
Is there any way to detect that the compression is complete, rather
than counting files? The only alternative I can see is to extract
all the files, delete the zip, replace the file I need to, then
recreate the zip.

As this is to be done over a VPN connection, the time involved and
the chances of a problem with the connection increases greatly.

--
Ian
--

This is working OK in Vista with Office 2007 to replace ron.xlsm in the zip with ron.xlsm in the DefaultFilePath

Sub ReplaceFileInZipTest()
Dim oApp As Object
Dim fname
Dim FileName ' do not dim as string
Dim DefPath As String

'Browse to the Zip file
fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
MultiSelect:=False)
If fname = False Then
'do nothing
Else

'Folder where the file is that you want to replace
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If
' File Name that you want to replace
FileName = "ron.xlsm"

Set oApp = CreateObject("Shell.Application")

'Move FileName out of the zip file in the temp folder
oApp.Namespace(Environ("Temp")).MoveHere oApp.Namespace(fname).Items.Item(FileName)

'Delete FileName from the temp folder
On Error Resume Next
Kill Environ("Temp") & "\" & FileName

'Copy the FileName (new one) in the zip folder
oApp.Namespace(fname).CopyHere DefPath & FileName

On Error GoTo 0

End If
End Sub


But in Win Xp with Office 2003 the part that move the file out of the zip is not working
So you get a replace dialog

MS not support this code so we must look for a other solution

Test more this evening and post back




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is waiting
until the number of files in the zip matches the number in the folder. This
will never happen as there's only 1 file in the folder and more than that in
the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the files,
delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Hi Ian

Also working OK in Vista with O2003 (setup a VPC to test)

Try to find a workaround for Win XP and why MoveHere is not working
in Win XP for Zip folders.

If I have more info I will post it in this thread




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
This is working OK in Vista with Office 2007 to replace ron.xlsm in the zip with ron.xlsm in the DefaultFilePath

Sub ReplaceFileInZipTest()
Dim oApp As Object
Dim fname
Dim FileName ' do not dim as string
Dim DefPath As String

'Browse to the Zip file
fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
MultiSelect:=False)
If fname = False Then
'do nothing
Else

'Folder where the file is that you want to replace
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If
' File Name that you want to replace
FileName = "ron.xlsm"

Set oApp = CreateObject("Shell.Application")

'Move FileName out of the zip file in the temp folder
oApp.Namespace(Environ("Temp")).MoveHere oApp.Namespace(fname).Items.Item(FileName)

'Delete FileName from the temp folder
On Error Resume Next
Kill Environ("Temp") & "\" & FileName

'Copy the FileName (new one) in the zip folder
oApp.Namespace(fname).CopyHere DefPath & FileName

On Error GoTo 0

End If
End Sub


But in Win Xp with Office 2003 the part that move the file out of the zip is not working
So you get a replace dialog

MS not support this code so we must look for a other solution

Test more this evening and post back




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is waiting
until the number of files in the zip matches the number in the folder. This
will never happen as there's only 1 file in the folder and more than that in
the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the files,
delete the zip, replace the file I need to, then recreate the zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Hi Ron

I'm using XP (SP2) and Office 2k (SP3).

--
Ian
--
"Ron de Bruin" wrote in message
...
Fast test

Working OK here

Vista and O 2007
No WinZip installed (only 7-Zip)

Test it tomorrow on a Win XP sysyem

What do you use (Windows and Office) ?



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
Tomorrow I have time Ian to look good

I will reply tomorrow evening



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

MoveHere placed the target file in the destination directory, but did
not remove the original from the zip. Could this be because you have
WinZip installed and this is being used?

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it
is diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I
may be able to persuade my employer to buy a copy of WinZip, but
that still doesn't really get round the issue of the time involved
(which could be considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual
intervention. If only I could delete the extracted file from the
zip (this doesn't appear to be possible with Windows zip), then I
could probably look to see if the file exists in the zip instead of
counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters
link)

But you can Delete the zip first before you create it with the VBA
Kill command

2 : If you use WinZip I have code on my site that give you more
control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now
how 2 problems with zipping. I'm using your "Sub
Zip_All_Files_in_Folder()" routine to zip a file to a zip which
already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file.
Can I automatically overwrite the existing file without the
prompt appearing?

2. Having asked for the file to be replaced, the code moves on to
the Do...Loop and hangs up. I see now that this is because the
code is waiting until the number of files in the zip matches the
number in the folder. This will never happen as there's only 1
file in the folder and more than that in the zip.
Is there any way to detect that the compression is complete,
rather than counting files? The only alternative I can see is to
extract all the files, delete the zip, replace the file I need
to, then recreate the zip.

As this is to be done over a VPN connection, the time involved
and the chances of a problem with the connection increases
greatly.

--
Ian
--

It's looking like I'll have to unzip all the files, replace the file(s) I
need to, then zip everything up again. Not a huge problem, except for the
time involved over a VPN connection.

I've not actually tried this over the VPN connection so I may be worrying
about nothing.

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

Also working OK in Vista with O2003 (setup a VPC to test)

Try to find a workaround for Win XP and why MoveHere is not working
in Win XP for Zip folders.

If I have more info I will post it in this thread




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
This is working OK in Vista with Office 2007 to replace ron.xlsm in the
zip with ron.xlsm in the DefaultFilePath

Sub ReplaceFileInZipTest()
Dim oApp As Object
Dim fname
Dim FileName ' do not dim as string
Dim DefPath As String

'Browse to the Zip file
fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip),
*.zip", _
MultiSelect:=False)
If fname = False Then
'do nothing
Else

'Folder where the file is that you want to replace
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If
' File Name that you want to replace
FileName = "ron.xlsm"

Set oApp = CreateObject("Shell.Application")

'Move FileName out of the zip file in the temp folder
oApp.Namespace(Environ("Temp")).MoveHere
oApp.Namespace(fname).Items.Item(FileName)

'Delete FileName from the temp folder
On Error Resume Next
Kill Environ("Temp") & "\" & FileName

'Copy the FileName (new one) in the zip folder
oApp.Namespace(fname).CopyHere DefPath & FileName

On Error GoTo 0

End If
End Sub


But in Win Xp with Office 2003 the part that move the file out of the zip
is not working
So you get a replace dialog

MS not support this code so we must look for a other solution

Test more this evening and post back




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now how 2
problems with zipping. I'm using your "Sub Zip_All_Files_in_Folder()"
routine to zip a file to a zip which already contains a copy of the
file.

1. I'm prompted that the zip already contains a copy of the file. Can I
automatically overwrite the existing file without the prompt appearing?

2. Having asked for the file to be replaced, the code moves on to the
Do...Loop and hangs up. I see now that this is because the code is
waiting until the number of files in the zip matches the number in the
folder. This will never happen as there's only 1 file in the folder and
more than that in the zip.
Is there any way to detect that the compression is complete, rather than
counting files? The only alternative I can see is to extract all the
files, delete the zip, replace the file I need to, then recreate the
zip.

As this is to be done over a VPN connection, the time involved and the
chances of a problem with the connection increases greatly.

--
Ian
--

Working OK in Vista but not in win XP
I am trying to find out why ?

If I know more I post back



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message ...
Hi Ron

I'm using XP (SP2) and Office 2k (SP3).

--
Ian
--
"Ron de Bruin" wrote in message
...
Fast test

Working OK here

Vista and O 2007
No WinZip installed (only 7-Zip)

Test it tomorrow on a Win XP sysyem

What do you use (Windows and Office) ?



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
Tomorrow I have time Ian to look good

I will reply tomorrow evening



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

MoveHere placed the target file in the destination directory, but did
not remove the original from the zip. Could this be because you have
WinZip installed and this is being used?

--
Ian
--
"Ron de Bruin" wrote in message
...
Hi Ian

MoveHere was working in my test yesterday

Will play with it this weekend


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron. MoveHere still leaves the original file in the zip (ie it's no
different to Copy Here)

I tried to Kill the file in the zip but it came up with no file found.

Looks like I'm on to a loser here.

Thanks for trying.

--
Ian
--
"Ron de Bruin" wrote in message
...
Btw: You can move it

oApp.Namespace(FileNameFolder).MoveHere
oApp.Namespace(fname).Items.Item("ron.xlsm")

I will try to delete it in a zip file this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message
...
I will play with it this evening and see if I can find a way

If you unzip first then you can do what you want but in the zip it
is diffecult I think ?

Unzip
Do what you want in the normal folder
Zip it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Thanks, Ron. I'm afraid I'm using the Windows XP zip facility. I
may be able to persuade my employer to buy a copy of WinZip, but
that still doesn't really get round the issue of the time involved
(which could be considerable) and the dangers that introduces.

I think in this case I'll have to settle for some manual
intervention. If only I could delete the extracted file from the
zip (this doesn't appear to be possible with Windows zip), then I
could probably look to see if the file exists in the zip instead of
counting the files.

Thanks for your help.

--
Ian
--
"Ron de Bruin" wrote in message
...
1: The WinZip code on my site can do that (see the parameters
link)

But you can Delete the zip first before you create it with the VBA
Kill command

2 : If you use WinZip I have code on my site that give you more
control

See the note on the page

Warning :
It is not possible to hide the copy dialog when you copy to a zip
folder (only working with normal folders).
Also there is no possibility to avoid that someone can cancel the
CopyHere operation or that your VBA code will be notified that the
operation has been cancelled.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"IanC" wrote in message
...
Hi Ron

Many thanks for your help with unzipping a file with VBA. I now
how 2 problems with zipping. I'm using your "Sub
Zip_All_Files_in_Folder()" routine to zip a file to a zip which
already contains a copy of the file.

1. I'm prompted that the zip already contains a copy of the file.
Can I automatically overwrite the existing file without the
prompt appearing?

2. Having asked for the file to be replaced, the code moves on to
the Do...Loop and hangs up. I see now that this is because the
code is waiting until the number of files in the zip matches the
number in the folder. This will never happen as there's only 1
file in the folder and more than that in the zip.
Is there any way to detect that the compression is complete,
rather than counting files? The only alternative I can see is to
extract all the files, delete the zip, replace the file I need
to, then recreate the zip.

As this is to be done over a VPN connection, the time involved
and the chances of a problem with the connection increases
greatly.

--
Ian
--