Continue working while loop does batches

[GeeWhiz7]

Hi folks,
I am unable to figure out how to solve the following problem.

• I have a working loop that runs down a list of names in column B (Account) and retrieves data from individual account workbooks on our network (quite a few) and puts them on the main sheet like below.
• My problem is that some of the external workbooks get updated at different times given who makes them (Singapore vs. Cleveland for example).
• I would like to adjust the loop so that it considers the accounts in batches and pauses a certain amount of time before continuing to retreive.
• If possible, I would like to be able to continue working with the workbook while it is waiting for the next time stamp to get the next batch of data.

I've tried a few things out there that pause after each request, but that kind of defeats the purpose as I would like to be able to work "in between" updates in this and other excel files.

How could I for example send just the first 5 accounts through the loop to the Call, Pause for 1 hour (while still using excel?) and then process 5 more, pause for an hour, until the list is complete?
Thanks!

Option Explicit
Public Const firstAcctRow As Integer =3

SubSnippet()

Dim lastRow As Integer
Dim Acct As String
Dim AcctName As String
Dim firstAcctRow As Integer

lastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row

For Acct = firstAcctRow To lastRow
AcctName = Worksheets("Sheet1").Range("$b$" & Acct)
Call ExternalDataUpdate(AcctName)

Next

End Sub

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	#	Account		Date	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	1	A		6/6/2022	40	113	58	106	128	107	60	78	86	76	53	148	130	84	22
4	2	B		6/5/2022	103	59	133	25	41	69	61	36	51	49	107	25	54	78	14
5	3	C		6/5/2022	98	90	84	59	90	88	100	127	116	61	86	132	90	87	79
6	4	D		6/5/2022	101	38	72	31	143	115	118	19	31	126	16	80	92	116	98
7	5	E		6/5/2022	122	102	144	52	91	108	102	124	92	108	117	74	133	118	69
8	6	F		6/5/2022	38	16	93	19	47	145	30	36	37	150	46	113	13	20	93
9	7	G		6/5/2022	109	58	12	90	105	15	64	143	52	142	42	120	18	49	39
10	8	H		6/5/2022	137	103	17	15	97	148	68	150	15	95	126	68	19	37	146
11	9	I		6/5/2022	43	16	134	18	14	138	67	150	86	136	22	82	54	87	68
12	10	J		6/5/2022	48	48	18	134	99	53	86	120	39	126	96	17	18	118	124
13	11	K		6/5/2022	28	28	83	39	140	119	86	72	57	68	20	47	109	80	125
14	12	L		6/5/2022	143	84	109	107	16	93	128	70	42	31	94	67	33	56	110
15	13	M		6/5/2022	108	76	107	42	29	85	52	19	38	55	58	122	61	127	65
16	14	N		6/5/2022	66	17	23	77	104	125	56	69	126	107	61	11	98	128	134
17	15	O		6/5/2022	32	34	135	56	81	38	59	107	41	109	70	56	118	14	132
Sheet1

 

[Micron]

Perhaps like

For Acct = firstAcctRow To lastRow
   For i = 1 To 5
      AcctName = Worksheets("Sheet1").Range("$b$" & Acct)
      Call ExternalDataUpdate(AcctName)
   Next
'Call to your pause code goes here - it is in separate sub, yes?
Next

In the pause sub try Do Events, possibly as the first line to be executed before the timer portion. Can't see it so can't suggest anything specific. Also, I don't know if Do Events will free up the workbook or not, especially since what happens after the call to anywhere else is unknown.

 

[GeeWhiz7]

Perhaps like

For Acct = firstAcctRow To lastRow
   For i = 1 To 5
      AcctName = Worksheets("Sheet1").Range("$b$" & Acct)
      Call ExternalDataUpdate(AcctName)
   Next
'Call to your pause code goes here - it is in separate sub, yes?
Next

In the pause sub try Do Events, possibly as the first line to be executed before the timer portion. Can't see it so can't suggest anything specific. Also, I don't know if Do Events will free up the workbook or not, especially since what happens after the call to anywhe

Have been trying to get your code to work, but it looks like once the For loop gets done with the 5th "i" (Acct E), it starts back at the first Acct (A) again instead of doing A to E in one batch, pausing/waiting then resuming with Acct F.

This is the pause loop that I am using that was posted by Logit in 2020. I like that it shows the time left and lets me continue working in the worksheet at the same time. Seems to work fine.

Sub Delay()
Dim i As Integer
Dim s As Long

    Sheet1.Range("D2").Value = i
        For i = 30 To 0 Step -1
             Range("D2") = i
             s = Timer + 1
             Do While Timer < s
                DoEvents
            Loop
        Next

End Sub

 

[Micron]

That's often the outcome with untested code but in this case I'd say it's a large oversight on my part. A variable would have to be declared at the module level:

Option Explicit
Dim x As Integer

The following example will loop inside 5 times so I guess that's where you put the code that is for your updated files. The outer loop is for going over the sheet rows. I used 4 and 5 just to test that it works as I expected. So this is just how it might be constructed but you have to use your references and variable values of course.

Private Sub Snippet()
Dim i As Integer, n As Integer
Dim LastRow As Integer

LastRow = 4
x = 1: n = 1
For n = x To LastRow
     For i = 1 To 5
          'do stuff here 5x
          x = x + 1
     Next
Delay
Next
End Sub

I prefer a timer sub that takes parameters so it can be used for anything else. Yours is fixed. This one will accept fractional seconds.

Sub pause(sngSecs As Single)
Dim endTime As Single

endTime = Timer
Do Until Timer > endTime + sngSecs
   'Debug.Print Timer
Loop

End Sub

 

[GeeWhiz7]

That's often the outcome with untested code but in this case I'd say it's a large oversight on my part. A variable would have to be declared at the module level:

Option Explicit
Dim x As Integer

The following example will loop inside 5 times so I guess that's where you put the code that is for your updated files. The outer loop is for going over the sheet rows. I used 4 and 5 just to test that it works as I expected. So this is just how it might be constructed but you have to use your references and variable values of course.

Private Sub Snippet()
Dim i As Integer, n As Integer
Dim LastRow As Integer

LastRow = 4
x = 1: n = 1
For n = x To LastRow
     For i = 1 To 5
          'do stuff here 5x
          x = x + 1
     Next
Delay
Next
End Sub

I prefer a timer sub that takes parameters so it can be used for anything else. Yours is fixed. This one will accept fractional seconds.

Sub pause(sngSecs As Single)
Dim endTime As Single

endTime = Timer
Do Until Timer > endTime + sngSecs
   'Debug.Print Timer
Loop

End Sub

This works nicely, thank you Micron!

 

[Micron]

Glad you have a solution!