Opening SharePoint Workbooks

[Slicemahn]

Hello Everyone,

I having difficulty in getting this code to execute smoothly. This is the "Update" portion of the code, where the excel report is opened from a SharePoint location, checked out, and then the data refresh performed including any Pivot Tables/Caches. I want the opened or updated Excel file to open in another window and the code executed to do as I stated. What I am finding is interruptions with the blanking out of the current workbook when going to the desired workbook. At times, the updated workbook will not show and the code doesn't wait for all the refreshes to be done.

Sub Collect_Actions()
Dim strFile$
Dim ReportRow As Long, ReportCount As Long
Dim wb As Object


With ThisWorkbook.Worksheets("Sheet1")
     ReportCount = .[a2].CurrentRegion.Rows.Count
End With

ReportRow = 2


Do While ReportRow <= ReportCount

Application.ScreenUpdating = False
Application.StatusBar = "Reviewing report " & ReportRow & " of " & ReportCount
    
' Set conditions for Update, Retire, and Renew

Select Case Cells(ReportRow, 11)

Case "Renew"
Application.StatusBar = Cells(ReportRow, 2) & "is currently undergoing " & Cells(ReportRow, 4) & "ing process..."
            If Cells(ReportRow, 5) <> "Inactive" Then
                 Application.Wait (Now + TimeValue("00:00:02"))
            Application.StatusBar = "Report " & Cells(ReportRow, 2) & " is already active."
            End If
            'Update log with Date, Time, Report, Error: Renew Task Not Completed, URL, Description:  Report is already active.
            Cells(ReportRow, 5) = "Active"
            Cells(ReportRow, 12) = "Request Complete"
            Cells(ReportRow, 13) = Format(Now, "mm/dd/yyyy hh:mm AM/PM")
Application.StatusBar = Cells(ReportRow, 2) & " has been renewed."

Case "Retire"
Application.StatusBar = Cells(ReportRow, 2) & "is currently undergoing the " & Cells(ReportRow, 4) & "process..."
            If Cells(ReportRow, 5) <> "Active" Then
            Application.Wait (Now + TimeValue("00:00:02"))
            Application.StatusBar = "Report " & Cells(ReportRow, 2) & " is an inactive report."
            End If
            'Write to Error Log Date, Time, Report, Error: Task Not Completed, URL, Description: This report is already retired.
            Cells(ReportRow, 5) = "Inactive"
            Cells(ReportRow, 12) = "Request Complete"
            Cells(ReportRow, 13) = Format(Now, "mm/dd/yyyy hh:mm AM/PM")
Application.StatusBar = Cells(ReportRow, 2) & " has been retired."


Case "Update"
Application.StatusBar = Cells(ReportRow, 2) & " is currently undergoing" & Cells(ReportRow, 4) & "ing process ..."
            If Cells(ReportRow, 5) <> "Active" Then
            Application.Wait (Now + TimeValue("00:00:02"))
            Application.StatusBar = "Report " & Cells(ReportRow, 2) & " is not an active report."
            ' Write this in error log
            End If
    ' Begin data refresh process
    
    Set wb = Workbooks.Open(ReportRow, 4)
    Workbooks.CheckOut Cells(ReportRow, 4)
    'Debug.Print "Opened"
    wb.RefreshAll
    'Debug.Print "Refresh started"
    wb.Application.CalculateUntilAsyncQueriesDone
    'Debug.Print "Ensured refresh completed with calculate"
    Application.DisplayAlerts = False 'Prevent message for saving as macro free workbook
    wb.CheckIn
    Application.DisplayAlerts = True
    'Debug.Print "Checked In"
     Cells(ReportRow, 12) = "Request Complete"
     Cells(ReportRow, 13) = Format(Now, "mm/dd/yyyy hh:mm AM/PM")
Application.StatusBar = Cells(ReportRow, 2) & " has been refresh."


End Select

ReportRow = ReportRow + 1

Loop

Application.ScreenUpdating = True

End Sub

