View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Mark Driscol[_3_] Mark Driscol[_3_] is offline
external usenet poster
 
Posts: 3
Default Windows XP zip problem

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