MOSS 2007 SharePoint and Excel checkout

[dougbert]

EXPERTISE:

I hope I'm not pushing my luck here, as this question may be out of the scope of this forum. In addition to Excel expertise, you'll probably need advanced Sharepoint expertise in order to help me.

BACKGROUND:

I am the SharePoint admin for our MOSS 2007 SP site. Due to the large number of users collaborating on a single Excel file, I implemented a site-wide mandatory Check-out / Check-in policy (version control) on all files. When a file is 'checked-out', the user immediately receives a pop-up dialog box. In the middle of the dialog box is a 'check box' followed by the option 'use my local drafts folder'. By default, this 'check box' contains a 'check mark'. However, this box MUST be 'unchecked' in order for the Excel file to retain the embedded file link to another single Excel source file. Leaving the box 'checked/ticked' changes the embedded source file link to the user's local HDD. This breaks the entire flow of information for the Excel workbook.

I've tried educating the users to remove the check mark via e-mail. I've placed a Warning Message with this same information when the user opens the Excel file with the 'checkbox' 'checked/ticked' anyways. Microsoft doesn't allow the default state of the 'check box' to be changed at the server level. It can only be implemented at the local user level within their Excel Options. So, I sent instructions to all the users on how to make the 'check box' 'unchecked' by default. However, they actually have to PERFORM the instructions in order for this to occur.

Guess what? I can't get 100% compliance! Duh! They 'forget'. So, I'm continually having to go in and relink Excel to the correct file path.

WHAT I'D LIKE TO DO:

(the bold text is the hardest part of this request)
I'd like to insert VBA code into the Excel ThisWorkbook module in a Workbook_Open sub that would intercept the 'state' of the SP 'checkbox' titled 'use my local drafts folder'. If it's unchecked, "all systems go!" You may edit the file. However, if the 'check box' is 'checked/ticked', I want to display a message explaining what they did wrong, instructions for correcting and the fact that they will not be allowed to continue to edit the file. When they click OK, the file will immediately close AND FORCE a discard of the 'checked-out' Excel file.

The message is obviously the easy part. Intercepting the 'checkbox' status may be the hardest part. Closing the file is easy. Forcing a discard of the 'checked-out' file may be the other hard part.

WHAT I NEED FROM YOU:

The VBA code for the 'intercept' and the 'forced discard'. Any takers?!

Any and all suggestions are welcomed. If I was dealing with robots this would be easy. Humans?! Whatya' gonna' do with 'em?!!! (rhetorical)

 

[dougbert]

More information I uncovered:

I found the following thread that referenced my 'use local drafts folder' issue:
http://social.msdn.microsoft.com/Fo...n/thread/8c990eda-fc0c-425e-8382-bbf5665f5374

They wanted to customize the dialog box verbiage, which can't be done. However, within the thread I found this excerpt (I cleaned up their language in an effort to make it more readable):

Re-phrased: "I have analyzed the process and found that when a document is checked out, CheckoutDocument javascript function (I discovered this is an ActiveX control) is called by the system. In the function, "CheckoutDocumentPrompt" method of the ""SharePoint.OpenDocuments.3" is being called. This method "check's out" the document by taking document url and returns the status."

I found this information on the OpenDocuments Control:
http://msdn.microsoft.com/en-us/library/ms454230(v=office.12).aspx

Here are the 'members' of the OpenDocuments Control and their uses:
http://msdn.microsoft.com/en-us/library/cc264316(v=office.12).aspx

Within those 'members' is the MSDN information about the CheckoutDocumentPrompt method:
http://msdn.microsoft.com/en-us/library/cc768578(v=office.12).aspx

So, I wonder... If fEditAfterCheckout is the only boolean flag in this method, then is it the boolean status of the 'check mark' even if the name doesn't seem to fit exactly? I suspect the 'f' at the beginning may mean 'flag', though I'm no expert in this language.

To my other point:
This link points to the method to discard the checkout:
http://msdn.microsoft.com/en-us/library/cc768614(v=office.12).aspx

Does this provide enough 'meat' for the lightbulb to 'come on' for someone?

If so, do you believe it's possible to obtain the status of the 'check mark' from this ActiveX Control using VBA? If so, I should easily be able to write the remainder of the code to initiate the Warning message.

If that's possible, then can the method to discard checkout also be implemented? This seems like it might be the more difficult issue now, because it requires setting this flag in the ActiveX Control.

In searching on "SharePoint" in the forum, I've noticed there haven't been many replies to these types of questions. I will be very impressed if anyone can help me figure out how to do this in a sub!!! Or, even get me started!

