My Loop Keeps Running but the image doesn't update

[Icarus38376]

Hello Folks, I'm new at this so any help appreciated.
I have a form that starts on Workbook_Open. It simply displays an image that gets updated form another system. When the user select the Option_Button1, the code should get the picture, wait 5 seconds and then redisplay it. It does work most of the time, However many time it just stops without error. Also, the label1.caption shows me that the loop is still running, but the picture is not updated. The image has the date and time it was generated, that is how we know it has not been updated. IF yo go directly to the image, the time may be much later than shows on the form. Could it be that the image is getting cached? Any help appreciated.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'This Opens the Form
Private Sub Workbook_Open()
 bigStatusForm.Show vbModal
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'This start the loop to update the picture
Private Sub OptionButton1_Click()
Do While OptionButton1.Value = True
 i = i + 1
 WasteTime (5)
 Call doCopo
 bigStatusForm.Label1.Caption = i
Loop
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Sub doCopo()
On Error Resume Next
myPicture = "\\Desktop-iuf6vi7\Applications\Jobs\Reports\COPO_Status.jpg"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If Application.Workbooks.Count > 1 Then
 WasteTime (5)
Else
    IsFile = ((GetAttr(myPicture) And vbDirectory) <> vbDirectory)
    If IsFile = True Then
     Call getPicture(myPicture)
    End If
End If
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Sub getPicture(myPicture)
 bigStatusForm.Image1.Picture = LoadPicture(myPicture)
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Sub WasteTime(Seconds As Long)
 Dim EndTime As Double
 EndTime = Time() + Seconds / 24 / 60 / 60
 Do
      DoEvents
 Loop Until Time() >= EndTime
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]

 

[Yongle]

Potential issue
It seems this loop has no exit point and I assume is usually forced to end when the userform is closed

Do While OptionButton1.Value = True
 i = i + 1
 WasteTime (5)
 Call doCopo
 bigStatusForm.Label1.Caption = i
Loop

Some Likely Reasons for the problem
- The debris left behind in memory when the sub is crashed in this manner varies because the sub could be loading the picture, wasting time, testing if the file exists .... etc
- Perhaps VBA is tsometimes rying to grab the image at the exact moment it is updated by the other system
- The PC being used for other tasks at the same time as the macro is running

Your requirements
There is probably a better way achieve what you need
To make suggestions that may help you, it would be useful to understand what you are trying to do and why

1. Why does the image require re-loading indefinitely every 5 seconds?
- do you need to see the image or simply to be aware that it has been updated by the other system?
2. I am guessing that you trying to use the PC for something else whilst the loop is running - is this correct?
3. When should the loop stop running? - is there a natuaral stop point?

Let me know and I'll post a suggested approach

 

[Icarus38376]

Thank you for your help. I didn't post the code for the other option buttons on the form. There are option buttons to choose other images as well as an Exit button which sets Option_button1 to False and exits the loop.
Regarding the big picture, we have a scheduled task that runs a macro every 2 minutes to generate a .jpg graphical report. The code that I posted in the OP will take that .jpg image every 5 seconds and should update on the form. I put the lines

 If Application.Workbooks.Count > 1 Then WasteTime (5)

so in the case the first macro is running, the Form code will wait as not to have two instances of Excel running simultaneously.

 

[Yongle]

What is driving what?
Can you list an outline sequence of events like this so that I understand the flow

A scheduled task opens the workbook every 2 minutes
the userform is displayed automatically
the user selects ONE option which loads a picture
in theory the picture refreshes itself (often does not)
The report is generated based on that picture
The workbook is closed automatically
And after 2 minutes repeat


THANKS

 

[Steve_]

[FONT=Verdana]
Private Sub OptionButton1_Click()
Do While OptionButton1.Value = True
 i = i + 1
 WasteTime (5)
 Call doCopo
 bigStatusForm.Label1.Caption = i
Loop
End Sub[/FONT][FONT=Verdana]
'
Sub doCopo()
On Error Resume Next
myPicture = "\\Desktop-iuf6vi7\Applications\Jobs\Reports\COPO_Status.jpg"[/FONT]
[FONT=Verdana]If Application.Workbooks.Count > 1 Then
 WasteTime (5)
