Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Windows XP zip problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Windows XP zip problem
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Windows XP zip problem
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Windows XP zip problem
Hi Mark

If you look at the code on my site you see that I not dim as string.
Must be variant

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



--

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


"Mark Driscol" wrote in message ups.com...
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Windows XP zip problem
On Sep 7, 11:49 am, "Ron de Bruin" wrote:
Hi Mark

If you look at the code on my site you see that I not dim as string.
Must be variant

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Mark Driscol" wrote in oglegroups.com...
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- Hide quoted text -

- Show quoted text -

You are correct, Ron. Thank you for taking time to look at this.

Mark


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Windows XP zip problem
OK, it took forever to find the problem, but the problem appears to be that
you can't declare your

strFileNameXls and strFileNameZip as String (see Ron's code).

This worked for me:

Sub Test()
Dim wbkbook1 As Workbook
Set wbkbook1 = Workbooks("AA_Demo.xls")
CompressFile wbkbook1
End Sub
Sub CompressFile(ByVal wkbBook1 As Workbook)

Dim strFileNameZip
Dim strFileNameXls
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"
Debug.Print strFileNameXls, strFileNameZip
If Dir(strFileNameXls) < "" Then
Kill strFileNameXls
End If
' 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
On Error Resume Next
Do Until objApp.Namespace(strFileNameZip).Items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
MsgBox "Your Backup is saved he " & strFileNameZip
End Sub

--
Regards,
Tom Ogilvy


"Mark Driscol" wrote:

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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Windows XP zip problem
Hi Tom

OK, it took forever to find the problem

I will add note about it on my site when I update the page in the new format.
Working on a few changes of the code but it is not so easy to know if the ESC key is
pressed or when you press the Cancel button in the copy dialog when shell is working.

Do you have a idea Tom




--

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


"Tom Ogilvy" wrote in message ...
OK, it took forever to find the problem, but the problem appears to be that
you can't declare your

strFileNameXls and strFileNameZip as String (see Ron's code).

This worked for me:

Sub Test()
Dim wbkbook1 As Workbook
Set wbkbook1 = Workbooks("AA_Demo.xls")
CompressFile wbkbook1
End Sub
Sub CompressFile(ByVal wkbBook1 As Workbook)

Dim strFileNameZip
Dim strFileNameXls
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"
Debug.Print strFileNameXls, strFileNameZip
If Dir(strFileNameXls) < "" Then
Kill strFileNameXls
End If
' 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
On Error Resume Next
Do Until objApp.Namespace(strFileNameZip).Items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
MsgBox "Your Backup is saved he " & strFileNameZip
End Sub

--
Regards,
Tom Ogilvy


"Mark Driscol" wrote:

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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Windows XP zip problem
I didn't get any copy dialog and it isn't something I have worked with.

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

Hi Tom

OK, it took forever to find the problem

I will add note about it on my site when I update the page in the new format.
Working on a few changes of the code but it is not so easy to know if the ESC key is
pressed or when you press the Cancel button in the copy dialog when shell is working.

Do you have a idea Tom




--

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


"Tom Ogilvy" wrote in message ...
OK, it took forever to find the problem, but the problem appears to be that
you can't declare your

strFileNameXls and strFileNameZip as String (see Ron's code).

This worked for me:

Sub Test()
Dim wbkbook1 As Workbook
Set wbkbook1 = Workbooks("AA_Demo.xls")
CompressFile wbkbook1
End Sub
Sub CompressFile(ByVal wkbBook1 As Workbook)

Dim strFileNameZip
Dim strFileNameXls
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"
Debug.Print strFileNameXls, strFileNameZip
If Dir(strFileNameXls) < "" Then
Kill strFileNameXls
End If
' 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
On Error Resume Next
Do Until objApp.Namespace(strFileNameZip).Items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
MsgBox "Your Backup is saved he " & strFileNameZip
End Sub

--
Regards,
Tom Ogilvy


"Mark Driscol" wrote:

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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Windows XP zip problem
Thanks Tom

You only see it with a large file Tom

For example zip a workbook of 20-30 MB
Or a whole folder with a lot of files in it with the example from my site.

I will test more this week


--

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


"Tom Ogilvy" wrote in message ...
I didn't get any copy dialog and it isn't something I have worked with.

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

Hi Tom

OK, it took forever to find the problem

I will add note about it on my site when I update the page in the new format.
Working on a few changes of the code but it is not so easy to know if the ESC key is
pressed or when you press the Cancel button in the copy dialog when shell is working.

Do you have a idea Tom




--

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


"Tom Ogilvy" wrote in message ...
OK, it took forever to find the problem, but the problem appears to be that
you can't declare your

strFileNameXls and strFileNameZip as String (see Ron's code).

This worked for me:

Sub Test()
Dim wbkbook1 As Workbook
Set wbkbook1 = Workbooks("AA_Demo.xls")
CompressFile wbkbook1
End Sub
Sub CompressFile(ByVal wkbBook1 As Workbook)

Dim strFileNameZip
Dim strFileNameXls
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"
Debug.Print strFileNameXls, strFileNameZip
If Dir(strFileNameXls) < "" Then
Kill strFileNameXls
End If
' 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
On Error Resume Next
Do Until objApp.Namespace(strFileNameZip).Items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
MsgBox "Your Backup is saved he " & strFileNameZip
End Sub

--
Regards,
Tom Ogilvy


"Mark Driscol" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Windows Messinger HarryGuy Setting up and Configuration of Excel 1 November 28th 09 07:02 PM
Problem with Windows Messinger HarryGuy Setting up and Configuration of Excel 2 November 26th 09 05:53 PM
May be a Chinese Windows problem?? AussieDave Excel Programming 2 July 11th 06 08:48 AM
PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS KandK Excel Discussion (Misc queries) 8 May 7th 06 11:01 PM
Thank you re VBE Windows problem John Topley Excel Programming 1 February 5th 04 03:59 AM


All times are GMT +1. The time now is 02:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement