Stop macro with loop then resume

[rowbro]

Hi all,

I have a macro that I am running to pull a series of data into excel from an internet based API, which is then dumped into my database for further use. The challenge I am facing is that pulling the data takes a few seconds, and I need to stop the macro between changing the date and saving the file, to allow for this. The "application.wait" function seems to stop the API, so I think the best is to stop and resume the macro. The challenge here is that the macro is a loop, that loops through dates set in the sheet, in cells C8:C9.

Can someone please assist me to stop the macro for 5 seconds, before resuming it, while allowing the API pull to still function?

Thanks!

Sub LoopThroughRange()
Dim rng As Range, c As Range, fPath As String, fName As String
fPath = "\\111.11.1.1\STAGING_AREA\"
Set rng = Range(Range("C8").Value & ":" & Range("C9").Value)
    For Each c In rng
        If c <> "" Then
        c.Copy
        
        Range("C3").PasteSpecial xlPasteValues
        
[I][B]        Application.Wait (Now + TimeValue("0:00:05"))[/B][/I]
        
        Sheets("Upload").Cells.Copy
        Sheets("ULVal").Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
                
        fName = "NSX_ALL_" & Format(Range("C3").Value, "yyyymmdd")
        
        Sheets("ULVal").Copy
        ActiveWorkbook.SaveAs fPath & fName & ".csv", FileFormat:=6, CreateBackup:=True, local:=True
        ActiveWorkbook.Close
        End If
    Next
End Sub

 

[JB2020]

You could try using DoEvents after changing the date?

 

[rowbro]

You could try using DoEvents after changing the date?

Please elaborate?

 

[AlphaFrog]

Try this. I'm not sure if it would work.

WaitTime = Now + TimeValue("0:00:05")
Do Until Now > WaitTime
    DoEvents
Loop

 

[JB2020]

Can I ask what you are using to import the data, i.e. power query?

 

[rowbro]

Can I ask what you are using to import the data, i.e. power query?

A custom add-in linking to a third party mySQL DB.