I wrote a Powershell script that queries all of my groups from Exchange Online, then gets membership for each of those groups and saves each group to a separate CSV file. This is all working fine.
(Some non relevant parts omitted for brevity):

    $excelTemplate = "$($fileLocation)\group-breakout.xlsm"
    Copy-Item $excelTemplate "$($tempPath)\group-breakout-$($curDate).xlsm"
    
    # Creating Excel COM Object
    $excel = new-object -comobject excel.application
    $excelFile = Get-ChildItem -Path C:\temp\group-breakout -Include *.xlsm -Recurse
    
    # Gathering all groups:
    Write-Host "Getting all groups from Exchange Online..."
    $allGroups = Get-DistributionGroup | select Name, PrimarySmtpAddress, ManagedBy, MemberJoinRestriction 
    $allGroups | Export-Csv "$($tempPath)\1-AllGroups.csv" -NoTypeInformation
    Write-Host "`tDone."
    # Gathering members for all groups
    Write-Host "Getting members for all groups from Exchange Online..."
    $allGroups | ForEach-Object {
        Get-DistributionGroupMember -Identity $_.Name | select DisplayName, Alias, PrimarySmtpAddress | Export-Csv "$($tempPath)\$($_.name.Replace("/","-")).csv" -NoTypeInformation
        }
    Write-Host "`tDone."
    
    # Calling macro in xlsm to merge the created csvs into a Excel spreadsheet
    Write-Host "Creating Spreadsheet..."
            $workbook = $excel.workbooks.open($excelFile) # Opening workbook
            $worksheet = $workbook.worksheets.item(1) # Selecting worksheet
            $excel.Run("ImportCSVs") # Calling macro
            $workbook.save() #Saving workbook
            $workbook.close() # Closing workbook
    $excel.quit() # Closing Excel COM Object
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null # Making sure Excel process is closed.


I also have an Excel VBA script access through a COM object that will take those CSVs and add them to an XLSX with each CSV being one worksheet: 

    Sub ImportCSVs()
    Dim fPath   As String
    Dim fCSV    As String
    Dim wbCSV   As Workbook
    Dim wbMST   As Workbook
    
    Set wbMST = ThisWorkbook
    fPath = "C:\temp\group-breakout\"                  'path to CSV files, include the final \
    Application.ScreenUpdating = False  'speed up macro
    Application.DisplayAlerts = False   'no error messages, take default answers
    fCSV = Dir(fPath & "*.csv")         'start the CSV file listing
    
        On Error Resume Next
        Do While Len(fCSV) > 0
            Set wbCSV = Workbooks.Open(fPath & fCSV)                    'open a CSV file
            wbMST.Sheets(ActiveSheet.Name).Delete                       'delete sheet if it exists
            ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count)    'move new sheet into Mstr
            Columns.AutoFit             'clean up display
            fCSV = Dir                  'ready next CSV
        Loop
    
    Worksheets("1-AllGroups").Activate
    Worksheets("placeholder").Delete
    Workbooks.Application.ActiveWorkbook.SaveAs Filename:="C:\temp\group-breakout\group-breakout.xlsx", FileFormat:=51
    Application.ScreenUpdating = True
    Set wbCSV = Nothing
    End Sub

The issue I have is having to include the Excel macro-enabled spreadsheet in the script location so I can access it from the script. Is there a way to somehow embed the VBA script in the Powershell script and run it inside of Excel? Or pass the VBA code to Excel? Or, alternatively, a way to perform the tasks in the VBA script using Powershell directly?

Hi Steve,

sure there is a way to do this in PowerShell. Boe Prox has a nice implementation in the Gallery.

Cheers,
Fred

The easiest way to do this is to add the VBA to a template and load the template.  Now you can call any VBA macro that is in the template.  That is one of the main purposes of templates  They are like libraries of code and formatting.