VBA Open Sharepoint File & then Continue macro

Question

Hello,

 I need help editing this Macro. The first 2 lines of the code I get to work perfectly without the area below the red arrow. However, when I try to use the whole down below I get a error and it states to debug. But then it continues opening the file from the sharepoint. How do I get excel to wait until file name <Disputes FY21 > is open from the sharepoint before going to sheet 'AR'. Also, this sharepoint file is an excel <XLXS> file that auto-opens into the Excel Desktop App. Any guidance would be greatly appreciated. THANKS!

Answer 1

Try inserting the command DoEvents on the next line after the line to FollowHyperlink

Answer 2

Hello,

   I tried a new coding to get with the DoEvents, see code below. However, this tries to run every time that I open an excel workbook. It doesn't wait for me to click 'Run' on the macro. However, if I just run the 'Green' code below the file open in my desktop app perfectly.  Any suggestion to fix the larger code? thanks!

Range("C1").Select

ActiveWorkbook.FollowHyperlink Address:="https://aramark365-my.sharepoint.com/:x:/r/personal/skip...xyxy", NewWindow:=False, AddHistory:=True

End Sub

' TEST Macro

'

Dim Ws As Worksheet

 Dim WY As Workbook

Set Ws = ActiveSheet

 Set WY = Workbooks("ICare Disputes list FY 2021.xlsx")

    Range("C1").Select

    ActiveWorkbook.FollowHyperlink Address:="https://aramark365-my.sharepoint.com/:x:/r/personal/skip...xyxy", NewWindow:=False, AddHistory:=True

    DoEvents

        If WY = True Then

Application.Wait Now + TimeValue("00:00:10")

Else

Call FILTER_YRLY (macro)

End If

End Sub

Answer 3

I can't test the code so I can only provide some suggestions.

Firstly, the following line is incorrect.

If WY = True Then

It should be as follows because WY is an object, not a boolean variable. If nothing then nothing is assigned to the variable.

If WY Is Nothing Then

Just as additional info to test if something is assigned to the variable the line of code is as follows. (If it is Not Nothing then it is something)

If Not Ws Is Nothing Then

As a suggestion to try, you could try inserting the Wait command before the line ActiveWorkbook.FollowHyperlink

If all else fails, instead of trying to run the code from the Workbook Open event, why not insert a button on a worksheet that the user can click to run the code after the workbook is opened.