The following VBA code can help you quickly split multiple worksheets of current workbook to separate Excel files, please do as follows:
1. Create a new folder for the workbook that you want to split, because the split Excel files will be stayed at the same folder as this master workbook.
2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA: Split a workbook into multiple workbooks and save in the same folder
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Import the session to your powershell: Import-PSSession $Session
Login to Microsoft 365 admin account (to use MsolUser Cmdlet) Connect-Msolservice (It will ask for your credentials.)
Download the contents of your Exchange Global Address list to a CSV file get-user -resultsize unlimited |select * |export-csv c:\MS365\users.csv
Remove unneccessry columns and edit what you need. (dont delete the columns named UserPrincipalName and Identity we need to use them later.) In this usecase I will use Set-MsolUser module, which supports the following options.
After updating the CSV, import the updated csv to powershell and execute the changes.
in the below script, header names referenced with a hyphon are the names we used in our sheet. header names referenced with $_. are the ones that the exchange is expecting from us. To make it simpler I used the same names as exchange in my sheet.
to map a field to a field in exchange we use this syntax : – $_.
Breaking down this formula would require a separate article, so you can simply copy it to your worksheet to make sure it really works 🙂
Upon examining the results, however, you may notice one insignificant drawback – if the source string does not contain a number, the formula returns zero, as in row 6 in the screenshot above. To fix this, you can wrap the formula in the IF statement, the logical test of which checks if the source string contains any number. If it does, the formula extracts the number, otherwise returns an empty string: