Open Excel File On Sharepoint 2010

[stanl]

First: I don't administer our SharePoint, but I can manually send and update files. I wish to automate certain Excel files so I can update and re-save.

The code below is a test URL to see if I can checkout and therefore open a SharePoint file exclusively. It works, in that when the file is opened [the URL is Valid], the '...Readonly' message does not appear, otherwise if users have it opened readonly (which is the default) the UDF returns a message. However, even though the file opens, I get a WB unknown error on the line oXL.Workbooks.CheckOut(cXLS) - which according to the docs I have read is a valid command. (see attached JPEG)

Code (WINBATCH) Select Expand


#DefineSubRoutine chkoutxl()
retval=1
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Return(0)
If !oXL.Workbooks.CanCheckOut(cXLS) == @True
   oXL=0
   Message("Error","Cannot Check Out":@CRLF:cXLS)
   retval=0
Endif
Return(retval)
#EndSubRoutine

cINI=DirScript():"chkout.ini"  ;just holds the URL
If ! FileExist(cINI) Then Terminate(@TRUE,"Cannot Continue","Missing Config Settings File ":cINI)

cXLS=IniReadPvt("Main","xls","",cINI)  ;reads in URL


If chkoutxl()
   oXL.Visible          = 1  ;Excel will not be visible
   oXL.ScreenUpdating   = 1  ;these next 3 increase speed of processing
   oXL.UserControl      = 1
   oXL.DisplayAlerts    = 0
   oXL.Workbooks.CheckOut(cXLS)
   oXL.Workbooks.Open(::FileName=%cXLS%,Readonly=@FALSE)
   oXL=0  ;for testing
Endif


Exit



[td]

You might want to check your wwwbatch.ini file for a hex error code under 'COM & CLR Sub-system' or 'COM/CLR Exception'.  Don't know if it is relevant to Sharepoint but some source code management systems will not let you check out when you already have a writable copy in the target folder.  For these systems this is true even if no other user has the file checked out and you have permission to check the file out.   

[stanl]

You might want to check your wwwbatch.ini file for an the hex error code under 'COM & CLR Sub-system' or 'COM/CLR Exception'.

looked like this

Code Select Expand


[COM & CLR Sub-system]
Function=InvokeMember
ErrorCode=261 (0x80010105)
ErrorDesc=The server threw an exception.



If I re-write as a VBA macro, I don't get and error on Checkout(), but the file is never opened by the macro - but if  I go into sharepoint, I can open it and it says I have it checked out.

I'll play with it some more tomorrow.

[stanl]

I suppressed the error, and the sharepoint file will open (for either edit or readonly). I tested and opened it for edit. Left it open. Then ran the compiled script again. It opened readonly, but the code to check the status did not execute.

Code (WINBATCH) Select Expand


#DefineSubRoutine chkoutxl()
retval=1
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Terminate(@TRUE,"Cannot Continue","Cannot Initiate Instance of Excel.")
If oXL.Workbooks.CanCheckOut(cXLS) ==0
   oXL=0
   Display(3,"Error","Cannot Check Out":@CRLF:cXLS)
   retval=0
Endif
Return(retval)
#EndSubRoutine

IntControl(73,2,0,0,0)


cINI=DirScript():"chkout.ini"  ;just holds the URL
If ! FileExist(cINI) Then Terminate(@TRUE,"Cannot Continue","Missing Config Settings File ":cINI)

cXLS=IniReadPvt("Main","xls","",cINI)  ;reads in URL


If chkoutxl()
   oXL.Visible          = 1 
   oXL.ScreenUpdating   = 1 
   oXL.UserControl      = 1
   oXL.DisplayAlerts    = 0

   ;error will be supressed and file will open to allow edit
   ;otherwise will open ReadOnly if already opened to allow edit
   oXL.Workbooks.CheckOut(cXLS)
   oXL.Workbooks.Open(::FileName=%cXLS%,Readonly=@FALSE)

   ;however, if file is opened readonly this code WILL NOT execute
   If oXL.ActiveWorkBook.ReadOnly
      Display(3,"Cannot Continue",cXLS:@CRLF:"is opened ReadOnly.")
   Endif
   oXL=0 
Endif


Exit


:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return


[td]

The script as displayed is not suppressing errors in the subroutine which would appear to be contrary to your intension.

[stanl]

The script as displayed is not suppressing errors in the subroutine which would appear to be contrary to your intension.

My intention was to open a sharepoint file to edit, or be notified that it can't.  The subroutine is more or less a checksum to indicate whether to even try to open the file. Not really sure it ever returns False. I used the Gosub error handler to supress the

oXL.Workbooks.CheckOut(cXLS)

error, so at least try to open the file to edit. The bottom line appears to be that you cannot do what I am trying to do with COM, even though there are posts to the contrary. A Plan-B might be to drop the Excel Object in WB, then use GetObject() to detect and see if the Excel file is Readonly.

Everyday I get emails looking for 'Sharepoint Developers'..... I can see why >:(

[td]

My error. For some reason I decided that your error was occurring at the 'CanCheckOut' method call instead of the 'CheckOut' method call.  I guess I need a second cup before starting work in the morning.

If the 0x80010105 error is actually being produced by your script (sans error handling) then it could be a Sharepoint server bug. At least, a server bug of some kind is often offered as the reason for this particular COM server error by MSFT.

[stanl]

Not to prolong this... but it gets better: by actually using the Filename= and enclosing in quotes, when the file is locked, I now get the pop-up "File In Use", but cannot detect it from code.

Code (WINBATCH) Select Expand


#DefineSubRoutine chkoutxl()
retval=0
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Terminate(@TRUE,"Cannot Continue","Cannot Initiate Instance of Excel.")
If oXL.Workbooks.CanCheckOut(::Filename="%cXLS%")<>@FALSE Then retval=1
If ! retval
   oXL=0
   Display(3,"Error","Cannot Check Out":@CRLF:cXLS)
Endif
Return(retval)
#EndSubRoutine

IntControl(73,2,0,0,0)


cINI=DirScript():"chkout.ini"  ;just holds the URL
If ! FileExist(cINI) Then Terminate(@TRUE,"Cannot Continue","Missing Config Settings File ":cINI)

cXLS=IniReadPvt("Main","xls","",cINI)  ;reads in URL


If chkoutxl()
   BoxOpen("Please Wair...","Accessing ":cXLS)
   oXL.Visible          = 1 
   oXL.ScreenUpdating   = 1 
   oXL.UserControl      = 1
   oXL.DisplayAlerts    = 0

   ;error will be supressed and file will open to allow edit
   ;otherwise will open ReadOnly if already opened to allow edit
   oXL.Workbooks.CheckOut(::FileName="%cXLS%")
   oXL.Workbooks.Open(::FileName="%cXLS%",Readonly=@FALSE)

   ;however, if file is opened readonly this code WILL NOT execute
   If oXL.ActiveWorkBook.ReadOnly
      Display(3,"Cannot Continue",cXLS:@CRLF:"is opened ReadOnly.")
   Endif
   oXL=0 
Endif


Exit


:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return


[stanl]

Mystery partially solved. It appears is a user opens Sharepoint and actually clicks on the link to cxheck out a document, the code works.  However, if they merely open the document under edit mode, the code will not see itr checked out, attempt to check it out (which it can't do, and therefore the error I was supressing) then attempt to open it getting the file in use dialog. I will refer further questions to an Excel forum and repost if the issue can be resolved.