asked on

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

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

Delerium1978

ASKER

Forgot to mention, im using Excel 2007

Delerium1978

ASKER

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 Helmers

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

Delerium1978

ASKER

@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

ASKER CERTIFIED SOLUTION

Scott Helmers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Delerium1978

ASKER

@scott - sadly no, it wont let me check in the file until the file itself has metadata for 'Document Type' filled in :(

SOLUTION