Check in PDF document to SharePoint from Excel using VBA


Can anyone post some simple vba code that I can plug in to my own code to allow me to automatically check in PDF files that have just been automatically created and saved on a SharePoint site?

I'm using the simple procedure, below, which works but means that I have the inconvenience of having to do manual check-ins afterwards.

Sub SaveAsPDF(ByVal theFileName As String, theID As String)

    Dim myPath As String
    myPath = Range("IssueDefectPath") & theFileName & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=myPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Open in new window

Who is Participating?
Rainer JeschorCommented:
you will have to call a SharePoint web service.

Code sample:
    Dim sURL As String
    Dim sEnv As String
    Dim xmlhtp As New MSXML2.XMLHTTP
    Dim xmlDoc As New DOMDocument
	Dim sDocumentPath As String
	Dim sComment As String
    Dim sCheckInType As String
	Dim sHostSetting As String
	' Set here the SharePoint site url where the library is in
    sURL = "https://xxx/_vti_bin/lists.asmx"
	' Set here the complete document path
	sDocumentPath = "https://xxx/yyy/document.pdf"
	' Set a checkin comment if wanted
	sComment = "Checked in from Excel"
	' Set the checkin type (see
	' A string representation of the values 0, 1 or 2, where 0 = MinorCheckIn, 1 = MajorCheckIn, and 2 = OverwriteCheckIn.
	sCheckInType = "1"
	' Set the host name to the root url without the protocol (e.g.
	sHostSetting = ""
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:xsi="""" xmlns:xsd="""" xmlns:soap="""">"
    sEnv = sEnv & "  <soap:Body>"
    sEnv = sEnv & "    <CheckInFile xmlns="""">"
    sEnv = sEnv & "      <pageUrl>" & sDocumentPath & "</pageUrl>"
    sEnv = sEnv & "      <comment>" & sComment & "</comment>"
    sEnv = sEnv & "      <CheckinType>" & sCheckInType & "</CheckinType>"
    sEnv = sEnv & "    </CheckInFile>"
    sEnv = sEnv & "  </soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    With xmlhtp
        .Open "post", sURL, False
        .setRequestHeader "Host", sHostSetting
        .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
        .setRequestHeader "soapAction", ""
        .send sEnv
        xmlDoc.LoadXML .responseText
    End With
    MsgBox "Done"

Open in new window

I could not yet test the code, but it should work.

Rainer JeschorCommented:
just verified to code and it works :-)
(at least on my demo machine)
ToucanierAuthor Commented:
Thanks very much for this. I only needed to tweak a few variables and it worked perfectly. This will save me many hours of mundane work when automatically creating PDFs.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.