Access Excel file in sharepoint

[dannydy]

Hi I.m new here and new to Autoit, still learning about Autoit.

i've a question here, may i know how to use autoit to access the excel file in sharepoint?Because i refer to some codes, unfortunely there were quite complicated and i dont understand at all.

appreaciate if anyone could help up.

[water]

When you search the forum for "Sharepoint" you won't get too many hits. A lot of questions but not too many answers.

As AutoIt is similar to Visual Basic I would use Google to search for "Access Sharepoint Visual Basic" and see if you find any code. It should be relatively easy to convert this code to AutoIt.

[dannydy]

Thanks water.

just did some studies, in order to talk to Sharepoint server 2010. I need to know the Sharepoint client object model, then use VB to interact with it.(This would be a little hard for me)

On the other hand, i've successfully created by using _IEGetObjById and _IEAction($oDiv,"click") access to my sharepoint and open the file in excel. i done it with the assist of IE builder 2,0(Get the element id).

[AnonymousX]

On 1/15/2012 at 6:01 AM, dannydy said:

Thanks water.

 

just did some studies, in order to talk to Sharepoint server 2010. I need to know the Sharepoint client object model, then use VB to interact with it.(This would be a little hard for me)

 

On the other hand, i've successfully created by using _IEGetObjById and _IEAction($oDiv,"click") access to my sharepoint and open the file in excel. i done it with the assist of IE builder 2,0(Get the element id).

 

If you still have it available could you post the code you used to open the excel file off sharepoint? Thanks

[water]

Dannydy has been offline for more than 4 1/2 years. Don't hold your breath for an answer
IIRC users have created a few topics regarding SharePoint and Excel in th meantime.

[Juvigy]

Just get the link to the file - it should look like the bellow:
 

$path = "https://sharepoint.com/teams/folder/file%20name.xlsx"
$oExcel = ObjGet("", "Excel.Application")
$oExcel.Application.WorkBooks.Open($path)

Works great.

[water]

I see

_Excel_BookOpen($oExcel, "https://sharepoint.com/teams/folder/filename.xlsx")

now returns @error = 2 ( Specified $sFilePath does not exist)?
If true, then the following modified _Excel_BookOpen function (please call _Excel_BookOpenEX in your script) should solve the problem:

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK, willichan
; ===============================================================================================================================
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) ; <== Modified
    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_BookOpenEX

If yes, then I'm going to update the function in AutoIt's repository.

Edited January 9, 2018 by water

[AnonymousX]

Thanks guys

[water]

_Excel_BookOpen and _Excel_BookOpenText have been modified to not check for existance of the workbook if parameter $sFilePath starts with "HTTP".
This fix will be part of the next beta version

 

[SEuBo]

Hello @water,

I've stumbled across this today, when a script of mine wasn't working:

shouldn't the modified line be 

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

 

rather than

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

 

because I'm not sure if that StringLeft($sFilePath, "HTTP") = 0 actually works.

 

Cheers,

 

/Edit: Thanks @Danp2  - I wasn't aware of that other thread.

Edited December 16, 2020 by SEuBo

[Danp2]

@SEuBo Yes, that's a bug that should already be fixed in the UDF. See prior discussion here.

[sudeepjd]

You could also solve this opening with SharePoint issue using the UNC path of SharePoint instead of the HTTP path.

For details refer to my answer below.