Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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. -- HTH Bob Phillips wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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...b;EN-US;260410 Microsoft Office XP Automation Help File Available (Q302460) http://support.microsoft.com/default...b;EN-US;302460 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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 wrote: 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. -- HTH Bob Phillips wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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) wrote in message oups.com... 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 wrote: 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. -- HTH Bob Phillips wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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" wrote in message ... 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...b;EN-US;260410 Microsoft Office XP Automation Help File Available (Q302460) http://support.microsoft.com/default...b;EN-US;302460 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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...b;EN-US;260410 Microsoft Office XP Automation Help File Available (Q302460) http://support.microsoft.com/default...b;EN-US;302460 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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...b;EN-US;260410 Microsoft Office XP Automation Help File Available (Q302460) http://support.microsoft.com/default...b;EN-US;302460 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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.) wrote in message oups.com... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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 wrote in message oups.com... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
Ron,
This works a treat thanks alot you are a very clever man!!!! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
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 wrote in message oups.com... Ron, This works a treat thanks alot you are a very clever man!!!! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
progress bar problem still
sorry robin, a bit of a typing error, once again thankyou i always knew
there was a way johny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Progress Bar | Excel Discussion (Misc queries) | |||
Progress bar | Excel Discussion (Misc queries) | |||
Progress bar in VBE | Excel Programming | |||
Progress Bar | Excel Programming | |||
Progress Bar Help | Excel Programming |