1

I'm trying to write a PowerShell script that will scan a SharePoint list and download an excel spreadsheet if it has a certain worksheet name.

I'm not sure of where to start as far as syntax goes... Has anyone provided a solution like this before?

3

Here you go. If you have questions about what the script actually does, just add them as comments. Obviously you would need to tweak it for your uses. It would be better to use variables for the site URL, path, and the file name to match on.

$gc = start-spassignment
$web = ($gc | Get-SPWeb "https://portal.domain.com")
$list = $web.Lists["Tasks"]

#Use CAML so you are only looking at items that actually have attachments.
$spQuery = New-Object Microsoft.SharePoint.SPQuery;
$spQuery.Query = "<Where><Eq><FieldRef Name=`"Attachments`" /><Value Type=`"Attachments`">1</Value></Eq></IsNull></Where>";
$itemsWithAttachments = $list.GetItems($spQuery);

foreach($item in $itemsWithAttachments)
{
    foreach($attachment in $item.Attachments)
    {
        if($attachment -eq "manualcandidato2006.pdf")
        {
            $file = $web.GetFile($item.Attachments.UrlPrefix + $attachment);       
            $bytes = $file.OpenBinary()               

            #Save the attachment as a file 
            $filePath = "C:\Path\" + $attachment
            $fs = new-object System.IO.FileStream($filePath, "OpenOrCreate")
            $fs.Write($bytes, 0 , $bytes.Length)   
            $fs.Close() 
        }
        else
        {
            $false
        }
    }
}


$gc | stop-spassignment
| improve this answer | |
  • thanks for the quick response, but I've already got this process down. What I really needed was the scan for a worksheet name in each excel file. – Sev09 Mar 13 '15 at 15:37
  • 1
    Ah, I misunderstood. I read workbook but you did type worksheet. Are these xlsx or xls files or a mix of both? – Robert Kaucher Mar 13 '15 at 16:14
  • 1
    Give me about 30 min. This is an interesting question and I think I have got it. – Robert Kaucher Mar 13 '15 at 16:37
  • no problem! They should all be xlsm. – Sev09 Mar 13 '15 at 17:06
1

I wrote the following powershell for downloading the original versions of file from document library. You can change the script as per your need. the script will iterate through all the folders and subfolders of the document library. it also has an option to specify the Root folder Initial for eg, i had library which had lots of folders at root level , so it was impossible for me to download all at once, so i specified the folder name initial in script and downloaded part by part.

if((Get-PSSnapin "Microsoft.SharePoint.PowerShell") -eq $null)
{
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}

$destination = "L:\Data\" 
$web = Get-SPWeb -Identity "https://TestWebApp/"
$list = $web.GetList("https://TestWebApp/Test/DocName") #your List/Document Library path

function ProcessFolder 
{
    param($folderUrl)
    $folder = $web.GetFolder($folderUrl)
    foreach ($file in $folder.Files) 

    {

         if($file.Name.Contains("Test.pdf"))
            {                                      
                 [Microsoft.SharePoint.SPFileVersion]$iv = $file.Versions[0]; 
                 $versionNumber = $iv.VersionLabel;      


               Write-Host    $versionNumber.Name
               if($versionNumber )
               {

               DownloadFile($iv)
               }
               else
               {

               DownloadFile($file)
               }
             }

      else
               {

               DownloadFile($file)
               }



       }

    }


     Function DownloadFile($type)
     {
     try
     {
      $destinationfolder = $destination + "/" + $folder.Url 
      if (!(Test-Path -path $destinationfolder))
        {
           $dest = New-Item $destinationfolder -type directory 
         }

     $binary = $type.OpenBinary()
     $stream = New-Object System.IO.FileStream($destinationfolder + "/" + $file.Name), Create
     $writer = New-Object System.IO.BinaryWriter($stream)
     $writer.write($binary)
     $writer.Close()

     }
     Catch
     {
      Write "Error: $file.name: $_" >>"L:\Data\Error.txt"
      continue;
     }

     }

        #Ensure destination directory


function ProcessAllSubfolders($folderCollection)
{
    foreach ($folder in $folderCollection)
    {
        if ($folder.SubFolders.Count -gt 0)
        {
            ProcessAllSubfolders($folder.SubFolders)
        }

        ProcessFolder($folder.Url)
    }
}


#Download root files
#ProcessFolder($list.RootFolder.Url)
#Download files in folders

foreach($folder in $list.RootFolder.SubFolders)
{
if($folder.Name.StartsWith("A") -or $folder.Name.StartsWith("B") -or $folder.Name.StartsWith("C"))
{
ProcessFolder($folder.Url)

if ($folder.Subfolders.Count -gt 0)
        {
            ProcessAllSubfolders($folder.SubFolders)
        }

}

}

Write-Host "Completed Succesfully"

More scripts here on my blog

| improve this answer | |
0

Instead of my original proposed solution, I just decided to download all Excel files from the SharePoint list to a folder, then run this on the source folder:

$ErrorActionPreference= 'silentlycontinue'
$tempLocation = "C:\Source"     # Path to read files
$targetlocation = "C:\Target"
Write "Loading Files..."
$files = Get-ChildItem C:\Source
Write "Files Loaded."
ForEach ($file in $files)
{
#Check for Worksheet named TestSheet
     $Excel = New-Object -ComObject Excel.Application
     $Excel.visible = $false
     $Excel.DisplayAlerts = $false
     $WorkBook = $Excel.Workbooks.Open($file.Fullname)
     $WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"}

     if($WorkSheets) {
     $path = $tempLocation + "\" + $file
     $dest = $targetlocation + "\" + $file
     Write "Saving $path"
     $WorkBook.SaveAs($dest)
     }
     $Excel.Quit()
     Stop-Process -processname EXCEL
}
Read-host -prompt "The Scan has completed.  Press ENTER to close..."
clear-host;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.