Report_Snip.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Location	Report Name	Business Unit	Link	Status	Priority	Comments	Support	Team Lead	Refresh Cycle	Choice 1	Result	Time Stamp	Date Applied	Olink	Elink	Alink	Updated	Updated By	Audience	ID	Count	Path	Checked
2	North Building	abc.xlsx	Marketing	https://sharepoint.com/Marketing/abc.xlsx	Active	Low		Staff	Tom Fielding	Weekly	Renew	Request Complete	########	4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Tom Fielding		14532	1	CLASSIFIED	Yes
3	Central Building	Employee_Asset.xlsx	Admin	https://sharepoint.com/Admin/Employee_Asset.xlsx	Active	Med		Dept	Suzanne Durrant	Weekly				4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Michelle Fennings		19452	1	CLASSIFIED	Yes
4	DMZ	End Point Mappings.xlsx	IT	https://sharepoint.com/IT/End Point Mappings.xlsx	Active	High		Dept	Henry Nguyen	Bi-Weekly				4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Lazar Popovic		12449	1	CLASSIFIED	Yes
5	West Quad	Student Residence.xlsx	Residence	https://sharepoint.com/Residence/Student Residence.xlsx	Active	Low		Staff	Bob Hamilton	Weekly				4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Riston Comich		14532	1	CLASSIFIED	Yes
6	East Quad	Custodial_Projects 2022.xlsx	Facilities	https://sharepoint.com/Facilities/Custodial_Projects 2022.xlsx	Active	Med		Dept	Hector Rodriguez	Weekly				4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Darlene Love		19452	1	CLASSIFIED	Yes
7	North Building	Sys_Org_diag.xlsx	IT	https://sharepoint.com/IT/Sys_Org_diag.xlsx	Active	High		Dept	Henry Nguyen	Bi-Weekly				4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Mark Kisel		12449	1	CLASSIFIED	Yes
8	South Point	Student_RAC_Membership.xlsx	Marketing	https://sharepoint.com/Marketing/Student_RAC_Membership.xlsx	Active	Low		Staff	Tom Fielding	Weekly				4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Aaron Ladner		14532	1	CLASSIFIED	Yes
9	West Quad	Employee_Asset.xlsx	Admin	https://sharepoint.com/Admin/Employee_Asset.xlsx	Active	Med		Dept	Suzanne Durrant	Weekly				4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Chris Continental		19452	1	CLASSIFIED	Yes
10	East Quad	Cash Reconciliation Report.xlsx	Facilities	https://sharepoint.com/Facilities/Cash Reconciliation Report.xlsx	Active	High		Dept	Hector Rodriguez	Bi-Weekly				4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Enzo Ramoni		12449	1	CLASSIFIED	Yes
11	West Quad	E2E Cost Report.xlsx	Marketing	https://sharepoint.com/Marketing/E2E Cost Report.xlsx	Active	Low		Staff	Tom Fielding	Weekly				4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Hailey Read		14532	1	CLASSIFIED	Yes
12	Library	Resource Allocations-Volumes by Floor.xlsx	Student Resources	https://sharepoint.com/Student Resources/Resource Allocations-Volumes by Floor.xlsx	Inactive	Med		Dept	Christmas Martin	Weekly	Renew			4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Carlton Spence		19452	1	CLASSIFIED	Yes
13	RAC Facilities	Gym Memberships 2022.xlsx	Recreation Facility	https://sharepoint.com/Recreation Facility/Gym Memberships 2022.xlsx	Active	Low		Staff	Hannah Worthington	Weekly				4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Robert Westall		14532	1	CLASSIFIED	Yes
14	RAC Facilities	Gym Asset Tracker.xlsx	Recreation Facility	https://sharepoint.com/Recreation Facility/Gym Asset Tracker.xlsx	Inactive	Med		Dept	Hannah Worthington	Weekly				4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Harold Snell		19452	1	CLASSIFIED	Yes
15	East Quad	Supplies List.xlsx	Facilities	https://sharepoint.com/Facilities/Supplies List.xlsx	Active	High		Dept	Hector Rodriguez	Bi-Weekly				4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Colin George		12449	1	CLASSIFIED	Yes
16	West Quad	Housing RSVP Fall 2023.xlsx	Residence	https://sharepoint.com/Residence/Housing RSVP Fall 2023.xlsx	Active	Low		Staff	Bob Hamilton	Weekly				4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Yaroslav Popovic		14532	1	CLASSIFIED	Yes
17	North Building	SCOA Budgets.xlsx	Accounting	https://sharepoint.com/Accounting/SCOA Budgets.xlsx	Active	Med		Dept	Steven Fischerall	Weekly	Retire			4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Suzanne Durrant		19452	1	CLASSIFIED	Yes
18	Central Building	Cost Benefit Analyses_Spring 2022.xlsx	IT	https://sharepoint.com/IT/Cost Benefit Analyses_Spring 2022.xlsx	Active	High		Dept	Henry Nguyen	Bi-Weekly				4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Samantha Crozier		12449	1	CLASSIFIED	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=HYPERLINK("https://sharepoint.com/"&C2&"/"&B2)

Cells with Data Validation
Cell	Allow	Criteria
K2:K18	List	Update, Retire, Renew

 

[jkpieterse]

The RefreshAll method is asynchonous. From Help: "Objects that have the BackgroundQuery property set to True are refreshed in the background."
So you must either set backgroundquery of all queries to false, or write your own code using the refresh method of each query and pivot cache. The Refresh methods have a BackgroundQuery argument.

 

[Slicemahn]

The RefreshAll method is asynchonous. From Help: "Objects that have the BackgroundQuery property set to True are refreshed in the background."
So you must either set backgroundquery of all queries to false, or write your own code using the refresh method of each query and pivot cache. The Refresh methods have a BackgroundQuery argument.

Thank you for this response. I am not clear on what you mean here. Are you saying the other files that are to be opened should have the BackgroundQuery property set to False? How do I go back and forth between the two? Use of ThisWorkbook and create another instance of Excel for the other workbook?

 

[jkpieterse]

Have you read this thread? Application.CalculateUntilAsyncQueriesDone Crashes Excel The user describes what I tried to say (though he has an additional issue with OLAP cubes which I think you don't have?)