Use AutoIT to open a file in Microsoft TEAMS

[water]

Fix has been committed to the repository.

[Danp2]

Is it safe to assume that you made the change in both places? 😉

[water]

_Excel_BookOpen and Excel_BookOpenText? So safe. Believe me 🤥

[seadoggie01]

Files uploaded and shared through MS Teams are actually stored inside of SharePoint (for me at least). If you open the workbook in the Desktop App (there should be a button that says "Open in Desktop App"), you can check where it is saved with 

VBA: Debug.Print(Workbook("your workbook's file name").FullName)

Or by opening the file's location: File -> Info -> Related Documents -> Open File Location (Shortcut: Alt F, I, U, 1)

[Valnurat]

17 hours ago, water said:

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?

My version is 3.3.14.5

[water]

You could modify function _Excel_BookOpen (and _Excel_BookOpenText) in Excel.au3 to fix the bug.

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

 

[Valnurat]

3 hours ago, water said:

You could modify function _Excel_BookOpen (and _Excel_BookOpenText) in Excel.au3 to fix the bug.

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

 

I did that and when I run the program the excel opens with a blank sheets.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = "https://companyonline.sharepoint.com/:x:/r/sites/Collaboration-ServiceDesk/_layouts/15/Doc.aspx?action=edit&sourcedoc=%7BE5BD3A67-9390-4F9A-BC05-AE561324DD4E%7D"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)

But it opens find when I do this:

Run("C:\Program Files (x86)\Microsoft Office\root\Office16\excel.exe /h https://companyonline.sharepoint.com/:x:/r/sites/Collaboration-ServiceDesk/_layouts/15/Doc.aspx?action=edit&sourcedoc=%7BE5BD3A67-9390-4F9A-BC05-AE561324DD4E%7D")

 

[Danp2]

@Valnurat Have you verified the file's path from within Excel?  You can use the shortcut: Alt F, I, C to place it on the clipboard.

[Valnurat]

1 hour ago, Danp2 said:

@Valnurat Have you verified the file's path from within Excel?  You can use the shortcut: Alt F, I, C to place it on the clipboard.

Yes, I get this path: https://companyonline.sharepoint.com/sites/Collaboration-ServiceDesk/Document Library/Schedule Service Desk 2020.xlsx?web=1

and @error = 3, @extended = -2147352567

[Danp2]

Are you sure you modified the UDF? From my experience, they are usually flagged read-only.

[iamtheky]

20 hours ago, seadoggie01 said:

Files uploaded and shared through MS Teams are actually stored inside of SharePoint

Teams is Sharepoint.  (the calls are coming from inside the house!!)

- open your https://delve.office[.]com   (or if govcloud https://delve-gcc.office[.]com/)

From there you can hit the ellipses and copy the ugly link.  Use one of those as the template.  Also, switching to the UNC path... 

edit:  just had a thought about cloud shell but i need to go try it rather than yolo it into your thead

Edited May 7, 2020 by iamtheky

[Valnurat]

1 hour ago, Danp2 said:

Are you sure you modified the UDF? From my experience, they are usually flagged read-only.

It is the excel.au3 where the location is C:\Program Files (x86)\AutoIt3\Include, right?

[Danp2]

@Valnurat That should be correct. Easiest way to test the change is to copy it into your local script and give it a new name, like this --

Func _Excel_BookOpenEx($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)

    If StringLeft($sFilePath, 4) <> "Http" And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name)
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

Then call this modified function instead of the standard one. Once you're sure that works, then you can move the changes into the UDF.

[water]

@extended = -2147352567 (decimal) stands for 0x80020009. This means "Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION)"
Unfortunately this is a general error and can be everything.

As Danp2 suggested a modified function of _Excel_BookOpen I suggest to add a modified COM error handler as well.
Replace the __Excel_COMErrorFunction line with

Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFuncModified")

Then add this function based on the example in ObjEvent from the helpfile:

; User's COM error function. Will be called if COM error occurs
Func __Excel_COMErrFuncModified($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

So we should get better error information.

[Nine]

Maybe the first step should be to get the file thru InetGet (as suggested above), so we can know for sure that the file can be downloaded from this URL and we are not trying to debug excel uselessly.  Just an idea...

[Valnurat]

On 5/6/2020 at 4:02 PM, Nine said:

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.

From the helpfile there is 2 examples.

Boths gives me this:

[Nine]

Well doesn't seem that it is Excel the problem...

[Danp2]

To prove that it isn't an Excel issue, try to open file from within Excel using the sequence Alt + F, O, O and then paste the URL into the file open dialog.

[Valnurat]

I took the function @Danp2 made and added it to a local file and it works.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Open an existing workbook and return its object identifier.
Local $sWorkbook = "https://companyonline.sharepoint.com/sites/Collaboration-ServiceDesk/Document%20Library/Schedule%20Service%20Desk%202020.xlsx?web=1"
Local $oWorkbook = _Excel_BookOpenEx($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)

Func _Excel_BookOpenEx($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)

    If StringLeft($sFilePath, 4) <> "Http" And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name)
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

 

[Nine]

By using an accessible xls from the web, everything is working fine :

expandcollapse popup

#include <InetConstants.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

Example()
Example2()

Func Example()
    ; Save the downloaded file to the temporary folder.
    Local $sFilePath = "Test.xls"

    ; Download the file by waiting for it to complete. The option of 'get the file from the local cache' has been selected.
    Local $iBytesSize = InetGet("https://file-examples.com/wp-content/uploads/2017/02/file_example_XLS_10.xls", $sFilePath, $INET_FORCERELOAD)

    ; Retrieve the filesize.
    Local $iFileSize = FileGetSize($sFilePath)

    ; Display details about the total number of bytes read and the filesize.
    MsgBox($MB_SYSTEMMODAL, "", "The total download size: " & $iBytesSize & @CRLF & _
            "The total filesize: " & $iFileSize)

    ; Delete the file.
    FileDelete($sFilePath)
EndFunc   ;==>Example

Func Example2 ()
  Local $oExcel = _Excel_Open()
  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

  ; Open an existing workbook and return its object identifier.
  Local $sWorkbook = "https://file-examples.com/wp-content/uploads/2017/02/file_example_XLS_10.xls"
  Local $oWorkbook = _Excel_BookOpenEX($oExcel, $sWorkbook)
  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
  MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)

EndFunc

Func _Excel_BookOpenEX($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
;   If StringLeft($sFilePath, "HTTP") = 0 And Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name)
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

Looks to me that the URL is incorrect, or there is network configuration that disables its download.