VBA Sharepoint Check In File after Upload

user_14172414 0
Viewed 157

Been using this code for a while to upload a file to SharePoint and noticed the other day that when the file is uploaded, it is checked out to myself automatically and have to go into SharePoint and manually check-in so that others can view the file. Any way to modify or add to my code below to auto check-in a file after it uploads? Totally stumped and any help would be greatly appreciated.

Sub SharePointUpload()

Dim WSN
Dim spAdd

Set WSN = CreateObject("WScript.Network")
spAdd = "https://mysharepoint/test"
WSN.mapnetworkdrive "N:", spAdd

ActiveWorkbook.Save

Dim SharepointAddress As String
Dim LocalAddress As String
Dim objNet As Object
Dim FS As Object

' Where you will enter Sharepoint location path
SharepointAddress = "\\mysharepoint\test"
' Where you will enter the local file path
LocalAddress = "C:\data\sample_file.xlsm"
Set objNet = CreateObject("WScript.Network")
Set FS = CreateObject("Scripting.FileSystemObject")
If FS.FileExists(LocalAddress) Then
FS.CopyFile LocalAddress, SharepointAddress
Else: MsgBox "File does not exist!"
End If

Set objNet = Nothing
Set FS = Nothing

WSN.removenetworkdrive "N:"

End Sub
vba excel
1 Answers

I had the same issue with our SharePoint Document library even with option Require Check Out switched off - some documents (Word, Excel) were uploaded as Checked Out automatically (green arrow mark) whereas others were in fact Checked In from upload. Moreover when I wanted to Check In manually such documents, I got error message about missing mandatory field, although there was no field in the library set as mandatory.

The reason in my case was the field Title. This field is presented in library as default text field and it is also presented in Office files. I noticed that for Office files with empty field Title the document is automatically Checked Out during upload. When uploaded document contained some text in Title property, no Check Out was set. I also tried to use workflow to change the Title and then Check Out file (after creation), but it was not working - initial Check In was required to start the workflow. Manual change of document properties works but that's too tedious...

The first programmatic option to handle this issue could be to fill Title property of uploaded document when empty (i.e. add file name or any other text). Should work well with many languages. Example with Python:

from docx import Document

path = 'D:/myfile.docx'
document = Document(path)
document_property = document.core_properties
if not document_property.title:
    document_property.title = 'Everything is Awesome'
document.save(path)

In VBA Title should accessible via Wb.BuiltinDocumentProperties("Title").

Another successful option for me was to find column Title in Columns of Document library and rename it to something else (I used Titlex as new name), then try to upload new document with empty Title - this time there was no automatic Check Out for the uploaded document. Then I could set the Titlex back to Title without returning the issue.

user_102578100
Related