I am trying to import a CSV file in to an existing excel sheet. I can get it to work with an .xlsx file, but I can't figure it out with a macro enabled sheet. The reason for the macro enabled sheet, is that I want to be able to use macros, after the data has imported, so I can create a PIVOT table.

I have another powershell script running, collecting users and group information, and I want to create an easy to read excel sheet.

$csv = 'C:\\test\CSV\ADusers.csv'
$extemp = 'C:\test\Temp\WithVBA.xlsm'
$ex = 'C:\test\Pivot.xlsm'
$macro = 'add'

# Adding header to CSV
$filedata = import-csv $csv -Header "UserID,GroupID"
$filedata | Export-csv -Path $csv

# copy temp with VBA to new sheet
Copy-Item -Path $extemp -Destination $ex

# start Excel and open file
$excel = New-Object -comobject Excel.Application
$workbook = $excel.Workbooks.Open($ex)

# Run excel hidden
$excel.Visible = $false

# Import CSV here

# Open excel and run saved macro
$app = $excel.Application
$app.Run($macro)

# Save and close Excel
$workbook.save()
$workbook.close()
$excel.Quit()