We have recently moved to SharePoint Online and I am auditing all the users in a particular group. Currently I am downloading each page using the "Get Data from Web" feature in excel and loading each page one at a time.
I tried using the Connection feature from Excel, but that fails. Reason below: The page link format changes based on the name of user from last page e.g. Page 1: https://<sharepoint>/sites/<Group>/_layouts/15/people.aspx?MembershipGroupId=161
Page 2: https://<sharepoint>/sites/<Group>/_layouts/15/people.aspx?Paged=TRUE&p_Title=<First_Name>%20<Last_Name>&p_ID=188&View=%7bXXXXX%2dXXXXX%2dXXXXX%2dXXXXX%2dXXXXX%7d&MembershipGroupId=161<group_id>&PageFirstRow=31
Now Page 3 will have a different First_Name and Last_Name. If I delete/add a few users, this value will change which is making it impossible to save connection on excel to get the list the next time.
This now means, I have do download each page (40+ in total) and then run my macro to compile a list and then perform my audits.
Hence I am looking for an alternate solution to be able to extract all the users details (Name, Title, Department) from the SharePoint probably by using a excel macro?