none
Macro to calculate total editing time returns two different results RRS feed

  • Question

  • I am using the following code to calculate the "Total Editing Time" of all the versions of a SharePoint document. I open the latest version of the document without enabling editing and then run the code. On the first run, the code doesn't identify any versions (I think vCount actually comes back "0" instead of nothing, even though I don't initialize it with 0). The Msgbox command returns the name of the document I'm evaluating and nothing for TET. However, if I run the code a second time (doesn't necessarily have to be immediately after), it does go through the versions and calculates the total editing time. For some reason, it also shows one more version in the vCount than I count on SharePoint. I'm not concerned about the latter, but was curious why I get two different results when I run the code on the same document. The macro is in a separate Word file, but since it's grabbing the name of document I want, I don't think that first attempt is counting the macro file.

    Scott

    Sub SumEditingTime()
    Dim d, q As Document
    Dim dName As String
    Dim v As DocumentLibraryVersion
    Dim TET, vCount As Long
    
    Set d = ActiveDocument: dName = d.Name
    For Each v In d.DocumentLibraryVersions
    v.Open
    Set q = ActiveDocument
    TET = TET + q.BuiltInDocumentProperties("Total Editing Time")
    vCount = vCount + 1
    q.Close (wdDoNotSaveChanges)
    
    20 Next v
    MsgBox dName & vbCrLf & vCount & " versions" & vbCrLf & "Total Editing Time = " & TET & " minutes"
    End Sub
    


    Scott Stocking

    Monday, November 3, 2014 2:32 PM

Answers

  • I added a simple count statement (dlv=d.DocumentLibraryVersions.Count) and go the error message for that line as well, but then it ran normally on second pass, and gave me the dlv value as well. I'll try adding an OnError Resume Next statement to see if it will run right the first time. I know we do have a few layers of security on SharePoint, so that could be the issue too. Thank you. Now, if I could only get the macro to run faster while I'm trying to get other things done. :-)

    Scott


    Scott Stocking

    • Proposed as answer by Peter Jamieson Wednesday, November 5, 2014 12:53 AM
    • Marked as answer by ScottSox Thursday, November 6, 2014 2:53 PM
    Tuesday, November 4, 2014 8:23 PM

All replies

  • I was able to replicate this using similar code, but have not been able to determine the cause of the problem - perhaps someone here will be able to recognise these symptoms and make some suggestions. 

    I inserted the following line before iterating the DocumentLibraryVersions collection:

    debug.print d.DocumentLibraryVersions.Count

    The first time through, the VBA would hang at that line. If I clicked the pause button in VBA Editor, then try to start again using the single-step button, the VBA editor would restart the subroutine, and after that, the routine would generally work until I started with a new open file. However, sometimes it would not, and although I seemed to be able to close everything and start again, an instance of Word was always left running and I would have to kill it in Task Manager.

    I tried to use the Locals window to looks at the DocumentLibraryVersions member, but clicking on the "plus box" to open the tree of values also hangs the process. (Opening the tree for a Document, which has a lot of properties, can take time anyway, but I am not sure that is the only thing going on in this case).

    I experimented with both an Office 365 SharePoint site and one on my local network. There was little difference in behaviour. 

    My best guesses (and that is all they are) are that
     a. Word does not retrieve this information until it is requested, then tries to contact the SharePoint server and either fails or times out. Trying to open the property tree in the Locals Window may also trigger that, even when no code that references "Server-only" properties.
     b. There is actually a permissions/authentication problem of some kind, and Word is waiting for credentials that it never receives because none of the components involved in geting the data prompts for them (or some such).


    Peter Jamieson

    Tuesday, November 4, 2014 2:28 PM
  • Thank you, Peter. I should add a few more details: I'm using Word 2010 and Windows SharePoint Services 3.0. I don't think there's a permissions issue. If I have at least one Word document open (in this case, the macro file), SharePoint doesn't ask me to supply my password to open a document after I've logged into the site. I've also never had an instance of a lingering version of Word running that I needed to close with the task manager. I will await more input from the community.

    Scott


    Scott Stocking

    Tuesday, November 4, 2014 2:43 PM
  • Armed with that info., I tried Win 7 (32-bit) with Word 2010 with SP2010 (the earliest version I still have on tap). Not very enlightening, but... 

    ...the behaviour is now slightly different - on the first pass I see an error 0x80041051 ("The Server has returned an error".  A search suggests that mainly occurs with servers that have ForeFront or some other virus checker that marks the documents server-side in some way. After that, everything proceeds much more smoothly than with my previous tests (Win 8.1 + Word 365 (2013) + Office 365 SharePoint in one case, Win 8.1 + Word 2013 (not the 365 version) + a non-365 version of SharePoint.

    Whatever the case, it does seem that Word is trying to contact the server even to get the count of the Document Versions. 



    Peter Jamieson

    Tuesday, November 4, 2014 4:12 PM
  • I added a simple count statement (dlv=d.DocumentLibraryVersions.Count) and go the error message for that line as well, but then it ran normally on second pass, and gave me the dlv value as well. I'll try adding an OnError Resume Next statement to see if it will run right the first time. I know we do have a few layers of security on SharePoint, so that could be the issue too. Thank you. Now, if I could only get the macro to run faster while I'm trying to get other things done. :-)

    Scott


    Scott Stocking

    • Proposed as answer by Peter Jamieson Wednesday, November 5, 2014 12:53 AM
    • Marked as answer by ScottSox Thursday, November 6, 2014 2:53 PM
    Tuesday, November 4, 2014 8:23 PM
  • SOunds like you have most of what you need now. As for performance, the only suggestion I can make is that if you need this facility enough, it might be worth considering writing it as a server-side .NET + SharePoint SDK + Office Open XML SDK application and avoiding opening all those versions in Word, over the network. But I really can't advise on the feasibility or degree of difficulty involved in that.

    Peter Jamieson

    Wednesday, November 5, 2014 12:53 AM