none
Anyway to auto-export a SharePoint list to Excel, and run macros against it? RRS feed

  • Question

  • Is there any way to create a job that will export a SharePoint list to Excel, run a couple macros against the worksheet, and save it as a csv to a file share?

    Current process:

    1. Double-Click .iqy file located in the share, which imports the list into excel.

    2. Run the macros manually that are stored in my Personal.xlsb

    3. Save As .csv back to the file share

    I would like to run this daily, but don't even know where to start. Hoping Powershell will solve my problem!

    Tuesday, December 10, 2013 5:23 PM

Answers

  • Thanks Chen, I was able to piece this together:

    Function Import-IQY { #computer running script must have Excel installed $xl = New-Object -C Excel.Application -vb:$false $xl.DisplayAlerts = $False #IQY file is generated manually, placed in the $stagingPath directory, and called on the next line: $iqy = $xl.Workbooks.Open("$stagingPath\oms.iqy") $iqy.SaveAs("$stagingPath\oms.csv", 6) $iqy.close($false) $xl.quit() $confirm = $false Get-Process Excel | kill -Confirm:$confirm } Function Run-Macros { $excelFiles = Get-ChildItem -Path "$stagingPath\" -Include *.csv -Recurse $excel = new-object -comobject excel.application $macrobook = $excel.workbooks.open("$stagingPath\BaseWorkbook.xlsm") Foreach($file in $excelFiles) { $name = $file.name $workbook = $excel.workbooks.open($file.FullName) $worksheet = $workbook.worksheets.item(1) $excel.Application.Run("BaseWorkbook.xlsm!Clean") $excel.Application.Run("BaseWorkbook.xlsm!ReplaceNullColumnA") $workbook.saveAs("$path\$name") $workbook.close($false) $excel.quit() $confirm = $false Get-Process Excel | kill -Confirm:$confirm } }


    Monday, December 23, 2013 8:41 PM

All replies

  • It's possible in Excel. But, Instead of saving the list as .iqy.

    Export the List as Excel using PowerShell - Refer this link

    Execute Macros on Excel - Refer this LINK

    Export as CSV and save in file location.

    Exportto-CSV \\NetworkShare -NoTypeInformation

    All three are possible. Let me know if you need more help!!!


    Regards Chen V [MCTS SharePoint 2010]


    • Edited by Chen VMVP Tuesday, December 10, 2013 5:35 PM
    Tuesday, December 10, 2013 5:34 PM
  • Thanks Chen, I was able to piece this together:

    Function Import-IQY { #computer running script must have Excel installed $xl = New-Object -C Excel.Application -vb:$false $xl.DisplayAlerts = $False #IQY file is generated manually, placed in the $stagingPath directory, and called on the next line: $iqy = $xl.Workbooks.Open("$stagingPath\oms.iqy") $iqy.SaveAs("$stagingPath\oms.csv", 6) $iqy.close($false) $xl.quit() $confirm = $false Get-Process Excel | kill -Confirm:$confirm } Function Run-Macros { $excelFiles = Get-ChildItem -Path "$stagingPath\" -Include *.csv -Recurse $excel = new-object -comobject excel.application $macrobook = $excel.workbooks.open("$stagingPath\BaseWorkbook.xlsm") Foreach($file in $excelFiles) { $name = $file.name $workbook = $excel.workbooks.open($file.FullName) $worksheet = $workbook.worksheets.item(1) $excel.Application.Run("BaseWorkbook.xlsm!Clean") $excel.Application.Run("BaseWorkbook.xlsm!ReplaceNullColumnA") $workbook.saveAs("$path\$name") $workbook.close($false) $excel.quit() $confirm = $false Get-Process Excel | kill -Confirm:$confirm } }


    Monday, December 23, 2013 8:41 PM
  • Hope this worked !!!

    Regards Chen V [MCTS SharePoint 2010]


    • Edited by Chen VMVP Tuesday, December 24, 2013 8:35 AM
    Tuesday, December 24, 2013 8:35 AM