Background
It’s so helpful to have an Excel list of the username data contained in multiple PWA Security Groups. So, we thought we’d make a blog post with the solution! You can easily achieve this if you only have a handful of PWA Security Group Usernames. But, what if you have more than a few users? You may have noticed there is no nifty “Export to Excel” button in the ribbon to save you this time.
You’ll find the secret to extracting users from PWA Security Groups in the HTML source code. There are two ways we can go about locating the User list in HTML source code, but both require you to access your PWA Security Group. We’ll outline both ways below.
Prep Work
Firstly, you will need to access your PWA Security Group, and navigate to Manage Groups.
- Go into your PWA environment by right-clicking on the cog wheel located at the top right-hand corner.
- Select “PWA settings”, then Under Security section, click on “Manage Groups”
- On the Group Name in the “Users” section, select the PWA Security Group you would like to extract Username data from.
- While it might vary for your set up, we accessed our HTML under “Portfolio Managers”, where you’ll find this data located in a container labelled “Selected Users”.
Time Saver Approach to Extract PWA Security Group Usernames
- Once you’re inside your desired PWA Security Group, right click on the web page to for the pop-up window to pop up.
- Select and click on “View page source”.
- This will open a new browser window with all the HTML source code, which can be edited.
- Use the Find command (Control + F or Command + F) and type into the search bar: <select name=”users_Beta” id=”users_Beta” size=”7″ style=”width:15em;”>
- We have found our Users, of which you could potentially have hundreds in a single PWA group. Now, it’s as easy as selecting the users you wish to grab and pasting them into an Excel spread sheet.
- Copying the whole HTML line for each user and paste it in your spreadsheet. It may require of you to do some minimal tidying up. Essentially, we want to make sure we have all our users under one excel column.
- Introduce a Macros script that will strip away all our HTML code for us in a timely manner.
Using Macros to Eliminate HTML Tags
An Excel Macros is a piece of programming code which will run in an Excel environment, and is very handy to automate routine tasks. A macro is an Excel task recording and playback tool. This tool will allow you to record your excel inputs and then play it back to save the hassle of repeating mundane tasks constantly.
For the purpose of this blog, here is the set of instruction to use Macros to strip away your HTML code:
- To enter Macros in Excel, go to “View” > “Macros” > “Record Macro…”
- You will be presented with a pop-up window. Name the macro appropriately (avoid spaces in Macro name), see below for suggestion. Click “OK” once done.
- Now go back to “”View” > “Macros” > “Stop Recording”.
- Go back again to “View” > “Macros” this time you want to select “View Macros”.
- Select the macros we have just created and click “Edit”.
- This will open a Visual Basic window. Visual Basic for Applications (VBA) window is a programming language that Excel utilises to record your steps as you perform routine tasks in an Excel environment.
- Clear preexisting text in the editor window and replace with the following:Sub HTML_Removal() Dim Cell As Range With CreateObject(“vbscript.regexp”) .Pattern = “\<.*?\>” .Global = True For Each Cell In Selection Cell.Value = .Replace(Cell.Value, “”) Next End With End Sub
- Your editor should now look like the image below. Click the “Save” icon to save this.
- When saving, please make sure to save it as an Excel Macro-Enabled Workbook.
- Select your entire User list in the spreadsheet. The Macros will affect anything you select.
- Go to “View” > “Macros” > “View Macros”.
- Select the Macros we just saved.
- Click “Run” to execute your Macros.
- Clicking on the arrow will open up nested HTML content specific to the area you’re digging in.
- Blue shaded selection panel allows you to determine which section of the web page you’re looking at.
- As an added aid, Chrome’s Designer Tool tags the selected area with the corresponding line of HTML code you’re currently selecting. In the example below, that is Div#DeltaPlaceHolderMain
Access More of Our Tech Support Articles
If you benefited from this article on extracting PWA Security Group Usernames, check out our other Tech Support Articles on Extracting PWA Usernames in Bulk, Workflows Invalid Data Error, and Updating Sharepoint Fields!