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

Question

question

bernardmwanza-0900 asked Feb 18, '22 AungZawMinThwin-9152 answered Jul 13, '22

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

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.

office-sharepoint-online windows-server-powershell

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YiLu-MSFT · Feb 21 at 10:07 AM

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

Thank you for your understanding and support.

0 ·

gaborschilten YiLu-MSFT · Jul 07 at 07:45 AM

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

Thanks,

Gabor

0 ·

Answer 1 · 2022-07-12T12:08:16.4Z

mcpyassine answered Jul 12, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-07-13T01:29:06.25Z

AungZawMinThwin-9152 answered Jul 13, '22

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')

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question