With the new commands it seems to hit later, but still, after a few hundred requests (my macro runs

Erica

Find more posts tagged with

vba

#technology

#product

Accepted answers

All comments

Jirapongse

@Erica

Thank you for reaching out to us.

Please provide a detailed description of this question.

Erica

@Jirapongse

With the new commands it seems to hit later, but still, after a few hundred requests (my macro runs one request per worksheet), it takes hours for the subsequent requests to execute. Setting a maximum timer to, e.g., 6000 milliseconds is not a viable option since this would simply skipping requests and leave me with missing data where there actually is data.

Thus, I am getting back to my initial two questions:

Is there any throttling at play? (if so, according to which rules?)
Is there any issue with a full cache (how can I empty the cache while running the loop via the VBA?)

Please find below the updated macro.

Thank you.
Best
Thomas Kaspereit

Option Explicit

Sub LoopAndEnter()

Dim ws As Worksheet

Dim roundCount As Long

Dim savePath As String


savePath = ThisWorkbook.Path & Application.PathSeparator & "Announcement_times_saved.xlsm"


Application.ScreenUpdating = True

Application.DisplayAlerts = False

Application.EnableEvents = True


Do


    For Each ws In ThisWorkbook.Worksheets


        ws.Activate


        Range("A1").Value = Range("A1").Value


        DoEvents

        Application.Run "WorkspaceRefreshWorksheet", True


        ' Select entire sheet ? copy ? paste values

        ws.Cells.Select

        ws.Cells.Copy

        ws.Cells.PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False


    Next ws


Loop

End Sub

Jirapongse

@Erica

According to the code, the client uses the WorkspaceRefreshWorksheet command which is the Workspace Excel Macro. Therefore, please contact the Workspace Excel support team via LSEG Support to answer the client's questions.

EXPLORE OUR SITES