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