Use AutoIT to open a file in Microsoft TEAMS

[Valnurat]

Hi,

We have our scheduler in TEAMS as an Excel file. The file is constantly changed, so I would like to open it and read data from it to show who's at work and who have different kinds of roles. 

I want to ask if it's possible to open a file in Microsoft TEAMS and read the content in the cells and with color format?

Edit: If it's not possible to collect the file, is it then possible to read the content of a cell and the format of it, with colors?

Br

Edited May 6, 2020 by Valnurat

[Danp2]

Have you tried using the Excel UDF to open / manipulate the file?

[mLipok]

Is this Excel file opens directly on website using Office 365 ?

edit:

Look here:
https://docs.microsoft.com/en-us/sharepoint/dev/general-development/excel-services-rest-api

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sample-uri-for-excel-services-rest-api#sample-uri-for-rest-commands-in-excel-services

 

Edited May 6, 2020 by mLipok

[Valnurat]

  On 5/6/2020 at 10:45 AM, Danp2 said:

Have you tried using the Excel UDF to open / manipulate the file?

Expand  

Not yet, I was not aware that it was possible with that.

 

  On 5/6/2020 at 11:16 AM, mLipok said:

Is this Excel file opens directly on website using Office 365 ?

Expand  

I found out there is a link to the file and when I add it to my browser, I get message as you can see in the attachment.

Is that useful?

[Valnurat]

I have this idea to open the excel file in read only mode. Read all the content and close it again, so it will not be open all the time.

I hope it is possible.

[Danp2]

Should be simple enough to test...

[Valnurat]

  On 5/6/2020 at 12:05 PM, Danp2 said:

Should be simple enough to test...

Expand  

I hope.

I just don't know how to do it.

[Danp2]

First step would be to read the help file entry for _Excel_BookOpen. Then give it a shot and let us know the results.

[Valnurat]

  On 5/6/2020 at 12:14 PM, Danp2 said:

First step would be to read the help file entry for _Excel_BookOpen. Then give it a shot and let us know the results.

Expand  

Ok.

Not sure where I can find it.

Can you point me to where it is?

Thank you.

 

Edit: I found it. It part of the installation of AutoIT.

Edited May 6, 2020 by Valnurat

[Valnurat]

Ok, I tried and I get

@error = 2, @extended = 0

The path does not exists.

My path is an URL:

https://teams.microsoft.com/l/file/E5BD3A67-9390-4F9A-BC05-AE561324DD4E?tenantId=ed5d5f47-52dd-48af-90ca-f7bd83624eb9&fileType=xlsx&objectUrl=https%3A%2F%2Fcompanyonline.sharepoint.com%2Fsites%2FCollaboration-ServiceDesk%2FDocument Library%2FSchedule Service Desk 2020.xlsx&baseUrl=https%3A%2F%2Fcompanyonline.sharepoint.com%2Fsites%2FCollaboration-ServiceDesk&serviceName=customspo&threadId=19:be74ab0edcd14d00b533c4d25c371d01@thread.skype&groupId=6d3b9944-4c17-4dd2-a4a1-9240336c6a5a

Is it possible to open an URL with the _Excel_BookOpen?

[Danp2]

This line from _Excel_BookOpen looks suspect to me --

If StringLeft($sFilePath, "HTTP") = 0 And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)

Maybe @water will jump in here with his thoughts.

[Nine]

Agree, the line is highly suspicious.  Meanwhile, try using InetGet, to get the file locally, and the open it with _Excel_BookOpen.  Since you are only reading, there shouldn't be any problem.

[water]

  On 5/6/2020 at 1:15 PM, Danp2 said:

If StringLeft($sFilePath, "HTTP") = 0 And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)

Expand  

This line has been added to access Excel workbooks on SharePoint. The function does not check for existance of the file if you use HTTP/HTTPS as protocol.

@Valnurat Could you please check that you have the latest version of AutoIt installed?

[Danp2]

@water I've never seen StringLeft uses in this manner. I thought the 2nd parameter needs to be a numeric value.

[Danp2]

@water If I change that line as follows, then it works as I would expect --

If StringLower(StringLeft($sFilePath, 4)) <> "http" And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)

 

[water]

I see. that's a bug and will be fixed quite soon

[water]

As a comparion is not case sensitive I see no need to call StringLower. Do you?

[Danp2]

I wasn't aware that the string comparison wasn't case sensitive.

[Nine]

Only == is case sensitive.

[Danp2]

Good to know. Guess I need to review where I'm needlessly doing this conversion in the Webdriver UDF. 😀