Checking for Documents with no Checked In Version for all Project Sites – Part 2

Part 2:  X-Ray Glasses!

Good morning, true believers!

Today we’re going to create our second function so that we can successfully Check for Documents with no Checked In Version for all Project Sites.  This is a continuation from Part 1.

The second function looks almost like the first one, but we’ve modified it to search the HTML for something a lot more mysterious.  I haven’t tried this on any sites from SharePoint that aren’t Project Sites, so I can’t be sure it would work on any old document library.  If you try it please let me know!

So here’s how our taggy-tag should look:

    Tag2 = Tag & “=” & Chr(34)
    EndTag = Chr(34)

Making “tag2” a combination of the tag from the macro (something called “h3 class”, which was the only obvious difference I found between the “Manage Documents…” locations that had documents in the list and those which didn’t) and the data between the double quotation marks.

The data that it pulls basically boils down to either “I found that tag, dude!” or “I didn’t find that tag, dude!” so I’ve simplified the result like so:

 If i = 0 Then
            GetStatus = “no”
        Else
            t = Split(txt, Tag2)(1)
            GetStatus = “yes”
Now the whole function, exactly like mine (everyone wants to copy me, because I’m cool!) is this:

Function GetStatus(WebPage As String, Tag As String)
    
    Dim t, Tag2 As String, EndTag As String
    Dim oHttp As Object, txt$, i&, j
    
     ‘Adjust this to suit
     ‘Chr(34) is a “
    
    Tag2 = Tag & “=” & Chr(34)
    EndTag = Chr(34)
     ‘*************************
    
    On Error Resume Next
    Set oHttp = CreateObject(“MSXML2.XMLHTTP”)
    If Err <> 0 Then Set oHttp = CreateObject(“MSXML.XMLHTTPRequest”)
    If oHttp Is Nothing Then MsgBox “MSXML2.XMLHTTP not found”, 16, “Error”: Exit Function
    On Error GoTo 0
    
    With oHttp
        .Open “GET”, WebPage, False
        .Send
        txt = .responseText
        i = InStr(1, txt, Tag2, 1)
        If i = 0 Then
            GetStatus = “no”
        Else
            t = Split(txt, Tag2)(1)
            GetStatus = “yes”
        End If
    End With
    Set oHttp = Nothing
    
End Function
Now for the macro, which is almost exactly like the other one but with a different staring point and a different tag:
Sub Check()

 Range(“E3”).Select

 Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
   ActiveCell.Value = GetStatus(ActiveCell.Offset(0, -1).Value, “h3 class”)
   ActiveCell.Offset(1, 0).Select
 
  Loop   
End Sub
Don’t forget that your range might be different from mine – the important thing is that it’s directly to the right of the URL (with GUID!) that you want to pull data for.

And we’re done!
I know it’s not the prettiest code ever, and I’m sure there’s probably even a way to get it to list out the documents which are checked out, but I have a day job, here, so I’m going to stop at serviceable.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s