Backup and WinZip

[Jon.N]

The following code copies the current database and zips it up. However this does not work if the directory name has spaces in it, e.g. ...Documents and Settings\Desktop...

Dim fs
Dim strSource 'As String
Dim strTarget 'As String
Dim strLocation As String

‘MAKE COPY OF DATABASE

Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile strSource, strTarget
Set fs = Nothing


'ZIP COPY OF DATABASE

strLocation = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
strSource = CurrentDb.Name
strTarget = strLocation & "OffSite.mdb"

Call Shell("C:\Program Files\WinZip\WINZIP32.EXE -min -a -ex " & _
strLocation & "OffSite.zip" & " " & strTarget, vbHide)

I know many threads have been written on this topic already and I am sure I have trolled through them all but I cannot for the life of me see where I am going wrong.

As always all help is greatly appreciated.

 

[namliam]

How about filling the variables before using them??

Dim fs
Dim strSource As String
Dim strTarget As String
Dim strLocation As String

‘MAKE COPY OF DATABASE

strLocation = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
strSource = CurrentDb.Name
strTarget = strLocation & "OffSite.mdb"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.copyfile strSource, strTarget
Set fs = Nothing

'ZIP COPY OF DATABASE

Call Shell("C:\Program Files\WinZip\WINZIP32.EXE -min -a -ex " & _
strLocation & "OffSite.zip" & " " & strTarget, vbHide)

Also i am not quite sure you can copy the open database?!

Regards

 

[Jon.N]

Still having WinZip problems

Many thanks namliam, a silly mistake.

You can copy the open database, honest that bit does work. My problem is still with WinZip not reading the directory path correctly. This is the error message WinZip throws at me.

Action: Add (and replace) files Include subfolders: yes Save full path: no
Warning: name not matched: and
Warning: name not matched: Settings\jon\Desktop\OffSite.zip
Warning: name not matched: C:\Documents
Warning: name not matched: and
Warning: name not matched: Settings\jon\Desktop\OffSite.mdb
Error: No files were found for this action that match your criteria - nothing to do. (C:\Documents.zip)

Heas anyone else met this problem?

 

[namliam]

Common space problem, Solution: Use qoutes!

Call Shell("C:\Program Files\WinZip\WINZIP32.EXE -min -a -ex """ & _
strLocation & "OffSite.zip" & """ """ & strTarget & """", vbHide)

This will result in something like:

"C:\Program Files\WinZip\WINZIP32.EXE -min -a -ex "C:\Your Path\OffSite.zip" "C:\Your Original\Your file.mdb"

Regards

 

[Oldsoftboss]

Trowel through the code in the utility I designed some time ago. Looks for winzip, and if found, replaces the spaces with underscores, adds the date to the zip file. Needs a registered version of winzip.

May even save you time and do all you need.

Dave

 

[Jon.N]

Problem solved

I had tried quotes but I did not enter them correctly. A big thank you for your help.