Accesing sharepoint excel using powershell/python

1

I am trying to copy excel sharepoint data and manipulate it using powershell or python.

I tried the below code for powershell

$fromfile = "https://company.sharepoint.com/:x:/r/personal/username_domain_company_com/.................
Changes.xlsx"
$tofile   = "C:\ExcelTest\NewExcel.xlsx"

$webclient = New-Object System.Net.WebClient
$webclient.UseDefaultCredentials = $true
$webclient.DownloadFile($fromfile, $tofile)

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($tofile)
$Worksheet = $Workbook.Sheets.Item($WorksheetName)
$Excel.Visible = $false

$Workbook.Close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel

The output is

Exception calling "DownloadFile" with "2" argument(s): "An exception occurred during a WebClient request."
At C:\Users\username\Documents\Untitled1.ps1:8 char:1
+ $webclient.DownloadFile($fromfile, $tofile)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : WebException

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
At C:\Users\username\Documents\Untitled1.ps1:12 char:1
+ $Worksheet = $Workbook.Sheets.Item($WorksheetName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
powershell
asked on Stack Overflow Jun 10, 2020 by Swetha

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0