Thanks for any and all efforts!

 

[MikeDBMan]

I unfortunately don't have an answer for you but I was wondering how you are doing with this issue. I have a similar issue. What I am trying to accomplish is to open SharePoint .xlsx files using VBA, checking them out, saving them (or saving-as) and then checking back in. I can't figure out how to do any of these things. Do you have any suggestions based on your studies in this area?

 

[dougbert]

Hi Mike,

As with you, I have no expertise in using VBA with SP. Unfortunately, I just don't have the time to get up to speed on it either. So, for now, I have to live with my situation, as I haven't discovered another resource on the web that has a forum where one can pursue these ideas.

Good luck to both of us!

 

[MikeDBMan]

Today is your lucky day. I got some help on another posting and got it all figured out:

Check In:

        ActiveWorkbook.Save
        ActiveWorkbook.CheckInWithVersion

'to check out:

Let OKOut = UseCanCheckOut(ThePath & TheFile2)
        If OKOut = 1 Then
            Application.DisplayAlerts = False
            Workbooks.Open FileName:=ThePath & TheFile2
       else
            msgbox("Can't check out file")
       end if


'And the function called above:

Function UseCanCheckOut(docCheckOut As String)
    If Workbooks.CanCheckOut(FileName:=docCheckOut) = True Then
       Workbooks.CheckOut docCheckOut
       UseCanCheckOut = 1
    Else
       UseCanCheckOut = 0
    End If
End Function

 

[dougbert]

Wow! It looks like it IS my lucky day! I'm very much looking forward to seeing how I can modify this for my scenario.

It looks like this may handle the 'Use my local drafts folder' portion of my problem. It appears that it may be checking the flag status for this option. Is that the case? Have you tried it with the dialog box checked/unchecked? If so, what occurs after the user receives the message? Does it discard their attempted check out?

It looks like for my purposes, I'll need to remove the very first 2 lines, as they don't appear to be necessary for the checkout process itself; true?

Thanks for sharing!
-dougbert

 

[MikeDBMan]

The first two lines are to check back in a file with VBA. So you probably won't need that. Regarding the check out, this is set up so that the user will not get the file if it is not in check-out mode. So there are no checkboxes at all. Just the file open on the screen if he got it checked out and opened. I hope this helps you. If you need help "assembling" this into a working sub let me know more specifics and I can help you with that too.

 

[dougbert]

Hmmmm... Yes, after analyzing your code further, I now realize it doesn't quite address my issue, or it doesn't appear that it does.

On MOSS 2007, I have an enhanced site. BTW: I have a Document Library 'policy' in place that requires check out / check in, in order for a user to even edit a file.

One of the display columns in the Document Library directory listing is 'Checked out to:'. So, my users already can see if someone else has the file open and they don't attempt to check out the file. Even if they tried they'd receive a message not allowing them to check out.

In the directory listing, the user hovers their cursor to the right of the file name. A pull down menu appears. They select Check Out. Immediately, a pop up dialog box opens with the 'Use my local drafts folder' option checked by default. This is the Excel default for all users with the default Excel installation. Yes, it can be changed, but only by the user themselves in Excel Options. I haven't been 100% successful in getting users to change the default.

However, if left checked, all the links in formulas within the file will change from pointing to the desired file to the user's local HD. Since the user Checked Out properly, they are still allowed Edit access to the file. When the user saves, the damage is done. The next time someone opens the file properly (or otherwise), they receive a message that one or more links are 'broken'. The options are Continue or Edit Links. Users have no idea how to fix this. So, they click Continue and ignore the message. So, I have to go in from time to time when this happens and reset to the correct file path.

So, my challenge is to figure out via VBA whether or not the 'Use my local drafts folder' option is checked or unchecked. Then, proceed to allow the edit or force discard the successful Check Out, depending upon the status of the 'Use my local drafts folder' option.

It doesn't appear your code will address this as is. Is that correct? I did find the thread where you were discussing this.

Thanks for any ideas.

 

[MikeDBMan]

I think the best way to see if this code works is to check out and open a file via the VBA code I have and then save it and then go back into the file and see if you links issue exists. I don't understand how your links become an issue in the first place but I'll take your word for it.

 

[L01$Lan3]

hi - I'm banging my head against the wall and I'm hoping you can help.

I am attempting to save a document in a document library with a custom excel template.

I want to generate a unique file name for saving. However, I need to supply a path (otherwise excel wants to save it local)

BUT I REALLY DO NOT WANT TO HARD CODE IT. How can I grab the url of the document library with VB?

many many thanks (I drink because sharepoint makes me cry )