Else
    IsFile = ((GetAttr(myPicture) And vbDirectory) <> vbDirectory)
    If IsFile = True Then
     Call getPicture(myPicture)
    End If
End If
End Sub[/FONT]

So I believe this was alluded to in another reply. Your loop will continue until OptionButton1.Value is no longer True...
No where in your loop is that value ever changed.

The loop does also launch "doCopo". As you can see, nothing in that code changes the value of OptionButton1.value either.

Hence, the loop runs forever as it runs as long as optionbutton1.value = True.

Also, DoEvents works WONDERS in loops...especially when you notice things are not updating correctly.

 

[Icarus38376]

What is driving what?
Can you list an outline sequence of events like this so that I understand the flow

A scheduled task opens the workbook every 2 minutes
the userform is displayed automatically
the user selects ONE option which loads a picture
in theory the picture refreshes itself (often does not)
The report is generated based on that picture
The workbook is closed automatically
And after 2 minutes repeat


THANKS

Here are the events in sequence....

The Task Scheduler runs ever 2 minutes to open Workbook1 run a macro that generates a .jpg image and Exits. This is a seperate workbook on the same machine.
Workbook2 (the code in the OP) is opened manually and it launches a form that stays open with option buttons to show different .jpg images and an exit button to close the form. This runs every 5 seconds to look for new images generated by Workbook1. Again, even though the 5 second loop continues, the image is not always current.

 

[Icarus38376]

So I believe this was alluded to in another reply. Your loop will continue until OptionButton1.Value is no longer True...
No where in your loop is that value ever changed.

The loop does also launch "doCopo". As you can see, nothing in that code changes the value of OptionButton1.value either.

Hence, the loop runs forever as it runs as long as optionbutton1.value = True.

Also, DoEvents works WONDERS in loops...especially when you notice things are not updating correctly.

The Loop is supposed to run forever until OptionButton1 is no longer true. It will get set to False when I Choose another option button on the form. The problem is that the loop does run forever, but the image in the loop stops updating.

 

[Yongle]

Steve_ suggested using Do Events
- here is a link explaining a bit more about that https://wellsr.com/vba/2018/excel/vba-doevents-and-when-to-use-it/
You could try incorporating it into your code to see if it helps

---------------------------------

Using Do Events may be helpful
- but updating the picture every 5 seconds seems to be an unecessary overhead and contributing to the problem

My logic
- workbook1 is updating the image every 2 minutes
- workbook2 is displaying paranoid behaviour and updating every 5 seconds - 20 times too often!

Loading a picture is slow but checking if it has changed would be much quicker
- the images should only require to be reloaded into the userform only ONCE every 2 minutes
- after reloading it there is little point in checking until after workbook1 has dumped a new edition - which must be 2 minutes later

So according to my thinking
- get rid of the loop
- schedule workbook1 to dump the image at specific times 09:00:00, 09:02:00, 09:04:00 etc
- schedule workbook2 to update the image at 09:00:15, 09:02:15, 09:04:15 etc
(VBA could check first to see if a new file is present before updating userform)

Is my logic good or have I missed something crucial?

 

[Icarus38376]

Is my logic good or have I missed something crucial?

No Yongle, that is the gist of it. I am trying to get to get this form and image updated in as close to real time as possible. I was thinking of increasing the 2 min. workbook1 to go more often (every 30 seconds) but it seems that until i figure out why Workbook2 is failing there is no point. What I don't understand is how a loop can work 100 times and fail on the 101st with no error or reason. I'll try slowing things down and see if it helps. Thank you for your input.

 

[Yongle]

I am busy now for 48 hours
- in the meantime you could try adding Do Events and scheduling the macros to run at specific times (ie not use time delays) so that they do not coincide and let me know how that goes.

One other idea would be to get workbook2 to generate the image (possibly by calling workbook1) immediately before updating the userform

Getting the best solution will take a bit of trial an error
- Excel is essentially a number cruncher and you are pushing it outside its comfort zone with a none-core bolt-on