progress bar problem still

[john.9.williams]

Hi,

I have some code that executes a hyperlink to open a word document on a
server. The code executes in secs, however thedoc takes a long time to
open. I have all the latest info on progress bars but this is not
working for me. i need to display a message until the word doc opens, i
can display a message based on time i.e display the mssage for 5 secs,
but the document take a variable lenght of time to open, so i need the
program to select the hyperlink display a message until the document is
open and then disapear. hel;p please if this is possible.
regards

Johny5

 

[Bob Phillips]

Johnny,

Try this.

Build a little userform that says that the doc is loading
In the form Userform_Activate, add the following code

Private Sub UserForm_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "loadit"
End Sub

where loadit is the name of another macro that opens the Word doc. Also in
that macro, add the line

Unload Userform1

at then end.

Not too much visual feedback, but something.

 

[Tom Ogilvy]

Hyperlinks operate asynchronously to the best of my knowledge. If you want
that kind of feedback, you need to use automation. Even then, opening a
document is a single command, so you can't get inside that command and
operate a progress bar for the time it takes to open - at least not with any
built in capabilities of VBA that I am aware of.


Microsoft Office 2000 Automation Help File Available (Q260410)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;260410

Microsoft Office XP Automation Help File Available (Q302460)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;302460

 

[john.9.williams]

thanks bob, although the userform will still only appear for 1 sec, it
disapears after that and i am still waiting for the doc to load

regards

johny

 

[Bob Phillips]

It shouldn't do. The code in the 'loadit' macro should look something like

Public Sub loadit()

'the code to open the word document

Unload Userform1

End Sub

What does yours look like?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

Tom: I haven't tried this exactly, although I remember doing something
similar. I'd be interested in your comments and experience.

John: Since you're opening the document through VBA, you've set an object
to the doc. You might try setting another object to the doc's first
paragraph. This will fail (I think!) until the doc is opened. Using On
Error and Is Nothing, you can construct a loop to set the object until it is
successful; when it is, Unload the UserForm.

Something like:

On Error Resume Next
Do
Set oPar = oDoc.Paragraphs(1)
Loop While oPar Is Nothing ' not sure of correct syntax here

Unload UserForm1
On Error GoTo 0

Ed

 

[Tom Ogilvy]

But he doesn't set an object to the doc if he is using a hyperlink.

If he did use automation to open the doc, then, I believe the code would
wait until the document is opened.

 

[Ed]

But he doesn't set an object to the doc if he is using a hyperlink.
Ouch! You're right - I missed that!

Well, what if he did set such an object? Would it be Nothing until the doc
opened? Could you trap the Nothing as in the previous code for a loop?
Then again, there could be too many variables, such as whether that's the
only doc open or not. If the hyperlink contained the name of the doc, you
could extract it for the object reference; if it doesn't, though . . . well,
I guess I'm out of steam. 8>(

Ed

 

[Tom Ogilvy]

I believe the only way to set an object to the doc would be to open word,
then the doc through automation and in that case, as I said, the code
should wait until it is opened.

Usually looping and testing is only done when trying to synchronize with an
asynchronous situation.

 

[john.9.williams]

this is the code that opens the hyperlink, the actual doc name is
chosen from a list by the user and assigned to the variable "process".
how could i change this to adapt the changes you have suggested

Private Sub CommandButton1_Click()
Dim process

