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]

36 minutes ago, Danp2 said:

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

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

 

5 minutes ago, mLipok said:

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

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]

6 minutes ago, Danp2 said:

Should be simple enough to test...

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]

14 minutes ago, 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.

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]

49 minutes ago, Danp2 said:

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

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. 😀