On Sep 7, 10:22 am, Tom Ogilvy
wrote:
I suspect the problem is that you have a space in the name of your file.

Try something like changing

Set objApp = CreateObject("Shell.Application")
objApp.Namespace(strFileNameZip).CopyHere strFileNameXls

to

Set objApp = CreateObject("Shell.Application")
objApp.Namespace("'" & strFileNameZip & "'").CopyHere _
"'" & strFileNameXls & "'"

--
Regards,
Tom Ogilvy



"Mark Driscol" wrote:
Excel 2003, Windows XP SP2

I have copied and modified some code from Ron de Bruin's website. I
am trying to zip a file, but the last line of the code below results
in the following error: "Run-time error '91': Object variable or With
block variable not set". Can anyone see what I am doing wrong? I
have searched this newsgroup but haven't found a post that addresses
this situation. Thanks in advance.

Sub CompressFile(ByVal wkbBook1 As Workbook)

Dim strFileNameZip As String
Dim strFileNameXls As String
Dim strWBPath As String
Dim objApp As Object

strWBPath = wkbBook1.path
If Right(strWBPath, 1) < "\" Then strWBPath = strWBPath & "\"

' Create temporary Excel and .zip file names
strFileNameXls = strWBPath & Left(wkbBook1.Name,
Len(wkbBook1.Name) - 4) _
& " (" & Format(Now, "yyyy-mm-dd, h-mm-ss ampm") & ").xls"
strFileNameZip = strWBPath & Left(wkbBook1.Name,
Len(wkbBook1.Name) - 4) & ".zip"

' Save a copy of workbook
wkbBook1.SaveCopyAs Filename:=strFileNameXls

' Kill any old copies of .zip file
If Len(Dir(strFileNameZip)) < 0 Then Kill strFileNameZip

' Create empty .zip file
Open strFileNameZip For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1

' Copy the file into the compressed folder
Set objApp = CreateObject("Shell.Application")
objApp.Namespace(strFileNameZip).CopyHere strFileNameXls

' Rest of code continues after this point- Hide quoted text -

- Show quoted text -

Thank you, Tom. I did try that but it gives me the same error
message. I took the code directly from this website, so I'm expecting
it should work OK. I did see Ron's warning about the copy dialog and
the CopyHere method, but haven't had a dialog box show up. Any other
suggestions would be appreciated.

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