Sheets("codes").Select
process = Range("b1")
Range("c1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"outlook://Public Folders/All Public Folders/BT plc/Networks/Network
Build/Processes/eTASK Enabled Processes/~" & process & ".doc"

Range("c2").Select
Range("c1").Select


Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True


Sheets("codes").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D19").Select
Application.CutCopyMode = False
Selection.ClearContents



Application.Visible = True
Application.Quit

ActiveWorkbook.Close SaveChanges:=False

 

[Ed]

John: This is untested in your specific instance, but I use code like this
in Excel macros. The idea is that if you have the file path and document
name, you can put that into a string and use it to open the document as an
object. The following code is intended to replace everything in your code
from

Dim process down to
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Dim oWord As Object
Dim oDoc As Object
Dim sDoc As String

Set oWord = CreateObject("Word.Application")
sDoc = Range("B1").Value
sDoc = "outlook://Public Folders/All Public Folders/BT plc/Networks/Network
Build/Processes/eTASK Enabled Processes/~" & sDoc & ".doc"
''' NOTE: DON'T FORGET TO FIX THE NG-INSERTED LINE BREAK IN THE ABOVE LINES

Set oDoc = oWord.Documents.Open sDoc

If Tom is correct (and he usually is!!), your code will wait until the
document is open before continuing. If so, then just before you open the
doc, insert
UserForm1.Show vbModeless
DoEvents

After, Unload the form.

If the code rolls over the doc opening and doesn't wait, then try something
like
On Error Resume Next
Set oWord = CreateObject("Word.Application")
sDoc = Range("B1").Value
sDoc = "outlook://Public Folders/All Public Folders/BT
plc/Networks/Network
Build/Processes/eTASK Enabled Processes/~" & sDoc & ".doc"
''' NOTE: DON'T FORGET TO FIX THE NG-INSERTED LINE BREAK IN THE ABOVE LINES

Set oDoc = oWord.Documents.Open sDoc

UserForm1.Show vbModeless
Do
Dim oPar As oWord.Paragraph
Set oPar = oDoc.Paragraphs(1)
Loop While Err.Number <> 0

Unload UserForm1
On Error GoTo 0

The idea is that until the document is open, the first paragraph is not
available to set an object to, causing an error and forcing the loop. When
all objects are set and you have no errors, the code will drop past the loop
and Unload the Userform. If you have to go this way and run into
difficulties, you might want to bounce it off the Word MVPs at thw
Word.VBA.General newsgroup.

Ed
(PS - Sorry to post and run, but I'm outta here in a couple of minutes for a
long weekend. Hope this helps you.)

 

[Robin Hammond]

Here's an attempt at a solution that seems to work for me. I've used my own
progress bar class to make life easier for me. Note the comments at the top.
First time I've tried using hyperlinks like this, so I'd be interested to
know if it works for you.

'requires inclusion of frmProgress and clsProgBar available at
'http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm
'requires a project reference to Microsoft.Word object library
'no checks made, assumes a net connection exists

Option Explicit
Public UserCancelled As Boolean

Sub Test()

Dim appWord As Word.Application
Dim nDocs As Integer
Dim PB As clsProgBar
Dim dtDelay As Date
Dim dtMaxWait As Date
Dim dtStart As Date
Dim nProgress As Integer

On Error Resume Next
Set appWord = GetObject(, "Word.Application")
On Error GoTo 0

If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")

Set PB = New clsProgBar

dtDelay = TimeSerial(0, 0, 1)
dtMaxWait = TimeSerial(0, 1, 0) + Now
nDocs = appWord.Documents.Count
dtStart = Now
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"URL of document here"

With PB
.Reset
.Title = "Load Monitor"
.Caption1 = "Loading word document"
.Caption2 = "This may take a while"
.Show
DoEvents
End With

'asynchronous
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Do While appWord.Documents.Count <= nDocs

Application.Wait Now + dtDelay
DoEvents

nProgress = (nProgress + 5) Mod 100
PB.Progress = nProgress
PB.Caption3 = "Load time: " & Format(Now - dtStart, "s") & " seconds"
If Now > dtMaxWait Then

MsgBox "Document has not loaded within " & Format(dtMaxWait, "s") &
" seconds" & _
vbCrLf & "It may still load but Excel will no longer monitor
this process", _
vbOKOnly, "Load Monitor"
PB.Finish
Exit Sub

End If

If UserCancelled Then

PB.Finish
MsgBox "The load command has been issued and may still complete", _
vbInformation, "Load Terminated"
Exit Sub

End If

Loop

PB.Finish
Set PB = Nothing
Set appWord = Nothing

MsgBox "Your document has been loaded and is open in Word", vbOkOnly, "Load
Monitor"

End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[john.9.williams]

Ron,

This works a treat thanks alot you are a very clever man!!!!

 

[Robin Hammond]

John,

If that message is for me, you are very welcome, but my name's Robin.

If it's for Ron, I can't see his post!

Robin Hammond
www.enhanceddatasystems.com

 

[john.9.williams]

sorry robin, a bit of a typing error, once again thankyou i always knew
there was a way

johny