PowerShell Script to export SharePoint List in Excel File

This script exports SharePoint list to csv using PowerShell. The script is suitable if you want to export list from Task Scheduler.

Start PowerShell as Administrator

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the Web
$web = Get-SPWeb -identity "https://project/test"

#Get the Target List
$list = $web.Lists["TestList"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items | foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

When you insert in Excel

If you want to filter list export just insert line  Where-Object { $_["City"] -eq "New York"}


#Get the Web
$web = Get-SPWeb -identity "https://sbprojekti/test"

#Get the Target List
$list = $web.Lists["TestList"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

 #Get All List items
 $list.Items |  Where-Object { $_["City"] -eq "New York"} |foreach {
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Name" -value $_["Name"]
 $ExportItem | Add-Member -MemberType NoteProperty -Name "City" -value $_["City"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Job" -value $_["Job"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Department" -value $_["Department"]

 #Add the object with property to an Array
 $ListItemCollection += $ExportItem
 }
 #Export the result Array to CSV file
 $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation                      

#Dispose the web Object
$web.Dispose()

Can't open Excel when a file run from task scheduler

Why can't Excel open a file when run from task scheduler?

I wrote a powershell script that opens an excel workbook and runs a macro. When I run that script from PS console, or even from command line using powershell.exe script.ps1, it jus works. When I set up a task from the windows task scheduler, it raises an exception about that excel file, saying that it either does not exist or is already in use.

Solution 1

Create these two folders:

32Bit:

C:\Windows\System32\config\systemprofile\Desktop 

64Bit:

C:\Windows\SysWOW64\config\systemprofile\Desktop

Excel needs these folders if it's not run interactively.


Solution 2

Open Component Services (Start -> Run, type in dcomcnfg)

Drill down to Component Services -> Computers -> My Computer and click on DCOM Config

Right-click on Microsoft Excel Application and choose Properties

In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok

Note: When setting DCOM permissions, if Microsoft Excel doesn't appear in dcomcnfg try mmc comexp.msc /32

Use the Excel Web Access Web Part in SharePoint 2013

Suppose that you have published an Excel workbook to a SharePoint library, and now you want to display all or part of that workbook in its own SharePoint Web Part. You can do this by using a special Web Part known as the Excel Web Access Web Part.

Create Excel Services Application. LINK
Manage Excel Services trusted file locations. LINK


Create excel document with chart or table.

Save to SharePoint library.

In Browser View Options check chart.

Edit page.

Insert, Web Part. From the Categories menu, select Business Data.
From the Parts menu, select Excel Web Access and click add button.

Click here to open the tool pane.

Click the button under Workbook and choose excel file.
In the Named Item textbook, enter the name of chart or table.
Ok, Save Page.

That's it!

 Web part is automatically synchronizes with excel file.

Export an Excel table to a SharePoint 2013 list

You can export data from an Excel table to a SharePoint list. When you export the list, Excel will create a new SharePoint list on the site. You can then work with the data on the site, just like you would for any other SharePoint list.

Create Excel table.

Export table to SharePoint list.

In Address field type SharePoint url.
In Name field type name of new list.

List in SharePoint.