Delay Macro while workbook refreshes from sharepoint

[Godders199]

Hello, I have the following macro to pull information from another workbook. When this work book opens it refreshes the data from a sharepoint list. I have tried various attempts to pause the macro to allow the refresh to happen. I have tried various code from the current Activeworkbook.Refreshall, to Applicationwait. but none seem to work. Is there a code i can use, that will open the workbook. wait for the refresh, and then filter? The workbook is current set to auto refresh.

I use Excel 2013

   Workbooks.Open Filename:= _
        "S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\Allocation Data\Sharepoint allocation list.xlsm"
        Dim sh1 As Worksheet
        Set sh1 = Worksheets("sheet1")
        With sh1.Range("a1").CurrentRegion
        ActiveWorkbook.RefreshAll
DoEvents
              
        .AutoFilter Field:=9, Field:=9, Criteria1 _
        :="=File & Call Listening", Operator:=xlOr, Criteria2:="=File & Outbound"
        End With
     Columns("a:l").Select
    Application.CutCopyMode = False
    Selection.Copy
   Windows("Allocation 2018.xlsm").Activate
Sheets("Material").Select
Range("a1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.DisplayAlerts = False
        Range("a1").Select
Windows("sharepoint allocation list.xlsm").Activate
ActiveSheet.ShowAllData
ActiveWorkbook.Close

Some help would be appreciated.

 

[RoryA]

Re: Delay Marco while workbook refreshes from sharepoint

If you set the properties for all the data connections in the workbook so that they do not refresh in the background, your code should work.

 

[Godders199]

Re: Delay Marco while workbook refreshes from sharepoint

Thanks for your reply, I have tried and the current code does not refresh the workbook.

 

[RoryA]

Re: Delay Marco while workbook refreshes from sharepoint

You mean it doesn't refresh at all?

 

[Godders199]

Re: Delay Marco while workbook refreshes from sharepoint

yes it does not refresh, i have checked there are new items on the sharepoint list and my current coding will not refresh at all.