How to load and read excel file hosted in sharepoint online using powershell

Question

How to load and read excel file hosted in sharepoint online using powershell

Benard Mwanza 1,006

Hi all,

I'm working on a PowerShell script to read content in excel file hosted in SharePoint online in office 365.

My goal is retrieve content for cell B2 from the file.

My code so far,

    $Excel = New-Object -ComObject Excel.Application
    $Workbook = $Excel.Workbooks.Open("\\bernardcomms.sharepoint.com\sites\Test_Site\Documents\test_with_macro.xlsm")
    $Excel.Workbooks.CheckOut("\\bernardcomms.sharepoint.com\sites\Test_Site\Documents\test_with_macro.xlsm")

    # Get the first sheet in excel file 
    $workSheet = $Workbook.Sheets.Item(1)
    $WorkSheet.Name

    # Get value for cell B2
    $mailto = $workSheet.cells.Item(2, 2).Text
    $mailto.Text

On executing the script above, it just hangs. What am i doin wrong.

Yi Lu_MSFT 17,611 Reputation points

2022-02-21T10:07:29.733+00:00

I am currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.
Gabor Schilten 1 Reputation point

2022-07-07T07:45:49.543+00:00

Hi, any news?
Would like to achieve the same thing :)

Thanks,

Gabor
Srinivas, Bhumadi 1 Reputation point

2022-11-24T12:56:31.247+00:00

It is hanging if we run this, can you help if you found any solution for this?
I am also looking to read the Excel file within SharePoint library and export values to csv.
Aung Zaw Min Thwin 306 Reputation points

2022-11-25T05:05:28.997+00:00

$workbook = $excel.Workbooks.Open('https://bernardcomms.sharepoint.com/sites/test_site/Shared%20Documents/Test%20excel.xlsx')

Tested working with the above example.

rgds,

Anonymous

Hello.

I have a similar issue: my xlsm is on sharepoint and I want to read the value of certain cells from there, but this file has limited access and requires authentication.

Is there a way to pass my credentials to sharepoint?

My code:

$XLSPath = "https://newcoldgroup.sharepoint.com/:x:/r/sites/ITSoftwareSolutions/Shared%20Documents/IT%20Appl%20-%20Value%20Stream%20WMS%20-%20CORAX/24x7%20Support/On-call%20schedule%20-%202023.xlsm?d=wba51a34d49f24609ae4c5378dd29b47a&csf=1&web=1&e=yG0EJo"

$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open($XLSPath)
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Contact information")
$Phone = $ExcelWorkSheet.Range("H2").Text
$Name = $ExcelWorkSheet.Range("I2").Text

Accepted answer

1 additional answer

Your answer

Yi Lu_MSFT 17,611 Reputation points

2022-02-21T10:07:29.733+00:00

I am currently looking into this issue and will give you an update as soon as possible.

Thank you for your understanding and support.
Gabor Schilten 1 Reputation point

2022-07-07T07:45:49.543+00:00

Hi, any news?
Would like to achieve the same thing :)

Thanks,

Gabor
Srinivas, Bhumadi 1 Reputation point

2022-11-24T12:56:31.247+00:00

It is hanging if we run this, can you help if you found any solution for this?
I am also looking to read the Excel file within SharePoint library and export values to csv.
Aung Zaw Min Thwin 306 Reputation points

2022-11-25T05:05:28.997+00:00

$workbook = $excel.Workbooks.Open('https://bernardcomms.sharepoint.com/sites/test_site/Shared%20Documents/Test%20excel.xlsx')

Tested working with the above example.

rgds,
Anonymous

2023-05-25T14:17:24.5166667+00:00

Hello.

I have a similar issue: my xlsm is on sharepoint and I want to read the value of certain cells from there, but this file has limited access and requires authentication.

Is there a way to pass my credentials to sharepoint?

My code:

$XLSPath = "https://newcoldgroup.sharepoint.com/:x:/r/sites/ITSoftwareSolutions/Shared%20Documents/IT%20Appl%20-%20Value%20Stream%20WMS%20-%20CORAX/24x7%20Support/On-call%20schedule%20-%202023.xlsm?d=wba51a34d49f24609ae4c5378dd29b47a&csf=1&web=1&e=yG0EJo" $ExcelObj = New-Object -comobject Excel.Application $ExcelWorkBook = $ExcelObj.Workbooks.Open($XLSPath) $ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Contact information") $Phone = $ExcelWorkSheet.Range("H2").Text $Name = $ExcelWorkSheet.Range("I2").Text

Answer 1

mcpyassine 226

Hello,

Try
$mailto = $workSheet.cells.Item(2, 2)
or
$mailto = $workSheet.cells.Item(2, 2).Value

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Answer 2

Aung Zaw Min Thwin 306

Please try one of below to open workbook.

$WorkBook = $Excel.Workbooks.Open('https://bernardcomms.sharepoint.com/sites/Test_Site/Documents/test_with_macro.xlsm')

or

$WorkBook = $Excel.Workbooks.Open('\\bernardcomms.sharepoint.com@SSL\DavWWWRoot\sites\Test_Site\Documents\test_with_macro.xlsm')

Share via

How to load and read excel file hosted in sharepoint online using powershell

1 additional answer

Your answer