We help IT Professionals succeed at work.

Copy Excel File to Sharepoint, Edit Properties and Check it in..... all in VBA...

Delerium1978 used Ask the Experts™
Hi Guys and Gals

I'm looking at a way of automating a manual process we're currently doing. The process is:

1) Upload excel file from network drive to sharepoint (In the web access)
2) Set the properties (Title and Document Type)
3) Click ok.....

Sounds slick but our web access is terribly slow.

What I think I want is some VBA code to:

1) Copy the excel file from a network drive to sharepoint
2) Set the properties of that file in sharepoint
3) 'Check in' the file so its visible to others.

Is this possible?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Forgot to mention, im using Excel 2007


Hi Guys, I've got the copy to sharepoint part done, its the setting of the file metadata and checking in part that im struggling with. Reading across the web, the Web Service Toolkit isnt available for excel 2007 so cant do it that way. What are the other options available to me to do this?

Scott HelmersVisio Consultant, Trainer, Author, and Developer
Most Valuable Expert 2011

I recall struggling with this a while back and using the test code below -- see whether this helps:
Sub SPCheckOutAndEdit()
'   >>>>>>> CanCheckOut method will fail if app is not already logged into SharePoint

    ' insert URL of Excel workbook on your SharePoint site here...
    Const sFullname As String = _
    Dim gxlApp      As Excel.Application
    Dim gxlBooks    As Excel.Workbooks
    Dim gxlbook     As Excel.Workbook
    Set gxlApp = Application
    Set gxlBooks = gxlApp.Workbooks

    ' if workbook can be checked out, do so
    If gxlBooks.CanCheckOut(sFullname) Then
        gxlBooks.CheckOut (sFullname)
    End If
    ' open checked out workbook for edit
    Set gxlbook = gxlBooks.Open(sFullname, ReadOnly:=False)
    If gxlbook Is Nothing Then Exit Sub
    ' do stuff to workbook here...
    If gxlbook.CanCheckIn Then
        gxlbook.CheckIn SaveChanges:=True
    End If

End Sub

Open in new window


@scott - thanks, looking at the code, this only deals with the check in part, the additional issue I have is that it wont check in until a mandatory meta data field is filled in for that document but I have no idea how to set this field. The field name is "Document Type", Any ideas?

Visio Consultant, Trainer, Author, and Developer
Most Valuable Expert 2011
According to
you can add
to add comment metadata on check in. Does that accomplish what you need? I know it's not a specific field called "Document Type" but does it satisfy the requirement?


@scott - sadly no, it wont let me check in the file until the file itself has metadata for 'Document Type' filled in :(
Daryl SirotaDirector of Technical Services
Check out http://spbulkdocumentimport.codeplex.com/
Also, you should be able to provide a default value for Document Type in the library settings if that value cannot be programmatically identified.


Closing question as I don't have time to work on this now for the forseeable. Both comments look like good ways to go.