We help IT Professionals succeed at work.

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

Delerium1978
Delerium1978 used Ask the Experts™
on
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?

James
Comment
Watch Question

Do more with

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

Author

Commented:
Forgot to mention, im using Excel 2007

Author

Commented:
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?

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

Commented:
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 = _
        "http://yaddayadda.com/test.xls"
    
    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

Author

Commented:
@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?

James
Visio Consultant, Trainer, Author, and Developer
Most Valuable Expert 2011
Commented:
According to
   http://msdn.microsoft.com/en-us/library/bb223246%28v=office.12%29.aspx
you can add
   Comment:="xyz"
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?

Author

Commented:
@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
Commented:
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.

Author

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