Update the Excel UDF that comes with AutoIt

[water]

​Is this a different function? _ESxcel-BookOpen

No, it is a typo

[nitekram]

If FileExists can't find the file

​I tested this with the UNC, for the file in question with FileExist, and it does find the file, but I am still getting the error 3 extended -2147352567. Any idea why I am unable to load the file with _Excel_BookOpen?

 

EDIT

I am also able to find it with InetGetSize

Edited May 1, 2015 by nitekram
added InetGetSize

[water]

Looks like Excel has a problem with the path you specified.
Can you please add the following lines at hte top of your script so we get better error information?

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

 

[nitekram]

 

@@ DEBUG COM Error encountered in SCRIPT.EXE (-1) :
    Number          = 0x80020009 (-2147352567)
    WinDescription  = Exception occurred.
    Description     = Microsoft Excel cannot access the file 'UNC path is correct'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
    Source          = Microsoft Excel
    HelpFile        = xlmain11.chm
    HelpContext     = 0
    LastDllError    = 0
    Retcode         = 0x800A03EC

This is my call that is getting the error:

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, True, False)

 

 

Edited May 1, 2015 by nitekram
added some code

[water]

Looks like you are out of look accessing the Excel file the way you try to do.

When you search the forum for "Excel Sharepoint" you will find some threads and - maybe - a solution.
 

[nitekram]

Okay, I looked prior to posting here, and really could not find anything that would help.

But I think I found a work around. Since I was able to see that the file did exist with the UNC, I just did a FileCopy to a temp directory locally, and then read the file with the Excel UDF from that location - though it seems that it should work the other way - I guess it is an issue with SharePoint.

Good think I only want to read the file and not actually update the file, as I do not believe the same would be true, to FileCopy it to the sharepoint location. I do not want to test that, as the sharepoint server is live, but if anyone else could do it in a test environment, and could post the results, that would be great.

One more idea. Do you think it has to do with permissions via the Excel UDF when trying to access a file on sharepoint - just a thought?

Edited May 1, 2015 by nitekram
spelling

[water]

I do not think it is a permission issue. The UDF uses COM to access Excel and workbooks - so it might be a problem with Excels COM interface.

[water]

Looks like Excel has some methods to work with workbooks on servers.
Check CanCheckIn, CheckIn, CheckInWithVersion, LockServerFile, ServerPolicy for the Workbook object plus
CanCheckOut and CheckOut for the Workbooks object.

Edited May 1, 2015 by water

[water]

Something like this should give you an idea how it works:

Private Sub testthis()
    ' Attempt to open a sharepoint file
    Dim oWB As Workbook, MyPath As String
    MyPath = "https://sharepoint.domain.com/folder1/folder2/folder%20number%20three/filename.xlsx"  ' Obviously this is just a sample path. My code has the proper path & filename
    If Workbooks.CanCheckOut(MyPath) = True Then
        MsgBox ("File on Sharepoint CAN be checked out.")
        Workbooks.CheckOut MyPath
        Set oWB = Workbooks.Open(MyPath)
        
    Else
        MsgBox ("File on Sharepoint can NOT be checked out." + Chr(13) + _
                "Make sure no one else is working in the file." + Chr(13) + _
                "Including yourself.")
        Exit Sub
    End If
    MsgBox (oWB.Worksheets("Specs-Undated-Small-Low%").UsedRange.Rows.Count)
    oWB.CheckIn (False)
    Set oWB = Nothing
End Sub

http://www.vbforums.com/showthread.php?728921-EXCEL2010-VBA-opening-another-Excel-file-from-a-sharepoint-site

Edited May 1, 2015 by water

[nitekram]

Thanks, I will look into that and if I have any luck, I will report my findings.

[water]

Thanks - I'm sure this will come in handy for future reference

[nitekram]

By the way, not sure if it was missed or if it is because of the update to the forum, but the only links you have is for CanCheckIn and the CheckOut the other ones look like links but are not hyperlinks

[nitekram]

I tried the following - can you make sure it looks good. But it does come back as FALSE - I have tried it both on sharepoint and local, both have the same results

 

If $oAppl.CanCheckOut($sTempFileLocation) Then
                MsgBox('', '', 'true can check out')
            Else
                MsgBox('', '', 'Fales')
            EndIf

 

[water]

You need to use the Workbooks object, so it should look like:

If $oAppl.Workbooks.CanCheckOut($sTempFileLocation) Then
    MsgBox('', '', 'true can check out')
Else
    MsgBox('', '', 'Fales')
EndIf

 

Edited May 1, 2015 by water

[water]

By the way, not sure if it was missed or if it is because of the update to the forum, but the only links you have is for CanCheckIn and the CheckOut the other ones look like links but are not hyperlinks

​In first place I posted this in the wrong forum:

Maybe just a copy & paste problem. All this methods can be foun in the Workboosk and Workbook objects:

Workbooks Object
Workbook Object

 

[nitekram]

Okay. Well found something strange. After I made your change, I was able to get true (meaning the method said I could check it out), so I did a checkout to see if that would make a difference, but now I am unable to get it so say true again, and not sure how to check it in again lol.
I even went to the sharepoint server and tried to check it out there, and checked it back in, but still not able to check if I can check it out. It has been a long night - I will try again tonight when I am working.

[water]

This line from the VB example I posted above checks the Excel workbook in and drops all changes made to the workbook.

oWB.CheckIn (False)

 

[nitekram]

I used $oAppl.Workbooks.CheckIn(False) - going off your other example.

[water]

To Checkout a workbook you need to use the Workbooks object. To CheckIn again you need to use the Workbook object as returned by _Excel_BookOpen.