PublishObjects lockup
 

HOW CAN I MODIFY THIS SCRIPT TO PREVENT LOCKUP IN THE CASE OF FAILURE?

I would appreciate suggestions on how to keep my code from crashing
Excel when the destination server for PublishObjects is not available.

Here's the code that causes the crash when the destination is
unavailable:


Code:
--------------------
rPath = Range("A1").Value 'full path to the source XLS file
mOutput = "https://mySPSsite/sites/sitename/DSR-MonthToDate.mht"
With ActiveWorkbook.PublishObjects("12-December-DSR_23618")
.HtmlType = xlHtmlStatic
.Filename = mOutput
.Publish (False)
.AutoRepublish = False
End With
ChDir rPath
--------------------


This is part of the code which is autorun when the file is opened. I
have setup task scheduler to execute this every night so that the file
will export the MHT files to the SharePoint web server.

On some occasions, the save fails due to SPS server or connectivity
issues and causes the Excel process to freeze. Since it's run as a
task in a separate session, i can't do anything but kill the task.

QUESTIONS


- How do I keep this command from locking up Excel when the
destination is unavailable?
- How do I excecute a subroutine when it fails?
- Is there any way to include HTTPS login information in the
PublishObjects command?


I want to capture the error in a variable, abort the script then run a
subroutine that sends a notification.


--
ICE9
------------------------------------------------------------------------
ICE9's Profile: http://www.excelforum.com/member.php...o&userid=13565
View this thread: http://www.excelforum.com/showthread...hreadid=547566

PublishObjects lockup
 

Use an error trap. I don't suggest you do it exactly this way but I think
this would work.

rPath = Range("A1").Value 'full path to the source XLS file

mOutput = "https://mySPSsite/sites/sitename/DSR-MonthToDate.mht"
On Error Resume Next
With ActiveWorkbook.PublishObjects("12-December-DSR_23618")
If err.number <0 then
'Do something here
Else
.HtmlType = xlHtmlStatic
.Filename = mOutput
.Publish (False)
.AutoRepublish = False
end if
End With
ChDir rPath


"ICE9" wrote in message
...

HOW CAN I MODIFY THIS SCRIPT TO PREVENT LOCKUP IN THE CASE OF FAILURE?

I would appreciate suggestions on how to keep my code from crashing
Excel when the destination server for PublishObjects is not available.

Here's the code that causes the crash when the destination is
unavailable:


Code:
--------------------
rPath = Range("A1").Value 'full path to the source XLS file
mOutput = "https://mySPSsite/sites/sitename/DSR-MonthToDate.mht"
With ActiveWorkbook.PublishObjects("12-December-DSR_23618")
.HtmlType = xlHtmlStatic
.Filename = mOutput
.Publish (False)
.AutoRepublish = False
End With
ChDir rPath
--------------------


This is part of the code which is autorun when the file is opened. I
have setup task scheduler to execute this every night so that the file
will export the MHT files to the SharePoint web server.

On some occasions, the save fails due to SPS server or connectivity
issues and causes the Excel process to freeze. Since it's run as a
task in a separate session, i can't do anything but kill the task.

QUESTIONS


- How do I keep this command from locking up Excel when the
destination is unavailable?
- How do I excecute a subroutine when it fails?
- Is there any way to include HTTPS login information in the
PublishObjects command?


I want to capture the error in a variable, abort the script then run a
subroutine that sends a notification.


--
ICE9
------------------------------------------------------------------------
ICE9's Profile:
http://www.excelforum.com/member.php...o&userid=13565
View this thread: http://www.excelforum.com/showthread...hreadid=547566

PublishObjects lockup
 

Sorry, I didn't say put this

On Error Goto 0

after the end if. This is so any other errors you get don't slip through.

Jeff

"Jeff Standen" wrote in message
...
Use an error trap. I don't suggest you do it exactly this way but I think
this would work.

rPath = Range("A1").Value 'full path to the source XLS file

mOutput = "https://mySPSsite/sites/sitename/DSR-MonthToDate.mht"
On Error Resume Next
With ActiveWorkbook.PublishObjects("12-December-DSR_23618")
If err.number <0 then
'Do something here
Else
.HtmlType = xlHtmlStatic
.Filename = mOutput
.Publish (False)
.AutoRepublish = False
end if
End With
ChDir rPath


"ICE9" wrote in
message ...

HOW CAN I MODIFY THIS SCRIPT TO PREVENT LOCKUP IN THE CASE OF FAILURE?

I would appreciate suggestions on how to keep my code from crashing
Excel when the destination server for PublishObjects is not available.

Here's the code that causes the crash when the destination is
unavailable:


Code:
--------------------
rPath = Range("A1").Value 'full path to the source XLS file
mOutput = "https://mySPSsite/sites/sitename/DSR-MonthToDate.mht"
With ActiveWorkbook.PublishObjects("12-December-DSR_23618")
.HtmlType = xlHtmlStatic
.Filename = mOutput
.Publish (False)
.AutoRepublish = False
End With
ChDir rPath
--------------------


This is part of the code which is autorun when the file is opened. I
have setup task scheduler to execute this every night so that the file
will export the MHT files to the SharePoint web server.

On some occasions, the save fails due to SPS server or connectivity
issues and causes the Excel process to freeze. Since it's run as a
task in a separate session, i can't do anything but kill the task.

QUESTIONS


- How do I keep this command from locking up Excel when the
destination is unavailable?
- How do I excecute a subroutine when it fails?
- Is there any way to include HTTPS login information in the
PublishObjects command?


I want to capture the error in a variable, abort the script then run a
subroutine that sends a notification.


--
ICE9
------------------------------------------------------------------------
ICE9's Profile:
http://www.excelforum.com/member.php...o&userid=13565
View this thread:
http://www.excelforum.com/showthread...hreadid=547566